Oracle Interview Questions and Answers
What are the different types of segments?
Data segment, index segment, rollback segment and
What is a data segment?
Each non-clustered table has a data segment. All of the
table's data is stored in the extents of its data
segment. Each cluster has a data segment. The data of
every table in the cluster is stored in the cluster's
What is an index segment?
Each index has an index segment that stores all of its
What is rollback segment?
A database contains one or more rollback segments to
temporarily store "undo" information.
What are the uses of rollback segment?
To generate read-consistent database information during
database recovery and to rollback uncommitted
transactions by the users.
What is a temporary segment?
Temporary segments are created by Oracle when a SQL
statement needs a temporary work area to complete
execution. When the statement finishes execution, the
temporary segment extents are released to the system for
What is a datafile?
Every Oracle database has one or more physical data
files. A database's data files contain all the database
data. The data of logical database structures such as
tables and indexes is physically stored in the data
files allocated for a database.
What are the characteristics of data files?
A data file can be associated with only one database.
Once created a data file can't change size. One or more
data files form a logical unit of database storage
called a tablespace.
What is a redo log?
The set of redo log files for a database is collectively
known as the database redo log.
What is the function of redo log?
The primary function of the redo log is to record all
changes made to data.
What is the use of redo log information?
The information in a redo log file is used only to
recover the database from a system or media failure
prevents database data from being written to a
database's data files.
What does a control file contains?
- Database name
- Names and locations of a database's files and redolog
- Time stamp of database creation.
What is the use of control file?
When an instance of an Oracle database is started, its
control file is used to identify the database and redo
log files that must be opened for database operation to
proceed. It is also used in database recovery.
Is it possible to split the print reviewer into more
than one region?
Is it possible to center an object horizontally in a
repeating frame that has a variable horizontal size?
For a field in a repeating frame, can the source come
from the column which does not exist in the data group
which forms the base for the frame?
Can a field be used in a report without it appearing in
any data group?
The join defined by the default data link is an outer
join yes or no?
Can a formula column referred to columns in higher
Can a formula column be obtained through a select
Is it possible to insert comments into sql statements
return in the data model editor?
Is it possible to disable the parameter from while
running the report?
When a form is invoked with call_form, Does oracle forms
issues a save point?
Explain the difference between a hot backup and a cold
backup and the benefits associated with each.
A hot backup is basically taking a backup of the
database while it is still up and running and it must be
in archive log mode. A cold backup is taking a backup of
the database while it is shut down and does not require
being in archive log mode. The benefit of taking a hot
backup is that the database is still available for use
while the backup is occurring and you can recover the
database to any point in time. The benefit of taking a
cold backup is that 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.
You have just had to restore from backup and do not have
any control files. How would you go about bringing up
I would create a text based backup control file,
stipulating where on disk all the data files where and
then issue the recover command with the using backup
control file clause.
How do you switch from an init.ora file to a spfile?
Issue the create spfile from pfile command.
Explain the difference between a data block, an extent
and a segment.
A data block is the smallest unit of logical storage for
a database object. As objects grow they take chunks of
additional storage that are composed of contiguous data
blocks. These groupings of contiguous data blocks are
called extents. All the extents that an object takes
when grouped together are considered the segment of the
Give two examples of how you might determine the
structure of the table DEPT.
Use the describe command or use the
Where would you look for errors from the database
In the alert log.
Compare and contrast TRUNCATE and DELETE for a table.
Both the truncate and delete command have the
desired outcome of getting rid of all the rows in 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 a now rollback. The delete
command, on the other hand, is a DML operation, which
will produce a rollback and thus take longer to
Give the reasoning behind using an index.
Faster access to data blocks in a table.
Give the two types of tables involved in producing a
star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains
measurements while dimension tables will contain data
that will help describe the fact tables.
What type of index should you use on a fact table?
A Bitmap index.
Give two examples of referential integrity constraints.
A primary key and a foreign key.
A table is classified as a parent table and you want to
drop and re-create it. How would you do this without
affecting the children tables?
Disable the foreign key constraint to the parent, drop
the table, re-create the table, enable the foreign key
Explain the difference between ARCHIVELOG mode and
NOARCHIVELOG mode and the benefits and disadvantages to
ARCHIVELOG mode is a mode that you can put the database
in for creating a backup of all transactions that have
occurred in the database so that you can recover to any
point in time. NOARCHIVELOG mode is basically the
absence of ARCHIVELOG mode and has the disadvantage of
not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having
to write transactions to an archive log and thus
increases the performance of the database slightly.
Page Numbers :