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');
EXECUTEdbms_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 this 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_REDO
FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'TEST' AND OPERATION = 'DDL';
