Oracle Top Interview Questions

1-         What is Sub Query?

A sub query is a SQL query nested inside a larger query. A sub query may used in :

SELECT clause

FROM clause

WHERE clause

A sub query can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another sub query.

2-         What are the various Oracle database objects?

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES

3-         What is Alias?

Alias is a temporary name provided to a column or table. It gets over after the execution of SQL statement.

4-        What is a view?

View is a virtual table which can be based on one or more tables. 

5-        What is a hot backup?

Hot Backup is basically taking a backup of the database while it is still up and running and database must be in archive log mode.

Benefit: The database is still available for use while the backup is occurring and you can recover the database to any point in time.

6-         What is a cold backup?

Cold Backup is basically taking a backup of the database while it is shut down and does not require being in archive log mode.

Benefit: It is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

7-         Difference between TRUNCATE and DELETE for a table.

Both Truncate and delete commands can use to delete all data from a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.

8-        What are benefits of index?

It provides faster access to data blocks in a table.

9-         In a parent and child table relationship you want to drop parent and re-create it. How would you do this without affecting the children tables?

Steps:

1-     Disable the foreign key constraint to the parent.

2-     Drop the parent table.

3-     Re-create the parent table.

4-     Enable the foreign key constraint.

10-       What is the difference between $ORACLE_BASE and $ORACLE_HOME?

ORACLE_BASE is the root directory for oracle and ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

11-       How would you determine the time zone by using database query?

SELECT dbtimezone FROM DUAL;

12-       What is the difference between a FUNCTION, PROCEDURE and PACKAGE?

They are all named PL/SQL blocks.

Function must return a value. It can be called inside a query.

Procedure may or may not return value.

Package is the collection of functions, procedures, variables which can be logically grouped together.

13-       What is Snap Shot in Oracle Database?

Snapshots are read only copies of a master table located on a remote node and these are periodically refreshed to reflect changes made to the master table.