Oracle Interview Questions – Set 02

by

What is a snapshot in Oracle database?

A snapshot is a replica of a target master table from a single point-in-time. In simple words you can say, snapshot is a copy of a table on a remote database.

What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

What are the different types of synonyms?

There are two types of synonyms or alias:

  1. Private: It can only accessed by the owner.
  2. Public: It can be accessed by any database user.

What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically generate a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace contains data dictionary tables for the entire database.

What is the difference between hot backup and cold backup in Oracle? Tell about their benefits also

Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with a uncertain condition it should be restarted with RESTRICT mode and then shutdown with NORMAL option.

For a complete cold backup the following files must be backed up.

All datafiles, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).

What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

  • Form module
  • Menu module
  • Pl/SQL Library module
  • Object Library module

What is an Oracle table?

A table is basic unit of data storage in Oracle database. A table contains all the accessible information of a user in rows and columns.

What is the usage of synonyms?

  • Synonym can be used to hide the real name and owner of an object.
  • It provides public access to an object.
  • It also provides location transparency for tables, views or program units of a remote database.
  • It simplifies the SQL statements for database users.

How many memory layers are in the Oracle shared pool?

Oracle shared pools contains two layers:

  1. library cache
  2. data dictionary cache

In the Oracle version 9.3.0.5.0, what does each number shows?

Oracle version number refers:

  • 9 – Major database release number
  • 3 – Database maintenance release number
  • 0 – Application server release number
  • 5 – Component Specific release number
  • 0 – Platform Specific release number