Oracle interview questions /Answer Set-4

What are transaction isolation levels supported by Oracle?

Oracle supports 3 transaction isolation levels:
a. Read committed (default)
b. Serializable transactions
c. Read only


What is SQL*Loader?

SQL*Loader is a loader utility used for moving data from external files into the Oracle database in bulk. It is used for high performance data loads.


What is Program Global Area (PGA)?

The Program Global Area (PGA): stores data and control information for a server process in the memory. The PGA consists of a private SQL area and the session memory.


What is a shared pool?

The shared pool is a key component. The shared pool is like a buffer for SQL statements. It is to store the SQL statements so that the identical SQL statements do not have to be parsed each time they're executed.


38. What is snapshot in oracle?

A snapshot is a recent copy of a table from db or in some cases, a subset of rows/cols of a table. They are used to dynamically replicate the data between distributed databases.

What is a synonym?

A synonym is an alternative name tables, views, sequences and other database objects.


What is a schema?

A schema is a collection of database objects. Schema objects are logical structures created by users to contain data. Schema objects include structures like tables, views, and indexes.




What are Schema Objects?

Schema object is a logical data storage structure. Oracle stores a schema object logically within a tablespace of the database.



What is a sequence in oracle?

Is a column in a table that allows a faster retrieval of data from the table because this column contains data which uniquely identifies a row. It is the fastest way to fetch data through a select query. This column has constraints to achieve this ability. The constraints are put on this column so that the value corresponding to this column for any row cannot be left blank and also that the value is unique and not duplicated with any other value in that column for any row.


Difference between a hot backup and a cold backup

Cold backup: It is taken when the database is closed and not available to users. All files of the database are copied (image copy). The datafiles cannot be changed during the backup as they are locked, so the database remains in sync upon restore.

Hot backup: While taking the backup, if the database remains open and available to users then this kind of back up is referred to as hot backup. Image copy is made for all the files. As, the database is in use the entire time, so there might be changes made when backup is taking place. These changes are available in log files so the database can be kept in sync


What are the purposes of Import and Export utilities?

Export and Import are the utilities provided by oracle in order to write data in a binary format from the db to OS files and to read them back.

These utilities are used:
• To take backup/dump of data in OS files.
• Restore the data from the binary files back to the database.
• move data from one owner to another


Difference between ARCHIVELOG mode and NOARCHIVELOG mode

Archivelog mode is a mode in which backup is taken for all the transactions that takes place so as to recover the database at any point of time.
Noarichvelog mode is in which the log files are not written. This mode has a disadvantage that the database cannot be recovered when required. It has an advantage over archivelog mode which is increase in performance.







What are the original Export and Import Utilities?

SQL*Loader, External Tables


What are data pump Export and Import Modes?

It is used for fast and bulk data movement within oracle databases. Data Pump utility is faster than the original import & export utilities.


What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE: It returns the error number for the last encountered error.
SQLERRM: It returns the actual error message of the last encountered error.