在误删除了表空间后成功恢复数据文件后,在使用odu、dul进行数据抽取时需要元数据。
- 可以使用odu进行恢复delete行.但是这个不一定可以成功.
- 使用logminer分析归档进行操作
- 即使flashback查询底层表
使用logminer分析归档进行操作
1.创建环境环境准备
--删除表 SQL> @o t1 owner object_name object_type STATUS OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- ----------------- TRAVEL T1 TABLE VALID 23087 23087 20150214 13:50:31 20150214 13:50:31 SQL> @tab t1 OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS -------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ----------------- -------------------- -------- TRAVEL T1 TAB 1 DISABLED SQL> @DESC t1 Name NULL? TYPE ------------------------------- -------- ---------------------------- 1 COL1 VARCHAR2(200) 2 COL2 BLOB 3 COL3 CLOB 4 TIMESTAMP DATE SQL> SELECT * FROM v$Log 2 ; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------------ ----------------- ------------------ ----------------- 1 1 772 52428800 512 1 YES INACTIVE 8594796162 20150214 12:14:11 8594796240 20150214 12:14:17 2 1 773 52428800 512 1 NO CURRENT 8594796240 20150214 12:14:17 281474976710655 3 1 771 52428800 512 1 YES INACTIVE 8594796006 20150214 12:13:09 8594796162 20150214 12:14:11 SQL> ALTER system checkpoint; System altered. SQL> DROP TABLE t1 purge; TABLE dropped. SQL> SELECT * FROM v$Log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------------ ----------------- ------------------ ----------------- 1 1 772 52428800 512 1 YES INACTIVE 8594796162 20150214 12:14:11 8594796240 20150214 12:14:17 2 1 773 52428800 512 1 NO CURRENT 8594796240 20150214 12:14:17 281474976710655 3 1 771 52428800 512 1 YES INACTIVE 8594796006 20150214 12:13:09 8594796162 20150214 12:14:11 SQL> ALTER system switch logfile; System altered. SQL> !ls -l /other/archivelog/1_773* -rw-r----- 1 oracle oinstall 5836288 Feb 14 13:57 /other/archivelog/1_773_824815737.dbf SQL> SQL> --添加日志 SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/other/archivelog/1_773_824815737.dbf',Options=>dbms_logmnr.NEW) PL/SQL PROCEDURE successfully completed. --分析 SQL> EXECUTE dbms_logmnr.start_logmnr(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); PL/SQL PROCEDURE successfully completed. |
2.创建表
-- SQL> CREATE TABLE ODU_LOGMNR TABLESPACE USERS AS SELECT * FROM V$LOGMNR_CONTENTS; TABLE created. --创建子表 SQL> SQL> SQL> CREATE TABLE LOGMNR_OBJ TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME='OBJ$'; TABLE created. SQL> SQL> CREATE TABLE LOGMNR_COL TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME='COL$'; TABLE created. SQL> SQL> CREATE TABLE LOGMNR_TAB TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME IN ('TAB$','IND$','TABPART$','INDPART$','TABSUBPART$','INDSUBPART$'); TABLE created. SQL> SQL> CREATE TABLE LOGMNR_IND TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME IN ('IND$'); TABLE created. SQL> SQL> CREATE TABLE LOGMNR_LOB TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME IN ('LOB$'); TABLE created. SQL> SQL> CREATE TABLE LOGMNR_LOBFRAG TABLESPACE USERS AS SELECT * FROM ODU_LOGMNR WHERE SEG_NAME IN ('LOBFRAG$'); TABLE created. |
3.分析obj$ 表
SQL> SELECT * 2 FROM 3 (SELECT 4 REGEXP_SUBSTR(OBJ,'[0-9]+') OBJ, 5 REGEXP_SUBSTR(DATAOBJ,'[0-9]+') DATAOBJ, 6 REGEXP_SUBSTR(OWNER,'[0-9]+') OWNER , 7 SUBSTR(NAME,INSTR(NAME,CHR(39),1,1)+1,INSTR(NAME,CHR(39),1,2)-INSTR(NAME,CHR(39),1,1)-1) NAME , 8 CASE 9 WHEN SUBNAME LIKE 'SUBNAME" IS NULL%' THEN NULL 10 ELSE SUBSTR(SUBNAME,INSTR(SUBNAME,CHR(39),1,1)+1,INSTR(SUBNAME,CHR(39),1,2)-INSTR(SUBNAME,CHR(39),1,1)-1) 11 END SUBNAME , 12 REGEXP_SUBSTR(TYPE,'[0-9]+') TYPE 13 FROM 14 ( SELECT 15 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1),20) OBJ , 16 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'DATAOBJ#',1),20) DATAOBJ , 17 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OWNER#',1),20) OWNER , 18 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'NAME',1),45) NAME , 19 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'SUBNAME',1),40) SUBNAME , 20 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TYPE#',1)-1,14) TYPE 21 FROM LOGMNR_OBJ 22 WHERE OPERATION='DELETE' 23 --AND SQL_REDO LIKE '%"OWNER#" = %112%' 24 --AND SQL_REDO LIKE '%"TYPE#" = 2%' 25 -- AND ROWNUM <2 26 ) 27 ) 28 WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' 29 ORDER BY NAME 30 / OBJ DATAOBJ OWNER NAME SUBNAME TYPE -------------------- -------------------- ---------- -------------------- ---------------------------------------- -------------------- 23087 23087 34 T1 2 |
4.分析 COL$ 表
SQL 语句 -- DELETE -- FROM "SYS"."COL$" -- WHERE "OBJ#" = '22492' -- -- AND "COL#" = '1' -- -- AND "SEGCOL#" = '1' -- AND "SEGCOLLENGTH" = '30' -- AND "OFFSET" = '0' -- AND "NAME" = 'EBPS_LOGGER_ID' -- AND "TYPE#" = '1' -- AND "LENGTH" = '30' -- AND "FIXEDSTORAGE" = '0' -- AND "PRECISION#" IS NULL -- AND "SCALE" IS NULL -- AND "NULL$" = '0' -- AND "DEFLENGTH" IS NULL -- AND "DEFAULT$" IS NULL -- AND "INTCOL#" = '1' -- AND "PROPERTY" = '0' -- AND "CHARSETID" = '852' -- AND "CHARSETFORM" = '1' -- AND "SPARE1" = '0' -- AND "SPARE2" = '0' -- AND "SPARE3" = '30' -- AND "SPARE4" IS NULL -- AND "SPARE5" IS NULL -- AND "SPARE6" IS NULL -- AND ROWID = 'AAAAACAABAAAIEVAA4'; COL OBJ# FOR A20 COL COL# FOR A20 COL SEGCOL# FOR A20 COL NAME FOR A20 COL TYPE# FOR A20 COL LENGTH FOR A20 COL PRECISION# FOR A20 COL SCALE FOR A20 COL INTCOL# FOR A20 COL PROPERTY FOR A20 COL CHARSETID FOR A20 COL CHARSETFORM FOR A20 SELECT * FROM (SELECT REGEXP_SUBSTR(OBJ#,'[0-9]+') OBJ#, REGEXP_SUBSTR(COL#,'[0-9]+') COL#, REGEXP_SUBSTR(SEGCOL#,'[0-9]+') SEGCOL# , SUBSTR(NAME,INSTR(NAME,CHR(39),1,1)+1,INSTR(NAME,CHR(39),1,2)-INSTR(NAME,CHR(39),1,1)-1) NAME , REGEXP_SUBSTR(TYPE#,'[0-9]+') TYPE#, REGEXP_SUBSTR(LENGTH,'[0-9]+') LENGTH, REGEXP_SUBSTR(PRECISION#,'[0-9]+') PRECISION#, REGEXP_SUBSTR(SCALE,'[0-9]+') SCALE, REGEXP_SUBSTR(NULL$,'[0-9]+') NULL$, REGEXP_SUBSTR(INTCOL#,'[0-9]+') INTCOL, REGEXP_SUBSTR(PROPERTY,'[0-9]+') PROPERTY, REGEXP_SUBSTR(CHARSETID,'[0-9]+') CHARSETID, REGEXP_SUBSTR(CHARSETFORM,'[0-9]+') CHARSETFORM FROM ( SELECT SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+8,10) OBJ# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'COL#',1)+8,5) COL# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'SEGCOL#',1)+11,5) SEGCOL# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'NAME',1)+8,32) NAME , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TYPE#',1)+9,5) TYPE# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'LENGTH',1)+10,5) LENGTH, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'PRECISION#',1),5) PRECISION#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'SCALE',1)+9,5) SCALE, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'NULL$',1)+9,5) NULL$, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'INTCOL#',1)+11,5) INTCOL#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'PROPERTY',1)+11,5) PROPERTY, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'CHARSETID',1)+12,5) CHARSETID, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'CHARSETFORM',1)+14,5) CHARSETFORM FROM LOGMNR_COL WHERE OPERATION='DELETE' --AND SQL_REDO LIKE '%"OBJ#" = %22492%' --AND SQL_REDO LIKE '%"OWNER#" = %112%' --AND SQL_REDO LIKE '%"TYPE#" = 2%' -- AND ROWNUM <4 ) ) --WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' ORDER BY 2 / OBJ# COL# SEGCOL# NAME TYPE# LENGTH PRECISION# SCALE NULL$ INTCOL PROPERTY CHARSETID CHARSETFORM -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ---------- -------------------- -------------------- -------------------- -------------------- 23087 1 1 COL1 1 200 0 1 0 852 1 23087 2 2 COL2 113 4000 0 2 128 0 0 23087 3 3 COL3 112 4000 0 3 128 852 1 23087 4 4 TIMESTAMP 12 7 0 4 0 0 0 |
5.分析 TAB$ 表
-- DELETE -- FROM "SYS"."TAB$" -- WHERE "OBJ#" = '22492' -- AND "DATAOBJ#" = '22492' -- AND "TS#" = '4' -- AND "FILE#" = '4' -- AND "BLOCK#" = '330' -- AND "BOBJ#" IS NULL -- AND "TAB#" IS NULL -- AND "COLS" = '16' -- AND "CLUCOLS" IS NULL -- AND "PCTFREE$" = '10' -- AND "PCTUSED$" = '40' -- AND "INITRANS" = '1' -- AND "MAXTRANS" = '255' -- AND "FLAGS" = '1073742353' -- AND "AUDIT$" = '--------------------------------------' -- AND "ROWCNT" = '0' -- AND "BLKCNT" = '0' -- AND "EMPCNT" = '0' -- AND "AVGSPC" = '0' -- AND "CHNCNT" = '0' -- AND "AVGRLN" = '0' -- AND "AVGSPC_FLB" = '0' -- AND "FLBCNT" = '0' -- AND "ANALYZETIME" = TO_DATE('20141108 12:37:48', 'YYYYMMDD HH24:MI:SS') -- AND "SAMPLESIZE" = '0' -- AND "DEGREE" IS NULL -- AND "INSTANCES" IS NULL -- AND "INTCOLS" = '16' -- AND "KERNELCOLS" = '16' -- AND "PROPERTY" = '536870912' -- AND "TRIGFLAG" = '0' -- AND "SPARE1" = '736' -- AND "SPARE2" IS NULL -- AND "SPARE3" IS NULL -- AND "SPARE4" IS NULL -- AND "SPARE5" IS NULL -- AND "SPARE6" = TO_DATE('20141106 20:33:47', 'YYYYMMDD HH24:MI:SS') -- AND ROWID = 'AAAAACAABAAAIEVAAB'; SELECT * FROM (SELECT REGEXP_SUBSTR(OBJ#,'[0-9]+') OBJ#, REGEXP_SUBSTR(DATAOBJ#,'[0-9]+') DATAOBJ#, REGEXP_SUBSTR(TS#,'[0-9]+') TS# , REGEXP_SUBSTR(FILE#,'[0-9]+') FILE#, REGEXP_SUBSTR(BLOCK#,'[0-9]+') BLOCK#, REGEXP_SUBSTR(TAB#,'[0-9]+') TAB# FROM ( SELECT SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+8,10) OBJ# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'DATAOBJ#',1)+12,10) DATAOBJ# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TS#',1)+7,5) TS# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'FILE#',1)+8,5) FILE# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BLOCK#',1)+9,8) BLOCK# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TAB#',1)+6,5) TAB# FROM LOGMNR_TAB WHERE OPERATION='DELETE' --AND SQL_REDO LIKE '%"OBJ#" = %22492%' -- AND ROWNUM <4 ) ) --WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' ORDER BY 2 / SQL> SELECT * 2 FROM 3 (SELECT 4 REGEXP_SUBSTR(OBJ#,'[0-9]+') OBJ#, 5 REGEXP_SUBSTR(DATAOBJ#,'[0-9]+') DATAOBJ#, 6 REGEXP_SUBSTR(TS#,'[0-9]+') TS# , 7 REGEXP_SUBSTR(FILE#,'[0-9]+') FILE#, 8 REGEXP_SUBSTR(BLOCK#,'[0-9]+') BLOCK#, 9 REGEXP_SUBSTR(TAB#,'[0-9]+') TAB# 10 FROM 11 ( SELECT 12 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+8,10) OBJ# , 13 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'DATAOBJ#',1)+12,10) DATAOBJ# , 14 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TS#',1)+7,5) TS# , 15 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'FILE#',1)+8,5) FILE# , 16 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BLOCK#',1)+9,8) BLOCK# , 17 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TAB#',1)+6,5) TAB# 18 FROM LOGMNR_TAB 19 WHERE OPERATION='DELETE' 20 --AND SQL_REDO LIKE '%"OBJ#" = %22492%' 21 -- AND ROWNUM <4 22 ) 23 ) 24 --WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' 25 ORDER BY 2 26 / OBJ# DATAOBJ# TS# FILE# BLOCK# TAB# -------------------- -------------------- ---------- ---------- ---------------- ---------- 23087 23087 4 4 234 23089 23089 4 4 210 23091 23091 4 4 226 SQL> |
6.分析 IND$ 表
-- DELETE -- FROM "SYS"."IND$" -- WHERE "OBJ#" = '22438' -- AND "DATAOBJ#" = '22438' -- AND "TS#" = '4' -- AND "FILE#" = '4' -- AND "BLOCK#" = '194' -- AND "BO#" = '18842' -- AND "INDMETHOD#" = '0' -- AND "COLS" = '1' -- AND "PCTFREE$" = '10' -- AND "INITRANS" = '2' -- AND "MAXTRANS" = '255' -- AND "PCTTHRES$" IS NULL -- AND "TYPE#" = '1' -- AND "FLAGS" = '2' -- AND "PROPERTY" = '0' -- AND "BLEVEL" = '1' -- AND "LEAFCNT" = '60' -- AND "DISTKEY" = '10999' -- AND "LBLKKEY" = '1' -- AND "DBLKKEY" = '1' -- AND "CLUFAC" = '5995' -- AND "ANALYZETIME" = TO_DATE('20141016 14:18:52', 'YYYYMMDD HH24:MI:SS') -- AND "SAMPLESIZE" = '14403' -- AND "ROWCNT" = '14403' -- AND "INTCOLS" = '1' -- AND "DEGREE" IS NULL -- AND "INSTANCES" IS NULL -- AND "TRUNCCNT" IS NULL -- AND "SPARE1" = '1' -- AND "SPARE2" IS NULL -- AND "SPARE3" IS NULL -- AND "SPARE4" IS NULL -- AND "SPARE5" IS NULL -- AND "SPARE6" = TO_DATE('20141016 06:18:51', 'YYYYMMDD HH24:MI:SS') -- AND ROWID = 'AAAAACAABAAAIEVAAB'; COL BO# FOR A20 COL OBJ# FOR A20 SELECT * FROM (SELECT REGEXP_SUBSTR(BO#,'[0-9]+') BO#, REGEXP_SUBSTR(OBJ#,'[0-9]+') OBJ# FROM ( SELECT SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BO#',1)+8,10) BO# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+7,10) OBJ# FROM LOGMNR_IND WHERE OPERATION='DELETE' --AND SQL_REDO LIKE '%"OBJ#" = %22492%' --AND SQL_REDO LIKE '%"OWNER#" = %112%' --AND SQL_REDO LIKE '%"TYPE#" = 2%' -- AND ROWNUM <4 ) ) --WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' ORDER BY 2 / SQL> SELECT * 2 FROM 3 (SELECT 4 REGEXP_SUBSTR(BO#,'[0-9]+') BO#, 5 REGEXP_SUBSTR(OBJ#,'[0-9]+') OBJ# 6 FROM 7 ( SELECT 8 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BO#',1)+8,10) BO# , 9 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+7,10) OBJ# 10 FROM LOGMNR_IND 11 WHERE OPERATION='DELETE' 12 --AND SQL_REDO LIKE '%"OBJ#" = %22492%' 13 --AND SQL_REDO LIKE '%"OWNER#" = %112%' 14 --AND SQL_REDO LIKE '%"TYPE#" = 2%' 15 -- AND ROWNUM <4 16 ) 17 ) 18 --WHERE TYPE IN ('2','19',34) AND NAME NOT LIKE 'BIN$+%' 19 ORDER BY 2 20 / BO# OBJ# -------------------- -------------------- 23087 23089 23087 23091 |
7.分析 LOB$ 表
SELECT obj# || ',' || col# || ',' || intcol# || ',' || lobj# || ',' || part# || ',' || ind# || ',' || ts# || ',' || file# || ',' || block# || ',' || chunk text FROM sys.lob$; DELETE FROM "SYS"."LOB$" WHERE "OBJ#" = '23087' AND "COL#" = '2' AND "INTCOL#" = '2' AND "LOBJ#" = '23088' AND "PART#" = '0' AND "IND#" = '23089' AND "TS#" = '4' AND "FILE#" = '4' AND "BLOCK#" = '202' AND "CHUNK" = '1' AND "PCTVERSION$" = '10' AND "FLAGS" = '97' AND "PROPERTY" = '2' AND "RETENTION" = '1800' AND "FREEPOOLS" = '0' AND "SPARE1" IS NULL AND "SPARE2" IS NULL AND "SPARE3" IS NULL AND ROWID = 'AAAAACAABAAAIFOAAA'; COL OBJ# FOR A20 COL COL# FOR A20 COL INTCOL# FOR A20 COL LOBJ# FOR A20 COL PART# FOR A20 COL IND# FOR A20 COL TS# FOR A20 COL FILE# FOR A20 COL BLOCK# FOR A20 COL CHUNK FOR A20 SELECT * FROM (SELECT REGEXP_SUBSTR(OBJ#, '[0-9]+') OBJ#, REGEXP_SUBSTR(COL#, '[0-9]+') COL#, REGEXP_SUBSTR(INTCOL#, '[0-9]+') INTCOL#, REGEXP_SUBSTR(LOBJ#, '[0-9]+') LOBJ#, REGEXP_SUBSTR(PART#, '[0-9]+') PART#, REGEXP_SUBSTR(IND#, '[0-9]+') IND#, REGEXP_SUBSTR(TS#, '[0-9]+') TS#, REGEXP_SUBSTR(FILE#, '[0-9]+') FILE#, REGEXP_SUBSTR(BLOCK#, '[0-9]+') BLOCK#, REGEXP_SUBSTR(CHUNK, '[0-9]+') CHUNK FROM ( SELECT SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+8,10) OBJ# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'COL#',1)+8,5) COL# , SUBSTR(SQL_REDO,INSTR(SQL_REDO,'INTCOL#',1)+11,5) INTCOL#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'LOBJ#',1)+9,10) LOBJ#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'PART#',1)+9,10) PART#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'IND#',1)+8,10) IND#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TS#',1)+7,5) TS#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'FILE#',1)+9,5) FILE#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BLOCK#',1)+10,10) BLOCK#, SUBSTR(SQL_REDO,INSTR(SQL_REDO,'CHUNK',1)+9,10) CHUNK FROM LOGMNR_LOB WHERE OPERATION='DELETE' --AND SQL_REDO LIKE '%"OBJ#" = %22492%' -- AND ROWNUM <4 ) ) ORDER BY 1,2 / SQL> SQL> SQL> SELECT * 2 FROM 3 (SELECT 4 REGEXP_SUBSTR(OBJ#, '[0-9]+') OBJ#, 5 REGEXP_SUBSTR(COL#, '[0-9]+') COL#, 6 REGEXP_SUBSTR(INTCOL#, '[0-9]+') INTCOL#, 7 REGEXP_SUBSTR(LOBJ#, '[0-9]+') LOBJ#, 8 REGEXP_SUBSTR(PART#, '[0-9]+') PART#, 9 REGEXP_SUBSTR(IND#, '[0-9]+') IND#, 10 REGEXP_SUBSTR(TS#, '[0-9]+') TS#, 11 REGEXP_SUBSTR(FILE#, '[0-9]+') FILE#, 12 REGEXP_SUBSTR(BLOCK#, '[0-9]+') BLOCK#, 13 REGEXP_SUBSTR(CHUNK, '[0-9]+') CHUNK 14 FROM 15 ( SELECT 16 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'OBJ#',1)+8,10) OBJ# , 17 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'COL#',1)+8,5) COL# , 18 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'INTCOL#',1)+11,5) INTCOL#, 19 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'LOBJ#',1)+9,10) LOBJ#, 20 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'PART#',1)+9,10) PART#, 21 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'IND#',1)+8,10) IND#, 22 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'TS#',1)+7,5) TS#, 23 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'FILE#',1)+9,5) FILE#, 24 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'BLOCK#',1)+10,10) BLOCK#, 25 SUBSTR(SQL_REDO,INSTR(SQL_REDO,'CHUNK',1)+9,10) CHUNK 26 FROM LOGMNR_LOB 27 WHERE OPERATION='DELETE' 28 --AND SQL_REDO LIKE '%"OBJ#" = %22492%' 29 -- AND ROWNUM <4 30 ) 31 ) 32 ORDER BY 1,2 33 / OBJ# COL# INTCOL# LOBJ# PART# IND# TS# FILE# BLOCK# CHUNK -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 23087 2 2 23088 0 23089 4 4 202 1 23087 3 3 23090 0 23091 4 4 218 1 |
分析 LOBFRAG$ 表 –略
