系统某条记录被删除,开发要求提供删除该记录的会话信息;
首先通过flashback query找到确切的操作时间
SQL> select count(*) from a as of timestamp to_date('2011-05-25 12:36:12','yyyy-mm-dd hh24:mi:ss') where id = 1128661;
COUNT(*)
----------
0
SQL> select count(*) from a as of timestamp to_date('2011-05-25 12:36:11','yyyy-mm-dd hh24:mi:ss') where id = 1128661;
COUNT(*)
----------
1
以前查询确定该记录是12:36:11 --12:36:12之间执行删除的
然后利用logminer来挖掘
SQL> BEGIN
2 DBMS_LOGMNR.START_LOGMNR(
3 STARTTIME => to_date('2011-05-25 12:36:10', 'yyyy-mm-dd hh24:mi:ss') ,
4 ENDTIME => to_date('2011-05-25 12:36:13', 'yyyy-mm-dd hh24:mi:ss'),
5 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
210
SQL> create table logmnr_0526 as select * from v$logmnr_contents;
Table created.
SQL> select session#,serial#,username,session_info,sql_redo from logmnr_0526 where timestamp between to_date('2011-05-25 12:36:11','yyyy-mm-dd hh24:mi:ss') and to_date('2011-05-25 12:36:13','yyyy-mm-dd hh24:mi:ss')
2 and table_name ='a' and sql_redo like 'delete%'
3 ;
SESSION# SERIAL# USERNAME SESSION_INFO SQL_REDO
---------- ---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2025 40571 UNKNOWN UNKNOWN delete from "JUSTIN"."a" where "ID" = '1128661' and "PRODUCT_CO
该sql的username和session_info均为unknown,查找v$session,还好还有记录;
SQL> select logon_time,username,machine,serial# from v$session where sid =2025;
LOGON_TIME USERNAME MACHINE SERIAL#
----------- ------------------------------ ---------------------------------------------------------------- ----------
2011-5-13 上 JUSTIN Larry-001 40571
首先通过flashback query找到确切的操作时间
SQL> select count(*) from a as of timestamp to_date('2011-05-25 12:36:12','yyyy-mm-dd hh24:mi:ss') where id = 1128661;
COUNT(*)
----------
0
SQL> select count(*) from a as of timestamp to_date('2011-05-25 12:36:11','yyyy-mm-dd hh24:mi:ss') where id = 1128661;
COUNT(*)
----------
1
以前查询确定该记录是12:36:11 --12:36:12之间执行删除的
然后利用logminer来挖掘
SQL> BEGIN
2 DBMS_LOGMNR.START_LOGMNR(
3 STARTTIME => to_date('2011-05-25 12:36:10', 'yyyy-mm-dd hh24:mi:ss') ,
4 ENDTIME => to_date('2011-05-25 12:36:13', 'yyyy-mm-dd hh24:mi:ss'),
5 OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
210
SQL> create table logmnr_0526 as select * from v$logmnr_contents;
Table created.
SQL> select session#,serial#,username,session_info,sql_redo from logmnr_0526 where timestamp between to_date('2011-05-25 12:36:11','yyyy-mm-dd hh24:mi:ss') and to_date('2011-05-25 12:36:13','yyyy-mm-dd hh24:mi:ss')
2 and table_name ='a' and sql_redo like 'delete%'
3 ;
SESSION# SERIAL# USERNAME SESSION_INFO SQL_REDO
---------- ---------- ------------------------------ -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
2025 40571 UNKNOWN UNKNOWN delete from "JUSTIN"."a" where "ID" = '1128661' and "PRODUCT_CO
该sql的username和session_info均为unknown,查找v$session,还好还有记录;
SQL> select logon_time,username,machine,serial# from v$session where sid =2025;
LOGON_TIME USERNAME MACHINE SERIAL#
----------- ------------------------------ ---------------------------------------------------------------- ----------
2011-5-13 上 JUSTIN Larry-001 40571
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-697298/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-697298/