Oracle Database Question Bank ... 2
(Pages 1 2
3 4
5 6
7 8
9 10)
51. What is a Data Dictionary ?
The data dictionary of an ORACLE database is a set of tables and views that are
used as a read-only reference about the database.
It stores information about both the logical and physical structure of the
database, the valid users of an ORACLE database, integrity constraints defined
for tables in the database and space allocated for a schema object and how much
of it is being used.
52. What is an Integrity Constrains ?
An integrity constraint is a declarative way to define a business rule for a
column of a table.
53. Can an Integrity Constraint be enforced on a table if some existing table
data does not satisfy the constraint ?
No.
54. Describe the different type of Integrity Constraints supported by ORACLE ?
NOT NULL Constraint - Disallows NULLs in a table's column.
UNIQUE Constraint - Disallows duplicate values in a column or set of columns.
PRIMARY KEY Constraint - Disallows duplicate values and NULLs in a column or set
of columns.
FOREIGN KEY Constrain - Require each value in a column or set of columns match a
value in a related table's UNIQUE or PRIMARY KEY.
CHECK Constraint - Disallows values that do not satisfy the logical expression
of the constraint.
55. What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
A column defined as UNIQUE can contain NULLs while a column defined as PRIMARY
KEY can't contain Nulls.
56. Describe Referential Integrity ?
A rule defined on a column (or set of columns) in one table that allows the
insert or update of a row only if the value for the column or set of columns
(the dependent value) matches a value in a column of a related table (the
referenced value). It also specifies the type of data manipulation allowed on
referenced data and the action to be performed on dependent data as a result of
any action on referenced data.
57. What are the Referential actions supported by FOREIGN KEY integrity
constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the
update or deletion of referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent rows
are deleted.
58. What is self-referential integrity constraint ?
If a foreign key reference a parent key of the same table is called
self-referential integrity constraint.
59. What are the Limitations of a CHECK Constraint ?
The condition must be a Boolean expression evaluated using the values in the row
being inserted or updated and can't contain subqueries, sequence, the
SYSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.
60. What is the maximum number of CHECK constraints that can be defined on a
column ?
No Limit.
SYSTEM ARCHITECTURE :
61. What constitute an ORACLE Instance ?
SGA and ORACLE background processes constitute an ORACLE instance. (or)
Combination of memory structure and background process.
62. What is SGA ?
The System Global Area (SGA) is a shared memory region allocated by ORACLE that
contains data and control information for one ORACLE instance.
63. What are the components of SGA ?
Database buffers, Redo Log Buffer the Shared Pool and Cursors.
64. What do Database Buffers contain ?
Database buffers store the most recently used blocks of database data. It can
also contain modified data that has not yet been permanently written to disk.
65. What do Redo Log Buffers contain ?
Redo Log Buffer stores redo entries a log of changes made to the database.
66. What is Shared Pool ?
Shared Pool is a portion of the SGA that contains shared memory constructs such
as shared SQL areas.
67. What is Shared SQL Area ?
A Shared SQL area is required to process every unique SQL statement submitted to
a database and contains information such as the parse tree and execution plan
for the corresponding statement.
68. What is Cursor ?
A Cursor is a handle ( a name or pointer) for the memory associated with a
specific statement.
69. What is PGA ?
Program Global Area (PGA) is a memory buffer that contains data and control
information for a server process.
70. What is User Process ?
A user process is created and maintained to execute the software code of an
application program. It is a shadow process created automatically to facilitate
communication between the user and the server process.
71. What is Server Process ?
Server Process handle requests from connected user process. A server process is
in charge of communicating with the user process and interacting with ORACLE
carry out requests of the associated user process.
72. What are the two types of Server Configurations ?
Dedicated Server Configuration and Multi-threaded Server Configuration.
73. What is Dedicated Server Configuration ?
In a Dedicated Server Configuration a Server Process handles requests for a
Single User Process.
74. What is a Multi-threaded Server Configuration ?
In a Multi-threaded Server Configuration many user processes share a group of
server process.
75. What is a Parallel Server option in ORACLE ?
A configuration for loosely coupled systems where multiple instance share a
single physical database is called Parallel Server.
76. Name the ORACLE Background Process ?
DBWR - Database Writer.
LGWR - Log Writer
CKPT - Check Point
SMON - System Monitor
PMON - Process Monitor
ARCH - Archiver
RECO - Recover
Dnnn - Dispatcher, and
LCKn - Lock
Snnn - Server.
77. What Does DBWR do ?
Database writer writes modified blocks from the database buffer cache to the data files.
78.When Does DBWR write to the database ?
DBWR writes when more data needs to be read into the SGA and too few database
buffers are free. The least recently used data is written to the data files first. DBWR also writes when CheckPoint occurs.
79. What does LGWR do ?
Log Writer (LGWR) writes redo log entries generated in the redo log buffer of the SGA to on-line Redo Log File.
80. When does LGWR write to the database?
LGWR writes redo log entries into an on-line redo log file when transactions commit and the log buffer files are full.
81. What is the function of checkpoint CKPT)?
The Checkpoint (CKPT) process is responsible for signaling DBWR at checkpoints
and updating all the data files and control files of the database.
82. What are the functions of SMON ?
System Monitor (SMON) performs instance recovery at instance start-up. In a
multiple instance system (one that uses the Parallel Server), SMON of one
instance can also perform instance recovery for other instance that have failed
SMON also cleans up temporary segments that are no longer in use and recovers
dead transactions skipped during crash and instance recovery because of
file-read or off-line errors. These transactions are eventually recovered by
SMON when the tablespace or file is brought back on-line SMON also coalesces
free extents within the database to make free space contiguous and easier to allocate.
83. What are functions of PMON ?
Process Monitor (PMON) performs process recovery when a user process fails PMON
is responsible for cleaning up the cache and Freeing resources that the process
was using PMON also checks on dispatcher and server processes and restarts them if they have failed.
84. What is the function of ARCH ?
Archiver (ARCH) copies the on-line redo log files to archival storage when they
are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode.
85. What is function of RECO ?
RECOver (RECO) is used to resolve distributed transactions that are pending due
to a network or system failure in a distributed database. At timed intervals,the
local RECO attempts to connect to remote databases and automatically complete
the commit or rollback of the local portion of any pending distributed transactions.
86. What is the function of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is responsible for routing requests from connected
user processes to available shared server processes and returning the responses
back to the appropriate user processes.
87. How many Dispatcher Processes are created ?
Atleast one Dispatcher process is created for every communication protocol in use.
88. What is the function of Lock (LCKn) Process ?
Lock (LCKn) are used for inter-instance locking when the ORACLE Parallel Server
option is used.
89. What is the maximum number of Lock Processes used ?
Though a single LCK process is sufficient for most Parallel Server systems
upto Ten Locks (LCK0,....LCK9) are used for inter-instance locking.
DATA ACCESS
90. Define Transaction ?
A Transaction is a logical unit of work that comprises one or more SQL
statements executed by a single user.
91. When does a Transaction end ?
When it is committed or Rollbacked.
92. What does COMMIT do ?
COMMIT makes permanent the changes resulting from all SQL statements in the
transaction. The changes made by the SQL statements of a transaction become
visible to other user sessions transactions that start only after transaction is
committed.
93. What does ROLLBACK do ?
ROLLBACK retracts any of the changes resulting from the SQL statements in the
transaction.
94. What is SAVE POINT ?
For long transactions that contain many SQL statements, intermediate markers or
savepoints can be declared which can be used to divide a transaction into
smaller parts. This allows the option of later rolling back all work performed
from the current point in the transaction to a declared savepoint within the
transaction.
95. 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.
96. What is the function of Optimizer ?
The goal of the optimizer is to choose the most efficient way to execute a SQL
statement.
97. What is Execution Plan ?
The combinations of the steps the optimizer chooses to execute a statement is
called an execution plan.
98. What are the different approaches used by Optimizer in choosing an execution
plan ?
Rule-based and Cost-based.
99. What are the factors that affect OPTIMIZER in choosing an Optimization
approach ?
The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary
the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the
statement.
100. What are the values that can be specified for OPTIMIZER MODE Parameter ?
COST and RULE.