OCM OCM Database Administration & SQL 2 — Questions and Answers
Question 1: Which Oracle tool is used to analyze and report on the contents of redo log files for auditing and recovery purposes?
- RMAN
- LogMiner (Correct answer)
- AWR
- Data Pump
Correct 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.
Question 2: What is a Materialized View Log used for in Oracle?
- Auditing DML on the base table
- Supporting fast refresh of materialized views (Correct answer)
- Capturing DDL changes for replication
- Storing execution plans for complex views
Correct 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.
Question 3: In Oracle Database, which isolation level prevents dirty reads and non-repeatable reads but still allows phantom reads by default?
- Read Uncommitted
- Read Committed (Correct answer)
- Repeatable Read
- Serializable
Correct 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).
Question 4: Which Oracle feature automatically reclaims unused space from high-water mark in a segment?
- Segment Advisor with Shrink (Correct answer)
- Automatic Segment Space Management (ASSM)
- DBMS_SPACE.FREE_BLOCKS
- Locally Managed Tablespaces
Correct 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.
Question 5: What does the V$SQL view primarily contain in Oracle?
- Session wait events
- Cursor statistics and SQL text for statements in the shared pool (Correct answer)
- Historical AWR snapshots
- Active transaction undo information
Correct 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.
Question 6: Which privilege is required to execute the DBMS_STATS.GATHER_DATABASE_STATS procedure?
- SYSDBA
- ANALYZE ANY
- ANALYZE ANY and SELECT ANY TABLE
- DBA role (Correct answer)
Correct answer: DBA role
The DBA role includes the ANALYZE ANY privilege needed to gather statistics on all objects across the database using DBMS_STATS.
OCM OCM Database Administration & SQL 2
Which Oracle tool is used to analyze and report on the contents of redo log files for auditing and recovery purposes?