Oracle interview Questions And Answers

Q : Explain in brief about snapshot in oracle?
A : A recent copy of a table or a subset of rows or cols of a table is called as snapshot in oracle. A snapshot is more than one useful in distributed computing environment. We can create snapshot in oracle using command create snapshot. We can specify refresh interval while creating snapshot.

Q : What’s the difference between trigger and stored procedure?
A : Procedure can run 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.

Q :What is a database buffer cache?
A : A Database buffer cache is a component of SGA. It has the responsibility of to cache most recent accessed data. It keeps the transaction in the buffer cache till it is written on the disk.

Q : Explain the difference between a data block, an extent and a segment?
A : A data block is the smallest unit of logical storage for a database object. As objects grow they get chunks of additional storage that are composed of a contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents are that an object takes when grouped together are considered the segment of the database object.

Q :What is difference between UNIQUE and PRIMARY KEY constraints?
A: A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. Primary Key does not allow the NULL value whereas UNIQUE key allows NULL value.

Q : What are different Oracle database objects?
A : There are many oracle database objects are,
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

Q : What is a view?
A : A view is stored procedure based on the one or more tables, it is a virtual table.

Q : What is difference between SQL and SQL*PLUS?
A : SQL*PLUS is a command line tool where as SQL and PL/SQL language interface and reporting tool. It is a command line tool that allows user to type SQL commands to be executed directly against an Oracle database. SQL is a language used to query the relational database (DML,DCL,DDL). SQL*PLUS commands are used to format query result, Set options, Edit SQL commands and PL/SQL.

Q :What are the more common used in pseudo-columns?
A : The most common used pseudo columns are,
SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

Q : Explain the Oracle cursor : Implicit & Explicit cursors ?
A : Oracle uses work areas called private SQL areas to create SQL statements.
PL/SQL construct to identify the each and every work are used, is called as Cursor.
For SQL queries returning a single row, PL/SQL declares is an all implicit cursors.
For queries that returning more than one row, the cursor needs to be explicitly declared.