OCM Database Administration & SQL Flashcards

Which Oracle feature allows you to recover a dropped table without restoring from a backup?

Answer: Flashback Table

Flashback Table uses the Oracle Recycle Bin to restore a dropped table to its state before the DROP command.

What is the purpose of the UNDO tablespace in Oracle Database?

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.

Which SQL hint forces the Oracle optimizer to use a full table scan instead of an index?

Answer: /*+ FULL(table) */

The FULL hint instructs the Cost-Based Optimizer to perform a full table scan on the specified table, bypassing available indexes.

In Oracle, what does the PCTFREE storage parameter control?

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.

Which Oracle background process is responsible for writing dirty buffers from the buffer cache to datafiles?

Answer: DBWR

DBWR (Database Writer) writes modified (dirty) buffers from the database buffer cache to the datafiles on disk.

What is the effect of setting the OPTIMIZER_MODE parameter to FIRST_ROWS_10?

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.