New Features for Administrators - Question 077

Your company has four DBAs. You need to know which DBA added a column to the JOB_HISTORY table. The database cannot be offline during work hours. Which LogMiner command can you use to find out which user made the table change?

A. SELECT username,operation,sql_redo from V$logmnr_contents where operation='DDL';

B. SELECT username,operation,sql_redo from V$logmnr_logs where operation='DDL';

C. You must wait until after hours, shut the database down, and issue the command EXECUTE DBMS_LOGMNR_D.BUILD to extract the information from the database.

D. The task cannot be done. LogMiner does not track DDL commands in Oracle9i.

Answer: A

Explanation:

Prior to Oracle9i, DDL statements were recorded as a set of DML statements on internal tables. It was no simple task to review these DML statements and realize that a DDL operation had been performed. You could almost forget this when trying to determine the actual DDL statement that sourced these DML statements. Oracle 9i records the original DDL statement in the redo logs, and LogMiner lists this DDL statement followed by the set of generated DML statements. When you query V$LOGMNR_CONTENTS, you can see DDL under the OPERATION column, and the DDL statement itself under the SQL_REDO column.

Incorrect Answers

B: This view does not provide the requested information. There are no USERNAME, OPERATION, SQL_REDO and OPERATION columns in this view.

C: You don’t need to wait until after hours, shutdown database to retrieve this information. The V$LOGMNR_CONTENTS dynamic view can be used for this purpose.

D: This task can be done with the V$LOGMNR_CONTENTS dynamic view.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 100-110 Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 68-72 Chapter 3: New Oracle9i Availability and Recoverability Features