Oracle Interview Questions and Answers
Define Transaction ?
A Transaction is a logical unit of work that comprises
one or more SQL statements executed by a single user.
What is Read-Only Transaction ?
A Read-Only transaction ensures that the results of each
query executed in the transaction are consistant with
respect to the same point in time.
What is a deadlock ? Explain .
Two processes wating to update the rows of a table which
are locked by the other process then deadlock arises. In
a database environment this will often happen because of
not issuing proper row lock commands. Poor design of
front-end application may cause this situation and the
performance of server will reduce drastically.
These locks will be released automatically when a
commit/rollback operation performed or any one of this
processes being killed externally.
What is a Schema ?
The set of objects owned by user account is called the
What is a cluster Key ?
The related columns of the tables are called the cluster
key. The cluster key is indexed using a cluster index
and its value is stored only once for multiple tables in
What is Parallel Server ?
Multiple instances accessing the same database (Only In
What are the basic element of Base configuration of an
oracle Database ?
It consists of
one or more data files.
one or more control files.
two or more redo log files.
The Database contains
one or more rollback segments
one or more tablespaces
Data dictionary tables
User objects (table,indexes,views etc.,)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, Redo log
buffers, Shared SQL pool)
SMON (System MONito)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
CKPT (Check Point)
User Process with associated PGS
What is clusters ?
Group of tables physically stored together because they
share common columns and are often used together is
What is an Index ? How it is implemented in Oracle
An index is a database structure used by the server to
have direct access of a row in a table. An index is
automatically created when a unique of primary key
constraint clause is specified in create table comman (Ver
What is a Database instance ? Explain
A database instance (Server) is a set of memory
structure and background processes that access a set of
The process can be shared by all users. The memory
structure that are used to store most queried data from
database. This helps up to improve database performance
by decreasing the amount of I/O performed against data
What is the use of ANALYZE command ?
To perform one of these function on an index, table, or
- To collect statistics about object used by the
optimizer and store them in the data dictionary.
- To delete statistics about the object used by object
from the data dictionary.
- To validate the structure of the object.
- To identify migrated and chained rows of the table or
What is default tablespace ?
The Tablespace to contain schema objects created without
specifying a tablespace name.
What are the system resources that can be controlled
through Profile ?
The number of concurrent sessions the user can establish
the CPU processing time available to the user's session
the CPU processing time available to a single call to
ORACLE made by a SQL statement the amount of logical I/O
available to the user's session the amout of logical I/O
available to a single call to ORACLE made by a SQL
statement the allowed amount of idle time for the user's
session the allowed amount of connect time for the
What is Tablespace Quota ?
The collective amount of disk space available to the
objects in a schema on a particular tablespace.
What are the different Levels of Auditing ?
Statement Auditing, Privilege Auditing and Object
What is Statement Auditing ?
Statement auditing is the auditing of the powerful
system privileges without regard to specifically named
What are the database administrators utilities available
SQL * DBA - This allows DBA to monitor and control an
ORACLE database. SQL * Loader - It loads data from
standard operating system files (Flat files) into ORACLE
database tables. Export (EXP) and Import (imp) utilities
allow you to move existing data in ORACLE format to and
from ORACLE database.
How can you enable automatic archiving ?
Shut the database
Backup the database
Modify/Include LOG_ARCHIVE_START_TRUE in init.ora file.
Start up the database.
What are roles? How can we implement roles ?
Roles are the easiest way to grant and manage common
privileges needed by different groups of database users.
Creating roles and assigning provides to roles. Assign
each role to group of users. This will simplify the job
of assigning privileges to individual users.
What are Roles ?
Roles are named groups of related privileges that are
granted to users or other roles.
What are the use of Roles ?
REDUCED GRANTING OF PRIVILEGES - Rather than explicitly
granting the same set of privileges to many users a
database administrator can grant the privileges for a
group of related users granted to a role and then grant
only the role to each member of the group.
DYNAMIC PRIVILEGE MANAGEMENT - When the privileges of a
group must change, only the privileges of the role need
to be modified. The security domains of all users
granted the group's role automatically reflect the
changes made to the role.
SELECTIVE AVAILABILITY OF PRIVILEGES - The roles granted
to a user can be selectively enable (available for use)
or disabled (not available for use). This allows
specific control of a user's privileges in any given
APPLICATION AWARENESS - A database application can be
designed to automatically enable and disable selective
roles when a user attempts to use the application.
What is Privilege Auditing ?
Privilege auditing is the auditing of the use of
powerful system privileges without regard to
specifically named objects.
What is Object Auditing ?
Object auditing is the auditing of accesses to
specific schema objects without regard to user.
What is Auditing ?
Monitoring of user access to aid in the investigation of
Page Numbers :