How to use Oracle LogMiner for tracing DDL

With the help of Oracle LogMiner we can trace the DDL fire. Please note that this is session dependent.

Below are the steps to be followed.

1)Set the parameter UTL_FILE_DIR (this is static parameter u need to take bounce of db to reflect it)

Sql>alter system set UTL_FILE_DIR=’D:\oracle\oradata\utl’ scope=spfile;

And take the bounce of database.

2)Add the logminer dictionary
Sql>
EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'D:\oracle\oradata\utl',OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

This will create one flat file 'Dictionary.ora' in the give UTL_FILE_DIR location.

3)Add the archive log file to Mine
Now u have to get the time between view u have created (approx ) and from ls –ltr u can see the archive log generated time.

Sql>
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.new,LogFileName=>'D:\oracle\oradata\archive\1_52_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\oracle\oradata\archive\1_51_743911224.LOG');
EXECUTE dbms_logmnr.add_logfile(Options=>dbms_logmnr.addfile,LogFileName=>'D:\ORACLE\ORADATA\ODSPROD\REDO01.LOG');

Now check whether this archived log register with logminer

Sql>select filename from V$LOGMNR_LOGS;

4) Start the logmining

Sql>
EXECUTE dbms_logmnr.start_logmnr(DictFileName=>'D:\oracle\oradata\utl\Dictionary.ora',OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING);

5) Once its successfully executed now u can see the DDL statement.

Sql> SELECT USERNAME, SQL_REDOFROM V$LOGMNR_CONTENTS WHERE USERNAME = 'TEST' AND OPERATION = 'DDL';

ORA-14450: attempt to access a transactional temp table already in use

Step to resolve this error.

1)Lock the table in exclusive mode

sql>lock table <username>.<temp_table> in exclusive mode;

2)Leave this session as it is and take new session

3) Take another session and execute below command

sql>SELECT * FROM v$lock
    WHERE id1 = (SELECT object_id FROM all_objects WHERE owner =<schema_name> AND object_name =<glb temp table>);

sql>ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

4)After all the session get killed (confirm once)

5)perform the DDL on table;

sql>ALTER TABLE <username>.<temp_table>MODIFY(CONVERSION_FACTOR_VAL NUMBER (20,9));

6)Release the lock which we held in step (1) by below command

sql>rollback;