OCM OCM Database Administration & SQL 1 — Questions and Answers
Question 1: Which Oracle feature allows you to recover a dropped table without restoring from a backup?
- Flashback Table (Correct answer)
- LogMiner
- RMAN Block Recovery
- Data Guard Redo Apply
Correct answer: Flashback Table
Flashback Table uses the Oracle Recycle Bin to restore a dropped table to its state before the DROP command.
Question 2: What is the purpose of the UNDO tablespace in Oracle Database?
- Storing archived redo logs
- Providing read consistency and supporting transaction rollback (Correct answer)
- Holding temporary sort segments
- Caching frequently accessed index blocks
Correct answer: Providing read consistency and supporting transaction rollback
The UNDO tablespace stores before-images of modified data to support transaction rollback and provide consistent reads for concurrent queries.
Question 3: Which SQL hint forces the Oracle optimizer to use a full table scan instead of an index?
- /*+ INDEX(table) */
- /*+ FULL(table) */ (Correct answer)
- /*+ NO_MERGE */
- /*+ PARALLEL */
Correct answer: /*+ FULL(table) */
The FULL hint instructs the Cost-Based Optimizer to perform a full table scan on the specified table, bypassing available indexes.
Question 4: In Oracle, what does the PCTFREE storage parameter control?
- Percentage of block space reserved for future row updates (Correct answer)
- Minimum percentage of a block that must be full before rows are added
- Number of free lists for concurrent inserts
- Maximum percentage of extents that can be free
Correct answer: Percentage of block space reserved for future row updates
PCTFREE specifies the percentage of each data block kept free to accommodate future updates that increase the size of existing rows.
Question 5: Which Oracle background process is responsible for writing dirty buffers from the buffer cache to datafiles?
- LGWR
- SMON
- DBWR (Correct answer)
- CKPT
Correct answer: DBWR
DBWR (Database Writer) writes modified (dirty) buffers from the database buffer cache to the datafiles on disk.
Question 6: What is the effect of setting the OPTIMIZER_MODE parameter to FIRST_ROWS_10?
- Optimizer minimizes total resource consumption for all rows
- Optimizer chooses plans that return the first 10 rows as fast as possible (Correct answer)
- Optimizer limits result sets to 10 rows
- Optimizer uses 10 parallel threads for query execution
Correct answer: Optimizer chooses plans that return the first 10 rows as fast as possible
FIRST_ROWS_10 instructs the optimizer to favor execution plans that deliver the first 10 rows of a result set with the lowest latency.
OCM OCM Database Administration & SQL 1
Which Oracle feature allows you to recover a dropped table without restoring from a backup?