Configuring Logminer in Oracle for DDL & DML

Tracking DDL Statements

LogMiner automatically builds its own internal dictionary from the source dictionary that you specify when you start LogMiner (either a flat file dictionary, a dictionary in the redo logs, or an online catalog).

If your source dictionary is a flat file dictionary or a dictionary in the redo logs, you can use the DDL_DICT_TRACKING option to direct LogMiner to track data definition language (DDL) statements. DDL tracking is disabled by default. To enable it, use the OPTIONS parameter to specify DDL_DICT_TRACKING when you start LogMiner. For example:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
2 DBMS_LOGMNR.DDL_DICT_TRACKING);

With this option set, LogMiner applies any DDL statements seen in the redo logs to its internal dictionary. For example, to see all the DDLs executed by user SYS, you could issue the following query:

SQL> SELECT USERNAME, SQL_REDO
2 FROM V$LOGMNR_CONTENTS
3 WHERE USERNAME = 'SYS' AND OEPRATION = 'DDL';

The information returned might be similar to the following, although the actual information and how it is displayed will be different on your screen.

USERNAME SQL_REDO
SYS ALTER TABLE SCOTT.ADDRESS ADD CODE NUMBER;
SYS CREATE USER KATHY IDENTIFIED BY VALUES 'E4C8B920449B4C32' DEFAULT
TABLESPACE TS1;

Keep the following in mind when you use the DDL_DICT_TRACKING option:

* The DDL_DICT_TRACKING option is not valid with the DICT_FROM_ONLINE_CATALOG option.
* The DDL_DICT_TRACKING option requires that the database be open.

The ability to track DDL statements helps you monitor schema evolution because SQL statements used to change the logical structure of a table (because of DDL operations such as adding or dropping of columns) can be reconstructed. In addition, data manipulation language (DML) operations performed on new tables created after the dictionary was extracted can also be shown.

Only Committed Transactions

When you use the COMMITTED_DATA_ONLY option, only rows belonging to committed transactions are shown in the V$LOGMNR_CONTENTS view. This enables you to filter out rolled back transactions, transactions that are in progress, and internal operations.

To enable this option, you specify it when you start LogMiner, as follows:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
2 DBMS_LOGMNR.COMMITTED_DATA_ONLY);

When you specify the COMMITTED_DATA_ONLY option, LogMiner groups together all DML operations that belong to the same transaction. Transactions are returned in the order in which they were committed.

If long-running transactions are present in the redo logs being analyzed, use of this option may cause an "Out of Memory" error.

The default is for LogMiner to show rows corresponding to all transactions and to return them in the order in which they are encountered in the redo logs.

For example, suppose you start LogMiner without specifying COMMITTED_DATA_ONLY and you execute the following query:

SQL> SELECT (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
2 USERNAME AS USER,
3 SQL_REDO AS SQL_REDO
4 FROM V$LOGMNR_CONTENTS;

The output would be as follows. Both committed and uncommitted transactions are returned and rows from different transactions are interwoven.

XID USER SQL_REDO
1.5.123 SCOTT SET TRANSACTION READ WRITE;
1.5.123 SCOTT INSERT INTO "SCOTT"."EMP"("EMPNO","ENAME")
VALUES (8782, 'Frost');
1.6.124 KATHY SET TRANSACTION READ WRITE;
1.6.124 KATHY INSERT INTO "SCOTT"."CUSTOMER"("ID","NAME","PHONE_DAY")
VALUES (8839, 'Cummings', '415-321-1234');
1.6.124 KATHY INSERT INTO "SCOTT"."CUSTOMER"("ID","NAME","PHONE_DAY")
VALUES (7934, 'Yeats', '033-334-1234');
1.5.123 SCOTT INSERT INTO "SCOTT"."EMP" ("EMPNO","ENAME")
VALUES (8566, 'Browning');
1.6.124 KATHY COMMIT;
1.7.234 GOUTAM SET TRANSACTION READ WRITE;
1.5.123 SCOTT COMMIT;
1.7.234 GOUTAM INSERT INTO "SCOTT"."CUSTOMER"("ID","NAME","PHONE_DAY")
VALUES (8499, 'Emerson', '202-334-1234');