Oracle interview questions and answers - 4

22. What is a snapshot in Oracle database?


A recent copy of a table or a subset of rows or cols of a table are called as snapshot in oracle. A snapshot is more useful in distributed computing environment. We can create snapshot in oracle using command “create snapshot”. They are used to dynamically replicate the data between distributed databases.

23. Difference between hot backup and cold backup in Oracle.


A database backup while it is still up and running is a Hot backup and it must be in archive log mode.

A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode.

The benefit of a hot backup is that the database is still available for use while the backup is occurring.

A cold backup is easier to administer the backup and recovery process.

Cold backups does not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.

24. What is the purpose of Save Points in Oracle database?


Save Points are used to divide a transaction into smaller phases.

It enables rolling back part of a transaction.

Maximum 5 save points are allowed in Oracle Database.

Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

25. Will you be able to store pictures in the database?


Yes, pictures can be stored in the database using Long Raw Data type.

This datatype is used to store binary data for 2 gigabytes of length.

However, the table can have only one Long Raw data type.

26. Explain the difference between Ref Cursor and Cursor.


A REF Cursor is a datatype whose value is the memory address of a query work area on the database

A cursor holds the rows returned by a SQL statement

A REF Cursor can be opened on the server and returned to the client as a unit rather than fetching one row at a time.

A cursor can't be returned to client

A REF Cursor is dynamic. It can be changed at run time.

A Cursor is static. It can't be changed at run time

A REF Cursor can be passed from one sub program to other sub program.

A Cursor can't be passed.

27. What is Global temporary table in oracle? What are the benefits of it?


GLOBAL TEMPORARY TABLE is used to store temporary data in scenarios where complex calculation is involved. The data in this table is session specific. This means that the data in temporary table cannot be shared with other sessions and is valid only until session is active. It is most useful when the table structure of an application is not known or the data in the temporary table is needed only while using the application.