OCM Database Administration & SQL Flashcards

Which Oracle tool is used to analyze and report on the contents of redo log files for auditing and recovery purposes?

Answer: LogMiner

LogMiner (DBMS_LOGMNR) reads online and archived redo logs to provide a SQL-based view of all database changes for auditing and point-in-time recovery analysis.

What is a Materialized View Log used for in Oracle?

Answer: Supporting fast refresh of materialized views

A Materialized View Log records row-level changes on the master table so that an associated materialized view can be refreshed incrementally (fast refresh) rather than fully rebuilt.

In Oracle Database, which isolation level prevents dirty reads and non-repeatable reads but still allows phantom reads by default?

Answer: Read Committed

Oracle's default Read Committed isolation prevents dirty reads by only seeing committed data, but a new query within the same transaction can see newly committed rows (phantoms).

Which Oracle feature automatically reclaims unused space from high-water mark in a segment?

Answer: Segment Advisor with Shrink

The Segment Advisor with the ALTER TABLE … SHRINK SPACE command reclaims free space below the high-water mark by compacting rows and resetting the HWM.

What does the V$SQL view primarily contain in Oracle?

Answer: Cursor statistics and SQL text for statements in the shared pool

V$SQL stores execution statistics, execution plans (via V$SQL_PLAN), and the SQL text for each parsed cursor currently in the shared pool.

Which privilege is required to execute the DBMS_STATS.GATHER_DATABASE_STATS procedure?

Answer: DBA role

The DBA role includes the ANALYZE ANY privilege needed to gather statistics on all objects across the database using DBMS_STATS.