溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹sql中如何使用dbms_logmnr，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！
講到不完全恢復(fù)（找回drop table）時，經(jīng)常需要確定drop table的確切時間，所以經(jīng)常需要用到dbms_logmner
SQL> desc dbms_logmnr_d
PROCEDURE BUILD
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME VARCHAR2 IN DEFAULT
DICTIONARY_LOCATION VARCHAR2 IN DEFAULT
OPTIONS NUMBER IN DEFAULT
PROCEDURE SET_TABLESPACE
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE VARCHAR2 IN
--過程set_tablespace可以把logmnr相關(guān)的對象從sysaux表空間轉(zhuǎn)移到set_tablespace設(shè)置的表空間
SQL> exec dbms_logmnr_d.set_tablespace('TEST');
PL/SQL 過程已成功完成。
SQL> select count(*) from dba_segments where tablespace_name='TEST';
COUNT(*)
----------
99
SQL> exec dbms_logmnr_d.set_tablespace('SYSAUX');
PL/SQL 過程已成功完成。
SQL> select count(*) from dba_segments where tablespace_name='TEST';
COUNT(*)
----------
4
SQL> desc dbms_logmnr
PROCEDURE ADD_LOGFILE
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
OPTIONS BINARY_INTEGER IN DEFAULT
FUNCTION COLUMN_PRESENT RETURNS BINARY_INTEGER
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE END_LOGMNR
FUNCTION MINE_VALUE RETURNS VARCHAR2
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
SQL_REDO_UNDO NUMBER IN DEFAULT
COLUMN_NAME VARCHAR2 IN DEFAULT
PROCEDURE REMOVE_LOGFILE
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
LOGFILENAME VARCHAR2 IN
PROCEDURE START_LOGMNR
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
STARTSCN NUMBER IN DEFAULT
ENDSCN NUMBER IN DEFAULT
STARTTIME DATE IN DEFAULT
ENDTIME DATE IN DEFAULT
DICTFILENAME VARCHAR2 IN DEFAULT
OPTIONS BINARY_INTEGER IN DEFAULT
--設(shè)置參數(shù)utl_file_dir
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> exec dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlo
gmnr');
BEGIN dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlogmnr
'); END;
*
第 1 行出現(xiàn)錯誤:
ORA-01308: 未設(shè)置初始化參數(shù) utl_file_dir
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3474
ORA-06512: 在 "SYS.DBMS_LOGMNR_INTERNAL", line 3552
ORA-06512: 在 "SYS.DBMS_LOGMNR_D", line 12
ORA-06512: 在 line 1
SQL> alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr';
alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr'
*
第 1 行出現(xiàn)錯誤:
ORA-02095: 無法修改指定的初始化參數(shù)
SQL> alter system set utl_file_dir='E:oracleproduct10.2.0admintestlogmnr' scope=spfile;
系統(tǒng)已更改。
SQL> shutdown immediate
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
SQL> startup
ORACLE 例程已經(jīng)啟動。
Total System Global Area 163577856 bytes
Fixed Size 1247876 bytes
Variable Size 92276092 bytes
Database Buffers 67108864 bytes
Redo Buffers 2945024 bytes
數(shù)據(jù)庫裝載完畢。
數(shù)據(jù)庫已經(jīng)打開。
SQL> exec dbms_logmnr_d.build('dict.ora','E:oracleproduct10.2.0admintestlo
gmnr');
PL/SQL 過程已成功完成。
SQL>
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版權(quán)所有 1985-2001 Microsoft Corp.
C:>e:
E:>cd E:oracleproduct10.2.0admintestlogmnr
E:oracleproduct10.2.0admintestlogmnr>dir
驅(qū)動器 E 中的卷沒有標(biāo)簽。
卷的序列號是 F6E5-4B31
E:oracleproduct10.2.0admintestlogmnr 的目錄
2008-09-26 13:11 <DIR> .
2008-09-26 13:11 <DIR> ..
2008-09-26 13:11 10,871,850 dict.ora
1 個文件 10,871,850 字節(jié)
2 個目錄 3,143,331,840 可用字節(jié)
E:oracleproduct10.2.0admintestlogmnr>exit
SQL> show user
USER 為 "SYS"
SQL> connect test/test
已連接。
SQL> set time on
13:13:04 SQL> desc tt
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
13:13:08 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;
GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- --------------
1 CURRENT 26 1576118 26-9月 -08
2 INACTIVE 25 1570052 26-9月 -08
3 INACTIVE 24 1563812 26-9月 -08
13:13:42 SQL> alter session set nls_date_format='yyyy/mm/dd hh34:mi:ss';
會話已更改。
13:13:58 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;
GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
1 CURRENT 26 1576118 2008/09/26 12:56:37
2 INACTIVE 25 1570052 2008/09/26 12:53:48
3 INACTIVE 24 1563812 2008/09/26 10:16:24
13:14:00 SQL> select *from tt;
未選定行
13:14:38 SQL> insert into tt values(1,'a');
已創(chuàng)建 1 行。
13:14:43 SQL> insert into tt values(2,'b');
已創(chuàng)建 1 行。
13:14:52 SQL> commit;
提交完成。
13:14:56 SQL> alter system switch logfile;
系統(tǒng)已更改。
13:15:03 SQL> alter system checkpoint;
系統(tǒng)已更改。
13:15:10 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;
GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
1 INACTIVE 26 1576118 2008/09/26 12:56:37
2 INACTIVE 25 1570052 2008/09/26 12:53:48
3 CURRENT 27 1580527 2008/09/26 13:15:02
13:15:13 SQL> update tt set id=100 ,name='logmnr';
已更新2行。
13:15:58 SQL> commit;
提交完成。
13:16:00 SQL> alter system switch logfile;
系統(tǒng)已更改。
13:16:04 SQL> alter system checkpoint;
系統(tǒng)已更改。
13:16:05 SQL> select group#,status,sequence#,first_change#,first_time from v$log
;
GROUP# STATUS SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ---------------- ---------- ------------- -------------------
1 INACTIVE 26 1576118 2008/09/26 12:56:37
2 CURRENT 28 1580563 2008/09/26 13:16:04
3 INACTIVE 27 1580527 2008/09/26 13:15:02
13:16:06 SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG
13:32:51 SQL> select sequence#, first_change#,first_time,next_change#,next_time
from v$archived_log where sequence# in (26,27,28)
13:34:08 2 and resetlogs_id=666280390;
SEQUENCE# FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------------- ------------ -------------------
26 1576118 2008/09/26 12:56:37 1580527 2008/09/26 13:15:02
27 1580527 2008/09/26 13:15:02 1580563 2008/09/26 13:16:04
13:34:10 SQL> col name format a80
13:35:08 SQL> select name from v$archived_log where sequence# in (26,27,28)
13:35:16 2 and resetlogs_id=666280390;
NAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1
13:35:18 SQL>
13:38:46 SQL> show user
USER 為 "TEST"
13:38:48 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',1);
BEGIN dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVER
Y_FILE_DESTARC26_666280390_1',1); END;
*
第 1 行出現(xiàn)錯誤:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必須聲明標(biāo)識符 'DBMS_LOGMNR.ADD_LOGFILE'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
13:40:31 SQL> connect / as sysdba
已連接。
13:40:57 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',dbms_logmnr.new);
PL/SQL 過程已成功完成。
--dbms_logmnr.new的作用是清除掉之前(add_logfile)加進(jìn)來的logfile
13:41:06SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1',dbms_logmnr.new);
PL/SQL 過程已成功完成。
13:42:33 SQL> exec dbms_logmnr.remove_logfile('E:ORACLEPRODUCT10.2.0DB_2DAT
ABASEDB_RECOVERY_FILE_DESTARC27_666280390_1');
PL/SQL 過程已成功完成。
13:42:37 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1',dbms_logmnr.new);
PL/SQL 過程已成功完成。
13:42:45 SQL>
13:46:52 SQL> select filename from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1
13:46:58 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC26_666280390_1',dbms_logmnr.new);
PL/SQL 過程已成功完成。
13:47:12 SQL> select filename from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1
13:47:27 SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0DB_2DATABA
SEDB_RECOVERY_FILE_DESTARC27_666280390_1');
PL/SQL 過程已成功完成。
13:47:41 SQL> desc v$logmnr_logs;
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
LOG_ID NUMBER
FILENAME VARCHAR2(512)
LOW_TIME DATE
HIGH_TIME DATE
DB_ID NUMBER
DB_NAME VARCHAR2(8)
RESET_SCN NUMBER
RESET_SCN_TIME DATE
THREAD_ID NUMBER
THREAD_SQN NUMBER
LOW_SCN NUMBER
NEXT_SCN NUMBER
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
TYPE VARCHAR2(7)
BLOCKSIZE NUMBER
FILESIZE NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
13:47:39 SQL> select filename from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC26_666280390_1
E:ORACLEPRODUCT10.2.0DB_2DATABASEDB_RECOVERY_FILE_DESTARC27_666280390_1
13:47:41 SQL>
13:51:01 SQL> select log_id,low_scn,low_time,next_scn,high_time from v$logmnr_lo
gs;
LOG_ID LOW_SCN LOW_TIME NEXT_SCN HIGH_TIME
---------- ---------- ------------------- ---------- -------------------
26 1576118 2008/09/26 12:56:37 1580527 2008/09/26 13:15:02
27 1580527 2008/09/26 13:15:02 1580563 2008/09/26 13:16:04
13:51:09 SQL>
13:51:09 SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'E:oracleproduct10.
2.0admintestlogmnrdict.ora',startscn=>1576118,endscn=>1580563);
PL/SQL 過程已成功完成。
13:55:42 SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
8648
13:56:15 SQL> desc v$logmnr_contents
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
SCN NUMBER
CSCN NUMBER
TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
THREAD# NUMBER
LOG_ID NUMBER
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJD# NUMBER
SEG_OWNER VARCHAR2(32)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(32)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(32)
ROW_ID VARCHAR2(18)
SESSION# NUMBER
SERIAL# NUMBER
USERNAME VARCHAR2(30)
SESSION_INFO VARCHAR2(4000)
TX_NAME VARCHAR2(256)
ROLLBACK NUMBER
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SEQUENCE# NUMBER
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(32)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SQL_COLUMN_TYPE VARCHAR2(30)
SQL_COLUMN_NAME VARCHAR2(30)
REDO_LENGTH NUMBER
REDO_OFFSET NUMBER
UNDO_LENGTH NUMBER
UNDO_OFFSET NUMBER
DATA_OBJV# NUMBER
SAFE_RESUME_SCN NUMBER
XID RAW(8)
PXID RAW(8)
AUDIT_SESSIONID NUMBER
14:08:10 SQL> select rbasqn,rbablk,rbabyte from v$logmnr_contents where seg_owne
r='TEST' and seg_name='TT';
RBASQN RBABLK RBABYTE
---------- ---------- ----------
26 7544 400
26 7546 16
27 30 16
27 30 468
14:08:33 SQL>
14:14:58 SQL> select scn,timestamp , sql_redo from v$logmnr_contents where seg_o
wner='TEST' and seg_name='TT';
SCN TIMESTAMP
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
--------------------
1580518 2008/09/26 13:14:47
insert into "TEST"."TT"("ID","NAME") values ('1','a');
1580520 2008/09/26 13:14:53
insert into "TEST"."TT"("ID","NAME") values ('2','b');
1580558 2008/09/26 13:15:59
update "TEST"."TT" set "ID" = '100', "NAME" = 'logmnr' where "ID" = '1' and "NAM
E" = 'a' and ROWID =
'AAAC+uAACAAAAMPAAA';
SCN TIMESTAMP
---------- -------------------
SQL_REDO
--------------------------------------------------------------------------------
--------------------
1580558 2008/09/26 13:15:59
update "TEST"."TT" set "ID" = '100', "NAME" = 'logmnr' where "ID" = '2' and "NAM
E" = 'b' and ROWID =
'AAAC+uAACAAAAMPAAB';
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 29
2 INACTIVE 28
3 INACTIVE 27
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG
SQL> select * from test.tt;
ID NAME
---------- ----------
1 a
SQL> CONNECT TEST/TEST
已連接。
SQL> delete from tt;
已刪除 1 行。
SQL> commit;
提交完成。
SQL> alter system checkpoint;
系統(tǒng)已更改。
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 CURRENT 29
2 INACTIVE 28
3 INACTIVE 27
SQL> connect / as sysdba
已連接。
SQL> exec dbms_logmnr.add_logfile('E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.
LOG');
PL/SQL 過程已成功完成。
SQL> select filename from v$logmnr_logs;
FILENAME
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
SQL> exec dbms_logmnr.start_logmnr;
PL/SQL 過程已成功完成。
SQL> select count(*) from v$logmnr_contents;
COUNT(*)
----------
326
SQL> select count(*) from v$logmnr_contents where seg_name like '%tt%';
COUNT(*)
----------
0
SQL> select count(*) from v$logmnr_contents where seg_name like '%TT%';
COUNT(*)
----------
0
SQL> select count(*) from v$logmnr_contents where sql_redo like '%delete%'
2 ;
COUNT(*)
----------
2
--從redo_sql中看出并沒有出現(xiàn)對象tt的名字，而是使用了“"UNKNOWN"."OBJ# 12206"”
SQL> select sql_redo,seg_owner,seg_name from v$logmnr_contents where sql_redo li
ke '%delete%'
2 ;
SQL_REDO
--------------------------------------------------------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
delete from "UNKNOWN"."OBJ# 12206" where "COL 1" = HEXTORAW('c102') and "COL 2"
= HEXTORAW('61') and ROWID = 'AAAC+uAACAAAAMQAAA';
UNKNOWN
OBJ# 12206
delete from "UNKNOWN"."OBJ# 8781" where "COL 1" = HEXTORAW('c20216') and "COL 2"
= HEXTORAW('c105') and "COL 3" = HEXTORAW('80') and "COL 4" = HEXTORAW('c102')
SQL_REDO
--------------------------------------------------------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
and "COL 5" IS NULL and "COL 6" = HEXTORAW('436f6e63757272656e6379') and "COL 7"
IS NULL and "COL 8" = HEXTORAW('c22205') and "COL 9" = HEXTORAW('44617461626173
652054696d65205370656e742057616974696e6720282529') and "COL 10" = HEXTORAW('3537
2e3835363036') and "COL 11" = HEXTORAW('436f6e63757272656e6379') and "COL 12" IS
NULL and "COL 13" IS NULL and "COL 14" = HEXTORAW('786c091a0730380d4ab5c01c3c')
and "COL 15" = HEXTORAW('786c091a0730380d4ab5c01c3c') and "COL 16" IS NULL and
"COL 17" IS NULL and "COL 18" IS NULL and "COL 19" IS NULL and "COL 20" IS NULL
SQL_REDO
--------------------------------------------------------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
and "COL 21" = HEXTORAW('c106') and "COL 22" IS NULL and "COL 23" = HEXTORAW('22
74657374222e227473696422') and "COL 24" = HEXTORAW('787973') and "COL 25" = HEXT
ORAW('3139322e3136382e302e323532') and "COL 26" = HEXTORAW('74736964') and "COL
27" = HEXTORAW('c102') and "COL 28" IS NULL and "COL 29" IS NULL and "COL 30" =
HEXTORAW('4438313436363034414543302d344141372d413732452d394230393735413130453746
2d30') and "COL 31" IS NULL and "COL 32" = HEXTORAW('c13a563d3a02274c150b') and
ROWID = 'AAACJNAADAAAAplAAC';
SQL_REDO
--------------------------------------------------------------------------------
SEG_OWNER
--------------------------------
SEG_NAME
--------------------------------------------------------------------------------
UNKNOWN
OBJ# 8781
SQL> select object_id, data_object_id from dba_objects where object_name='TT' AN
D OWNER='TEST';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
12206 12206
--驗證下rba：
SQL> select rbasqn,rbablk,rbabyte from v$logmnr_contents where sql_redo like '%d
elete%';
RBASQN RBABLK RBABYTE
---------- ---------- ----------
29 295 16
29 558 400
SQL> select status,group# from v$log;
STATUS GROUP#
---------------- ----------
CURRENT 1
INACTIVE 2
INACTIVE 3
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG
SQL> alter system dump logfile 'E:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG
';
系統(tǒng)已更改。
--dump logfile trace的rba是:RBA: 0x00001d.00000127.0010
SQL> select to_number('00000127','xxxxxxxx') from dual;
TO_NUMBER('00000127','XXXXXXXX')
--------------------------------
295
SQL> select redo_length from v$logmnr_contents where sql_redo like '%delete%';
REDO_LENGTH
-----------
0
0
SQL>
dump logfile trace:
--=============================================
REDO RECORD - Thread:1 RBA: 0x00001d.00000127.0010 LEN: 0x0188 VLD: 0x01
SCN: 0x0000.00182edd SUBSCN: 1 09/26/2008 14:45:55
CHANGE #1 TYP:0 CLS:23 AFN:4 DBA:0x01000039 OBJ:4294967295 SCN:0x0000.00182ed2 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0025 sqn: 0x000000c9 flg: 0x0012 siz: 148 fbi: 0
uba: 0x010000d1.005e.0c pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:24 AFN:4 DBA:0x010000d1 OBJ:4294967295 SCN:0x0000.00182ed1 SEQ: 7 OP:5.1
ktudb redo: siz: 148 spc: 6928 flg: 0x0012 seq: 0x005e rec: 0x0c
xid: 0x0004.025.000000c9
ktubl redo: slt: 37 rci: 0 opc: 11.1 objn: 12206 objd: 12206 tsn: 5
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x010000d1.005e.05
prev ctl max cmt scn: 0x0000.00182b93 prev tx cmt scn: 0x0000.00182b98
txn start scn: 0xffff.ffffffff logon user: 31 prev brb: 16777278 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00800310 hdba: 0x0080030b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) size/delt: 8
fb: --H-FL-- lb: 0x0 cc: 2
null: --
col 0: [ 2] c1 02
col 1: [ 1] 61
CHANGE #3 TYP:2 CLS: 1 AFN:2 DBA:0x00800310 OBJ:12206 SCN:0x0000.00182caf SEQ: 1 OP:11.3
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x0004.025.000000c9 uba: 0x010000d1.005e.0c
Block cleanout record, scn: 0x0000.00182edd ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.00182caf
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00800310 hdba: 0x0080030b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0)
--===================================================
以上是“sql中如何使用dbms_logmnr”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
