利用logminer查詢被刪除記錄的資訊
系統某條記錄被刪除,開發要求提供刪除該記錄的會話資訊;
首先透過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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 查詢刪除表中重複記錄
- 查詢、刪除多個欄位相同的記錄
- 處理表重複記錄(查詢和刪除)
- Oracle 查詢並刪除重複記錄的SQL語句OracleSQL
- Oracle查詢重複資料與刪除重複記錄Oracle
- mysql刪除查詢MySql
- Oracle查詢重複資料與刪除重複記錄方法Oracle
- 回閃查詢查詢刪除的資料
- 轉載:Oracle中查詢和刪除重複記錄方法簡介Oracle
- 【探索】兩種查詢和刪除重複記錄的方法及其效能比較
- 二叉查詢樹的插入刪除查詢
- logminer來恢復在表DDL之前被刪除的資料
- flowable的查詢操作和刪除操作
- 隨機記錄併發查詢與更新(轉移、刪除)的”無恥”優化方法隨機優化
- Mysql語句查詢指定重複記錄和刪除重複記錄僅保留一條【親測可以】MySql
- 刪除不掉的資料記錄
- 閃回刪除、閃回查詢
- 查詢已經刪除的procedure,view,functionViewFunction
- 查詢並刪除Oracle中等待的鎖Oracle
- mysql刪除一條記錄MySql
- 刪除Oracle重複記錄Oracle
- oracle刪除重複記錄Oracle
- 二叉查詢樹(查詢、插入、刪除)——C語言C語言
- [Mysql 查詢語句]——查詢指定記錄MySql
- 找回Oracle中Delete刪除的記錄Oracledelete
- mysql 跨表查詢、更新、刪除示例MySql
- Mac 備忘錄( Notes)裡筆記被永久刪除後的找回方法Mac筆記
- oracle重複資料的查詢及刪除Oracle
- GIS資料的查詢,插入,刪除,更新(ArcEngine)
- 查詢鎖表記錄
- [Logmnr]使用logminer找回被誤刪的資料
- sqlserver查詢最接近的記錄SQLServer
- windows10更新記錄刪除_怎樣刪除win10更新歷史記錄WindowsWin10
- 用“稽核物件”功能記錄檔案刪除記錄物件
- Mongodb原始碼分析--刪除記錄MongoDB原始碼
- Mysql-基本練習(09-刪除單表記錄、查詢指定列資料、列的別名、簡單單表條件查詢、簡單分組查詢)MySql
- 如何刪除oracle庫中相同的記錄Oracle
- db2刪除重複的記錄DB2