使用dbms_logmnr挖掘误操作

最新推荐文章于 2024-10-24 10:24:56 发布

彦祖的小号

最新推荐文章于 2024-10-24 10:24:56 发布

阅读量225

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/linsuhangoracle/article/details/119377091

版权

Oracle 专栏收录该内容

193 篇文章

订阅专栏

SQL> create table t1(id int) tablespace test;
SQL> insert into t1 values(1);
SQL> commit;
SQL> drop table t1 purge;
SQL> alter system switch logfile;
SQL> /
SQL> /
SQL> select name from v$archived_log;
NAME
-----------------------------------------------------------------------
/u01/disk1/timran/arch_1_782662700_129.log
/u01/disk1/timran/arch_1_782662700_130.log
/u01/disk1/timran/arch_1_782662700_131.log
/u01/disk1/timran/arch_1_782662700_132.log
/u01/disk1/timran/arch_1_782662700_133.log //drop table t1 purge
/u01/disk1/timran/arch_1_782662700_134.log
/u01/disk1/timran/arch_1_782662700_135.log

SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ -----------
utl_file_dir string /home/oracle/logmnr

SQL> execute dbms_logmnr_d.build('dict.ora','/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/disk1/timran/arch_1_782662700_133.log ',options=>dbms_logmnr.new);
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/disk1/timran/arch_1_782662700_134.log ',options=>dbms_logmnr.addfile);
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/logmnr/dict.ora',options=>d bms_logmnr.ddl_dict_tracking);

SQL> select username,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss'),sql_redo from v$logmnr_contents WHERE lower(sql_redo) like 'drop table%';

USERNAME SCN TO_CHAR(TIMESTAMP,'YYYY-MM-DDH SQL_REDO
------------------------------ ---------- ------------------------------
SCOTT 1917250 2012-07-18 16:44:55 drop table test purge;
SCOTT 1917267 2012-07-18 16:45:01 drop table student purge;
SCOTT 1918000 2012-08-01 17:28:29 drop table t1 purge;

SQL> execute dbms_logmnr.end_logmnr;