Oracle Interview Question and Answers

Oracle Interview Question and Answers

Oracles DBA Online Training1. What is Oracle table?

ANS: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

2. What is an Oracle sequence?

ANS: A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

3. What are the tools for oracle?

ANS: Tools for Oracle development
1. SQL Developer
2. TOAD
3. Dream Coder

SQL Developer is good for development. It requires less memory compare to others
SQL Developer & Dream Coder doesn’t require oracle to be installed in our PC

4. What is an Oracle index?

ANS: An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

5. What are the components of physical database structure of Oracle database?

ANS: Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

6. What is SGA?

ANS: The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

7. What is Partial Backup?

ANS: A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

8. Define Transaction?

ANS: A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

9. What are clusters?

ANS: Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

10. What is trigger associated with the timer?

ANS: When-timer-expired.

11. What is schema?

ANS: A schema is collection of database objects of a user.

12. What are Schema Objects?

ANS: Schema objects are the logical structures that directly refer to the database’s data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

13. What are visual attributes?

ANS: Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.

14. What is the difference between and constraints and triggers?

ANS: Triggers – They start a chain reaction- for instance each delete, update action etc. can trigger off another function

Contstraint is set when the application is customized so that there are restrictions to the data entered, so that the data is authentic.

15. What are cascading triggers?

ANS: A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers.

16. What is an implicit cursor?

ANS: Oracle opens an implicit cursor to process each SQL statement not associated with an explicit cursor.

17. Define Transaction?

ANS: A Transaction is a logical unit of work that comprises one or more SQL statements executed by a single user.

18. What is procedure? What is main use of procedure?

ANS: Procedure is a group of sql statement. its execute through a single name. you can user multiple time in your database.

Procedure is a collection of sql statements which can perform several events in a single call and the major use of it is the reusability.

19. Explain the difference between trigger and stored procedure?

ANS: Trigger in act which is performed automatically before or after a event occur.

Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Procedure runs only when one call them manually whereas a trigger runs when there is any activity (insert, update, delete) on table on which the trigger is written.

A trigger cannot be written in a stored procedure.

20. How does a stand alone procedures differ from that of other procedures?

These are the 4 differences between a Stand Alone Procedure and a Packaged Procedure

Creation – A standalone procedure is created using the CREATE PROCEDURE statement. A packaged procedure is created as part of the package body’s definition.

Memory – Oracle caches standalone procedures in memory by themselves, but packaged procedures must be stored and cleared from memory with the rest of the package.

Execution – Packaged procedures must be qualified by the package name to be executed by objects that aren’t contained within the package.

Scope – Packaged procedures can reference other constructs and objects within the same package without qualifying the reference with a package name.

For Oracles DBA Online Training in Hyderabad please visit http://www.revanthtechnologies.com