SQL> show parameter utl_file

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
utl_file_dir string D:oracleadminfun1cdump


SQL> update scott.emp set comm=999 where empno='7566';

已更新 1 個資料列.

SQL> update scott.dept set dname='LJG.ERIC' where dname='DDA';

已更新 1 個資料列.

SQL> commit;

確認完成.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 50 104857600 1 NO INACTIVE 5.9760E+12 11-3月 -06
2 1 51 104857600 1 NO INACTIVE 5.9760E+12 18-3月 -06
3 1 53 104857600 1 NO CURRENT 5.9760E+12 22-3月 -06
4 1 0 10485760 1 YES UNUSED 0

--GROUP 3 為當前的日志組

SQL> execute dbms_logmnr_d.build('fun1dict.ora','D:oracleadminfun1cdump')

PL/SQL 程序順利完成.

SQL> execute dbms_logmnr.add_logfile('D:oracleoradatafun1REDO03.LOG',dbms_logmnr.new);

PL/SQL 程序順利完成.

SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'D:oracleadminfun1cdumpfun1dict.ora');

PL/SQL 程序順利完成.

--查看v$logmnr_contents視圖(分別查sql_redo﹑sql_undo欄位
--EMP表的查詢
SQL> SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='EMP';

SQL_REDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "COMM" = '999' where "COMM" IS NULL and ROWID = 'AAAHW7AABAAAMUiAAD';

SQL> SELECT sql_undo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='EMP';

SQL_UNDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "COMM" = NULL where "COMM" = '999' and ROWID = 'AAAHW7AABAAAMUiAAD';

--DEPT表的查詢
SQL> SELECT sql_redo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='DEPT';

SQL_REDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."DEPT" set "DNAME" = 'LJG.ERIC' where "DNAME" = 'DDA' and ROWID = 'AAAHW5AABAAAMUSAAH

SQL> SELECT sql_undo FROM v$logmnr_contents WHERE username='SYS' AND seg_name='DEPT';

SQL_UNDO
----------------------------------------------------------------------------------------------------
update "SCOTT"."DEPT" set "DNAME" = 'DDA' where "DNAME" = 'LJG.ERIC' and ROWID = 'AAAHW5AABAAAMUSAAH

--結束日志分析
SQL> execute DBMS_LOGMNR.END_LOGMNR;