oracle logmnr文件

6月28日
logmnr
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string                                   

 
SQL> alter system set utl_file_dir='d:\oracle\oradir' scope=spfile;
系統已更改。
 
SQL> startup force
ORACLE 例程已經啟動。
Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
資料庫裝載完畢。
資料庫已經開啟。

 
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      d:\oracle\oradir

 
SQL> conn test/test
已連線。
SQL> select * from test1;
A          B
---------- ----------
11         1
22         2
 
SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-11-28 15:52:19
 
SQL> update test1 set a = 1 where b = 1;
已更新 1 行。
 
SQL> update test1 set a = 2 where b = 2;
已更新 1 行。
 
SQL> commit;
提交完成。
 
SQL>  select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2007-11-28 15:52:42
 
SQL> exec sys.dbms_logmnr_d.build(dictionary_filename => 'dir1.txt',dictionary_location =>

'd:\oracle\oradir' );
PL/SQL 過程已成功完成。
 
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\oradata\ora\REDO04.LOG',Options =>

sys.dbms_logmnr.NEW);
PL/SQL 過程已成功完成。
 
SQL> exec sys.dbms_logmnr.add_logfile(LogFileName => 'D:\oracle\oradata\ora\REDO05.LOG',Options =>

sys.dbms_logmnr.ADDFILE);
PL/SQL 過程已成功完成。
 
SQL> exec sys.dbms_logmnr.start_logmnr(startTime => to_date('2007-11-28 15:52:19', 'yyyy-mm-dd

hh24:mi:ss'),endTime => to_date('2007-11-28 15:52:42', 'yyyy-mm-dd hh24:mi:ss'),DictFileName =>

'd:\oracle\oradir\dir1.txt');
PL/SQL 過程已成功完成。
 
SQL> set wrap off
SQL> set linesize 1000

 
SQL> select SQL_REDO from v$logmnr_contents where SEG_NAME = 'TEST1';
SQL_REDO
-------------------------------------------------------------------------------
update "TEST"."TEST1" set "A" = '1' where "A" = '11' and ROWID = 'AAAHfOAALAAAAEyAAA';
update "TEST"."TEST1" set "A" = '2' where "A" = '22' and ROWID = 'AAAHfOAALAAAAEyAAB';
 
SQL> select SQL_UNDO from v$logmnr_contents where SEG_NAME = 'TEST1';
SQL_UNDO
-------------------------------------------------------------------------------
update "TEST"."TEST1" set "A" = '11' where "A" = '1' and ROWID = 'AAAHfOAALAAAAEyAAA';
update "TEST"."TEST1" set "A" = '22' where "A" = '2' and ROWID = 'AAAHfOAALAAAAEyAAB';
SQL> exec sys.dbms_logmnr.end_logmnr;
PL/SQL 過程已成功完成。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/235507/viewspace-555754/，如需轉載，請註明出處，否則將追究法律責任。