SQL> conn chennan/xxxx
已连接。
SQL>
SQL> create table test(id number primary key,name varchar2(20));表已创建。
SQL> ed
已写入文件 afiedt.buf1 begin
2 for x in 1 .. 10 loop
3 insert into test values(x,dbms_random.string('u',20));
4 end loop;
5 commit;
6* end;
SQL> /PL/SQL 过程已成功完成。
SQL> select * from test;
ID NAME
---------- --------------------
1 FGIUJVOACAFAWLLLYJZP
2 GWANRKJKDHPPUEKCBGBO
3 GHLAMYSQDVGZIDGWQSFN
4 DEPWFPCXRMAERLPTUSEU
5 FQKMLWLLXPPEKRLLXOXE
6 LSVUGVSDWLRWIPZOBBHC
7 ERCCDLQOZNYGKAPMLDPH
8 IDHFFOTMSTOCFAHPEMIF
9 JRJHLGITTOWJEPHJGVOW
10 FEQFNFVOMXSUUZOTJQZY已选择10行。
SQL> select * from v$log; -- 检查当前日志是哪组
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 9481 2097152 1 NO INACTIVE
2 1 9482 2097152 1 NO CURRENT
3 1 0 2097152 1 YES UNUSED已选择3行。
SQL> create or replace package mypack
2 is
3 procedure myproc;
4 end;
5 /程序包已创建。
SQL> create or replace package body mypack
2 is
3 procedure myproc
4 is
5 begin
6 delete from test;
7 for x in 1 .. 5 loop
8 insert into test values(x,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
9 end loop;
10 commit;
11 end;
12 end;
13 /程序包主体已创建。
SQL> select name,type from user_source group by name,type;
NAME TYPE
------------------------------ ------------
P PROCEDURE
NOTE PACKAGE
NOTE PACKAGE BODY
MYPACK PACKAGE
MYPACK PACKAGE BODY
LOGON_TRG TRIGGER
PRINT_TEMP PROCEDURE已选择7行。
删除package：
SQL> drop package mypack;
程序包已丢弃。
SQL> select name,type from user_source group by name,type;
NAME TYPE
------------------------------ ------------
P PROCEDURE
NOTE PACKAGE
NOTE PACKAGE BODY
LOGON_TRG TRIGGER
PRINT_TEMP PROCEDURE已选择5行。
SQL> select * from v$log; -- 再次检查当前日志是哪组
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
1 1 9481 2097152 1 NO INACTIVE
2 1 9482 2097152 1 NO CURRENT
3 1 0 2097152 1 YES UNUSED已选择3行。
SQL> alter system switch logfile;
系统已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
1 1 9481 2097152 1 NO INACTIVE
2 1 9482 2097152 1 NO ACTIVE
3 1 9483 2097152 1 NO CURRENT已选择3行。
SQL> alter system checkpoint;
系统已更改。
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------
1 1 9481 2097152 1 NO INACTIVE
2 1 9482 2097152 1 NO INACTIVE
3 1 9483 2097152 1 NO CURRENT已选择3行。
SQL>
此时拷贝日志组2的日志文件，更名为1REDO02.LOG
进行日志挖掘：
SQL> conn / as sysdba
已连接。
SQL>
SQL> exec dbms_logmnr.add_logfile('E:/oracle/oradata/cwgl/1REDO02.LOG',dbms_logmnr.NEW);PL/SQL 过程已成功完成。
SQL> exec SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL 过程已成功完成。
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss.sssss';
会话已更改。
SQL> select timestamp,sql_redo,sql_undo from v$logmnr_contents order by timestamp ;
TIMESTAMP SQL_REDO SQL_UNDO
------------------------- ---------------------------------------- --------------------------------
2008-07-23 10:39:36.38376 set transaction read write;
2008-07-23 10:39:36.38376 Unsupported Unsupported
2008-07-23 10:39:36.38376 Unsupported Unsupported
2008-07-23 10:39:36.38376 commit;
2008-07-23 10:39:36.38376 set transaction read write;
2008-07-23 10:39:36.38376 Unsupported Unsupported
......
2008-07-23 10:46:19.38779 drop package mypack;
2008-07-23 10:46:19.38779 delete from "SYS"."PROCEDURE$" where "OB insert into "SYS"."PROCEDURE$"("OBJ#","A
J#" = '46198' and "AUDIT$" = '---------- UDIT$","STORAGESIZE","OPTIONS") values (
----------------------' and "STORAGESIZE '46198','-------------------------------
" IS NULL and "OPTIONS" = '1' and ROWID -',NULL,'1');
= 'AAAABEAABAAAJ9DAB4';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '1' and "SOURCE" ","SOURCE") values ('46198','1','package
= 'package body mypack body mypack
' and ROWID = 'AAAABHAABAAAJeHAAc'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '2' and "SOURCE" ","SOURCE") values ('46198','2','is
= 'is ');
' and ROWID = 'AAAABHAABAAAJeHAAd';2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '3' and "SOURCE" ","SOURCE") values ('46198','3','procedu
= 'procedure myproc re myproc
' and ROWID = 'AAAABHAABAAAJeHAAe'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '4' and "SOURCE" ","SOURCE") values ('46198','4','is
= 'is ');
' and ROWID = 'AAAABHAABAAAJeHAAf';2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '5' and "SOURCE" ","SOURCE") values ('46198','5','begin
= 'begin ');
' and ROWID = 'AAAABHAABAAAJeHAAg';2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '6' and "SOURCE" ","SOURCE") values ('46198','6','delete
= 'delete from test; from test;
' and ROWID = 'AAAABHAABAAAJeHAAh'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '7' and "SOURCE" ","SOURCE") values ('46198','7','for x i
= 'for x in 1 .. 5 loop n 1 .. 5 loop
' and ROWID = 'AAAABHAABAAAJeHAAi'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '8' and "SOURCE" ","SOURCE") values ('46198','8','insert
= 'insert into test values(x,to_char(sy into test values(x,to_char(sysdate,''yyy
sdate,''yyyy-mm-dd hh24:mi:ss'')); y-mm-dd hh24:mi:ss''));
' and ROWID = 'AAAABHAABAAAJeHAAj'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '9' and "SOURCE" ","SOURCE") values ('46198','9','end loo
= 'end loop; p;
' and ROWID = 'AAAABHAABAAAJeHAAk'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '10' and "SOURCE ","SOURCE") values ('46198','10','commit
" = 'commit; ;
' and ROWID = 'AAAABHAABAAAJeHAAl'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '11' and "SOURCE ","SOURCE") values ('46198','11','end;
" = 'end; ');
' and ROWID = 'AAAABHAABAAAJeHAAm';2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46198' and "LINE" = '12' and "SOURCE ","SOURCE") values ('46198','12','end;')
" = 'end;' and ROWID = 'AAAABHAABAAAJeHA ;
An';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB1$" where "OBJ# insert into "SYS"."IDL_UB1$"("OBJ#","PAR
" = '46198' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '1' and values ('46198','2','153092096','1','473
"LENGTH" = '473' and "PIECE" = HEXTORAW( ',HEXTORAW('00203c00000001e40000b80071a5
'00203c00000001e40000b80071a5007b0000742 007b000074240404740404200000001384000408
4040474040420000000138400040813840c040c1 13840c040c1e0408040c10450000000e10400000
e0408040c10450000000e104000000071a5047b0 0071a5047b0000744c0410760410000003000408
000744c041076041000000300040810007404102 1000740410200000001e0408040c10190000000f
00000001e0408040c10190000000f10140000005 10140000005104080810d1ffffff686619000025
104080810d1ffffff68661900002504000600060 0400060006000000210001000014039202900100
0000021000100001403920290010000050002000 00050002000000000000000000050000000000d8
000000000000000050000000000d80002020200a 0002020200ac00ac000000000000000000000000
c00ac00000000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000100000000000000000000
0000000010000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 000000000001000000ffffffff00000000000000
1000000ffffffff0000000000000001140000001 0114000000100000000100000006003e05000000
00000000100000006003e05000000004d5950524 004d5950524f4300000300000003000000000000
f430000030000000300000000000000000000000 000000000000000000010000000500000002001e
0000000010000000500000002001e00000004000 000000040000000d0004144100081c41000c280d
0000d0004144100081c41000c280d00103400000 0010340000000000000c000000060000001d0000
00000000c000000060000001d000000070000003 0007000000370000000800000053000000070000
7000000080000005300000007000000710000000 00710000000a00000044454c4554452046524f4d
a00000044454c4554452046524f4d20544553542 205445535422494e5345525420494e544f205445
2494e5345525420494e544f20544553542056414 53542056414c554553283a4231202c544f5f4348
c554553283a4231202c544f5f434841522853595 415228535953444154452c2727797979792d6d6d
3444154452c2727797979792d6d6d2d646420686 2d646420686832343a6d693a73732727292922')
832343a6d693a73732727292922') and ROWID );
= 'AAAABIAABAAAKB/AAd';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB1$" where "OBJ# insert into "SYS"."IDL_UB1$"("OBJ#","PAR
" = '46198' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '2' and values ('46198','2','153092096','2','23'
"LENGTH" = '23' and "PIECE" = HEXTORAW(' ,HEXTORAW('01000000000000b0000400010c000
01000000000000b0000400010c0000000000b400 0000000b400040000'));
040000') and ROWID = 'AAAABIAABAAAKB/AAU
';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_SB4$" where "OBJ# insert into "SYS"."IDL_SB4$"("OBJ#","PAR
" = '46198' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '0' and values ('46198','2','153092096','0','18'
"LENGTH" = '18' and "PIECE" = Unsupporte ,Unsupported Type);
d Type and ROWID = 'AAAABLAABAAAH9RAAe';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SETTINGS$" where "OBJ insert into "SYS"."SETTINGS$"("OBJ#","PA
#" = '46198' and "PARAM" = 'plsql_compil RAM","VALUE") values ('46198','plsql_com
er_flags' and "VALUE" = 'INTERPRETED,NON piler_flags','INTERPRETED,NON_DEBUG');
_DEBUG' and ROWID = 'AAAABOAABAAAIXUAAv'
;2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SETTINGS$" where "OBJ insert into "SYS"."SETTINGS$"("OBJ#","PA
#" = '46198' and "PARAM" = 'nls_length_s RAM","VALUE") values ('46198','nls_lengt
emantics' and "VALUE" = 'BYTE' and ROWID h_semantics','BYTE');
= 'AAAABOAABAAAIXUAAy';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."DEPENDENCY$" where "D insert into "SYS"."DEPENDENCY$"("D_OBJ#"
_OBJ#" = '46198' and "D_TIMESTAMP" = TO_ ,"D_TIMESTAMP","ORDER#","P_OBJ#","P_TIME
DATE('2008-07-23 10:44:51.38691', 'yyyy- STAMP","D_OWNER#","PROPERTY") values ('4
mm-dd hh24:mi:ss.sssss') and "ORDER#" = 6198',TO_DATE('2008-07-23 10:44:51.38691
'0' and "P_OBJ#" = '46194' and "P_TIMEST ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'0','4
AMP" = TO_DATE('2008-07-23 10:44:01.3864 6194',TO_DATE('2008-07-23 10:44:01.38641
1', 'yyyy-mm-dd hh24:mi:ss.sssss') and " ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'61','
D_OWNER#" = '61' and "PROPERTY" = '1' an 1');
d ROWID = 'AAAABgAABAAAKhHAAu';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."DEPENDENCY$" where "D insert into "SYS"."DEPENDENCY$"("D_OBJ#"
_OBJ#" = '46198' and "D_TIMESTAMP" = TO_ ,"D_TIMESTAMP","ORDER#","P_OBJ#","P_TIME
DATE('2008-07-23 10:44:51.38691', 'yyyy- STAMP","D_OWNER#","PROPERTY") values ('4
mm-dd hh24:mi:ss.sssss') and "ORDER#" = 6198',TO_DATE('2008-07-23 10:44:51.38691
'1' and "P_OBJ#" = '46196' and "P_TIMEST ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'1','4
AMP" = TO_DATE('2008-07-23 10:41:30.3849 6196',TO_DATE('2008-07-23 10:41:30.38490
0', 'yyyy-mm-dd hh24:mi:ss.sssss') and " ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'61','
D_OWNER#" = '61' and "PROPERTY" = '1' an 1');
d ROWID = 'AAAABgAABAAAKhHAAv';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."DEPENDENCY$" where "D insert into "SYS"."DEPENDENCY$"("D_OBJ#"
_OBJ#" = '46198' and "D_TIMESTAMP" = TO_ ,"D_TIMESTAMP","ORDER#","P_OBJ#","P_TIME
DATE('2008-07-23 10:44:51.38691', 'yyyy- STAMP","D_OWNER#","PROPERTY") values ('4
mm-dd hh24:mi:ss.sssss') and "ORDER#" = 6198',TO_DATE('2008-07-23 10:44:51.38691
'2' and "P_OBJ#" = '647' and "P_TIMESTAM ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'2','6
P" = TO_DATE('2001-11-19 00:00:00.00000' 47',TO_DATE('2001-11-19 00:00:00.00000',
, 'yyyy-mm-dd hh24:mi:ss.sssss') and "D_ 'yyyy-mm-dd hh24:mi:ss.sssss'),'61','1'
OWNER#" = '61' and "PROPERTY" = '1' and );
ROWID = 'AAAABgAABAAAKhHAAw';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."DEPENDENCY$" where "D insert into "SYS"."DEPENDENCY$"("D_OBJ#"
_OBJ#" = '46198' and "D_TIMESTAMP" = TO_ ,"D_TIMESTAMP","ORDER#","P_OBJ#","P_TIME
DATE('2008-07-23 10:44:51.38691', 'yyyy- STAMP","D_OWNER#","PROPERTY") values ('4
mm-dd hh24:mi:ss.sssss') and "ORDER#" = 6198',TO_DATE('2008-07-23 10:44:51.38691
'3' and "P_OBJ#" = '649' and "P_TIMESTAM ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'3','6
P" = TO_DATE('2002-05-12 16:23:55.59035' 49',TO_DATE('2002-05-12 16:23:55.59035',
, 'yyyy-mm-dd hh24:mi:ss.sssss') and "D_ 'yyyy-mm-dd hh24:mi:ss.sssss'),'61','1'
OWNER#" = '61' and "PROPERTY" = '1' and );
ROWID = 'AAAABgAABAAAKhHAAx';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."ACCESS$" where "D_OBJ insert into "SYS"."ACCESS$"("D_OBJ#","OR
#" = '46198' and "ORDER#" = '0' and "COL DER#","COLUMNS","TYPES") values ('46198'
UMNS" IS NULL and "TYPES" = '12' and ROW ,'0',NULL,'12');
ID = 'AAAABhAABAAACjrABv';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."ACCESS$" where "D_OBJ insert into "SYS"."ACCESS$"("D_OBJ#","OR
#" = '46198' and "ORDER#" = '1' and "COL DER#","COLUMNS","TYPES") values ('46198'
UMNS" IS NULL and "TYPES" = '3' and ROWI ,'1',NULL,'3');
D = 'AAAABhAABAAACjrABw';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."ACCESS$" where "D_OBJ insert into "SYS"."ACCESS$"("D_OBJ#","OR
#" = '46198' and "ORDER#" = '1' and "COL DER#","COLUMNS","TYPES") values ('46198'
UMNS" = HEXTORAW('0600000000000000000000 ,'1',HEXTORAW('0600000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000000000000000000000000
0000000000000000000000000000000000000000 0000000000000000000000000000000000000000
000000000000000000000000000000') and "TY 000000000000000000000000000'),'6');
PES" = '6' and ROWID = 'AAAABhAABAAACjrA
By';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."ACCESS$" where "D_OBJ insert into "SYS"."ACCESS$"("D_OBJ#","OR
#" = '46198' and "ORDER#" = '2' and "COL DER#","COLUMNS","TYPES") values ('46198'
UMNS" IS NULL and "TYPES" = '12' and ROW ,'2',NULL,'12');
ID = 'AAAABhAABAAACjrABx';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."PROCEDURE$" where "OB insert into "SYS"."PROCEDURE$"("OBJ#","A
J#" = '46194' and "AUDIT$" = '---------- UDIT$","STORAGESIZE","OPTIONS") values (
----------------------' and "STORAGESIZE '46194','-------------------------------
" IS NULL and "OPTIONS" = '1' and ROWID -',NULL,'1');
= 'AAAABEAABAAAJ9DAB3';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46194' and "LINE" = '1' and "SOURCE" ","SOURCE") values ('46194','1','package
= 'package mypack mypack
' and ROWID = 'AAAABHAABAAAJeHAAR'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46194' and "LINE" = '2' and "SOURCE" ","SOURCE") values ('46194','2','is
= 'is ');
' and ROWID = 'AAAABHAABAAAJeHAAS';2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46194' and "LINE" = '3' and "SOURCE" ","SOURCE") values ('46194','3','procedu
= 'procedure myproc; re myproc;
' and ROWID = 'AAAABHAABAAAJeHAAT'; ');2008-07-23 10:46:19.38779 delete from "SYS"."SOURCE$" where "OBJ#" insert into "SYS"."SOURCE$"("OBJ#","LINE
= '46194' and "LINE" = '4' and "SOURCE" ","SOURCE") values ('46194','4','end;');
= 'end;' and ROWID = 'AAAABHAABAAAJeHAA
U';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB1$" where "OBJ# insert into "SYS"."IDL_UB1$"("OBJ#","PAR
" = '46194' and "PART" = '0' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '2' and values ('46194','0','153092096','2','3',
"LENGTH" = '3' and "PIECE" = HEXTORAW('0 HEXTORAW('000000'));
00000') and ROWID = 'AAAABIAABAAAKB/AAa'
;2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_CHAR$" where "OBJ insert into "SYS"."IDL_CHAR$"("OBJ#","PA
#" = '46194' and "PART" = '0' and "VERSI RT","VERSION","PIECE#","LENGTH","PIECE")
ON" = '153092096' and "PIECE#" = '3' and values ('46194','0','153092096','3','16
"LENGTH" = '16' and "PIECE" = '"MYPACK" ','"MYPACK"MYPROC""');
MYPROC""' and ROWID = 'AAAABJAABAAAIwFAA
U';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB2$" where "OBJ# insert into "SYS"."IDL_UB2$"("OBJ#","PAR
" = '46194' and "PART" = '0' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '4' and values ('46194','0','153092096','4','121
"LENGTH" = '121' and "PIECE" = Unsupport ',Unsupported Type);
ed Type and ROWID = 'AAAABKAABAAANO1AAG'
;2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB2$" where "OBJ# insert into "SYS"."IDL_UB2$"("OBJ#","PAR
" = '46194' and "PART" = '0' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '5' and values ('46194','0','153092096','5','15'
"LENGTH" = '15' and "PIECE" = Unsupporte ,Unsupported Type);
d Type and ROWID = 'AAAABKAABAAANO1AAH';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_SB4$" where "OBJ# insert into "SYS"."IDL_SB4$"("OBJ#","PAR
" = '46194' and "PART" = '0' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '0' and values ('46194','0','153092096','0','14'
"LENGTH" = '14' and "PIECE" = Unsupporte ,Unsupported Type);
d Type and ROWID = 'AAAABLAABAAAH9RAAi';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_SB4$" where "OBJ# insert into "SYS"."IDL_SB4$"("OBJ#","PAR
" = '46194' and "PART" = '0' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '1' and values ('46194','0','153092096','1','2',
"LENGTH" = '2' and "PIECE" = Unsupported Unsupported Type);
Type and ROWID = 'AAAABLAABAAAH9RAAc';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB1$" where "OBJ# insert into "SYS"."IDL_UB1$"("OBJ#","PAR
" = '46194' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '1' and values ('46194','2','153092096','1','101
"LENGTH" = '101' and "PIECE" = HEXTORAW( ',HEXTORAW('0060000000140392020c00000001
'0060000000140392020c0000000100010000000 0001000000000000000000000000000000000002
0000000000000000000000000000202000000000 02000000000000ffffffff000000000000000114
000ffffffff00000000000000011400000010000 000000100000000100000006000000000000004d
0000100000006000000000000004d5950524f430 5950524f43000003000000030000000000000000
0000300000003000000000000000000000000000 00000000000000'));
000') and ROWID = 'AAAABIAABAAAKB/AAb';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_UB1$" where "OBJ# insert into "SYS"."IDL_UB1$"("OBJ#","PAR
" = '46194' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '2' and values ('46194','2','153092096','2','1',
"LENGTH" = '1' and "PIECE" = HEXTORAW('0 HEXTORAW('00'));
0') and ROWID = 'AAAABIAABAAAKB/AAc';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."IDL_SB4$" where "OBJ# insert into "SYS"."IDL_SB4$"("OBJ#","PAR
" = '46194' and "PART" = '2' and "VERSIO T","VERSION","PIECE#","LENGTH","PIECE")
N" = '153092096' and "PIECE#" = '0' and values ('46194','2','153092096','0','18'
"LENGTH" = '18' and "PIECE" = Unsupporte ,Unsupported Type);
d Type and ROWID = 'AAAABLAABAAAH9RAAd';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."PROCEDUREINFO$" where insert into "SYS"."PROCEDUREINFO$"("OBJ#
"OBJ#" = '46194' and "PROCEDURE#" = '1' ","PROCEDURE#","OVERLOAD#","PROCEDURENAM
and "OVERLOAD#" = '0' and "PROCEDURENAM E","PROPERTIES") values ('46194','1','0'
E" = 'MYPROC' and "PROPERTIES" = '49152' ,'MYPROC','49152');
and ROWID = 'AAAABFAABAAANXpAAm';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."ARGUMENT$" where "OBJ insert into "SYS"."ARGUMENT$"("OBJ#","PR
#" = '46194' and "PROCEDURE$" = 'MYPROC' OCEDURE$","OVERLOAD#","PROCEDURE#","POSI
and "OVERLOAD#" = '0' and "PROCEDURE#" TION#","SEQUENCE#","LEVEL#","ARGUMENT","
= '1' and "POSITION#" = '1' and "SEQUENC TYPE#","CHARSETID","CHARSETFORM","DEFAUL
E#" = '0' and "LEVEL#" = '0' and "ARGUME T#","IN_OUT","PROPERTIES") values ('4619
NT" IS NULL and "TYPE#" = '0' and "CHARS 4','MYPROC','0','1','1','0','0',NULL,'0'
ETID" IS NULL and "CHARSETFORM" IS NULL ,NULL,NULL,NULL,NULL,'0');
and "DEFAULT#" IS NULL and "IN_OUT" IS N
ULL and "PROPERTIES" = '0' and ROWID = '
AAAABGAABAAAJKPAAQ';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SETTINGS$" where "OBJ insert into "SYS"."SETTINGS$"("OBJ#","PA
#" = '46194' and "PARAM" = 'plsql_compil RAM","VALUE") values ('46194','plsql_com
er_flags' and "VALUE" = 'INTERPRETED,NON piler_flags','INTERPRETED,NON_DEBUG');
_DEBUG' and ROWID = 'AAAABOAABAAAIXUAAw'
;2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."SETTINGS$" where "OBJ insert into "SYS"."SETTINGS$"("OBJ#","PA
#" = '46194' and "PARAM" = 'nls_length_s RAM","VALUE") values ('46194','nls_lengt
emantics' and "VALUE" = 'BYTE' and ROWID h_semantics','BYTE');
= 'AAAABOAABAAAIXUAAx';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 delete from "SYS"."OBJ$" where "OBJ#" = insert into "SYS"."OBJ$"("OBJ#","DATAOBJ
'46198' and "DATAOBJ#" IS NULL and "OWNE #","OWNER#","NAME","NAMESPACE","SUBNAME"
R#" = '61' and "NAME" = 'MYPACK' and "NA ,"TYPE#","CTIME","MTIME","STIME","STATUS
MESPACE" = '2' and "SUBNAME" IS NULL and ","REMOTEOWNER","LINKNAME","FLAGS","OID$
"TYPE#" = '11' and "CTIME" = TO_DATE('2 ","SPARE1","SPARE2") values ('46198',NUL
008-07-23 10:44:23.38663', 'yyyy-mm-dd h L,'61','MYPACK','2',NULL,'11',TO_DATE('2
h24:mi:ss.sssss') and "MTIME" = TO_DATE( 008-07-23 10:44:23.38663', 'yyyy-mm-dd h
'2008-07-23 10:44:51.38691', 'yyyy-mm-dd h24:mi:ss.sssss'),TO_DATE('2008-07-23 10
hh24:mi:ss.sssss') and "STIME" = TO_DAT :44:51.38691', 'yyyy-mm-dd hh24:mi:ss.ss
E('2008-07-23 10:44:51.38691', 'yyyy-mm- sss'),TO_DATE('2008-07-23 10:44:51.38691
dd hh24:mi:ss.sssss') and "STATUS" = '5' ', 'yyyy-mm-dd hh24:mi:ss.sssss'),'5',NU
and "REMOTEOWNER" IS NULL and "LINKNAME LL,NULL,'0',NULL,'6','65535');
" IS NULL and "FLAGS" = '0' and "OID$" I
S NULL and "SPARE1" = '6' and "SPARE2" =
'65535' and ROWID = 'AAAAASAABAAADB9ABB
';2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779
2008-07-23 10:46:19.38779 update "SYS"."OBJ$" set "OBJ#" = '46194' update "SYS"."OBJ$" set "OBJ#" = '46194'
, "DATAOBJ#" = NULL, "TYPE#" = '10', "CT , "DATAOBJ#" = NULL, "TYPE#" = '9', "CTI
IME" = TO_DATE('2008-07-23 10:44:01.3864 ME" = TO_DATE('2008-07-23 10:44:01.38641
1', 'yyyy-mm-dd hh24:mi:ss.sssss'), "MTI ', 'yyyy-mm-dd hh24:mi:ss.sssss'), "MTIM
ME" = TO_DATE('2008-07-23 10:46:19.38779 E" = TO_DATE('2008-07-23 10:44:01.38641'
', 'yyyy-mm-dd hh24:mi:ss.sssss'), "STIM , 'yyyy-mm-dd hh24:mi:ss.sssss'), "STIME
E" = TO_DATE('4712-12-31 23:59:59.86399' " = TO_DATE('2008-07-23 10:44:01.38641',
, 'yyyy-mm-dd hh24:mi:ss.sssss'), "STATU 'yyyy-mm-dd hh24:mi:ss.sssss'), "STATUS
S" = '1', "FLAGS" = '0', "OID$" = NULL, " = '1', "FLAGS" = '0', "OID$" = NULL, "
"SPARE1" = '6', "SPARE2" = '65535' where SPARE1" = '6', "SPARE2" = '65535' where
"OBJ#" = '46194' and "DATAOBJ#" IS NULL "OBJ#" = '46194' and "DATAOBJ#" IS NULL
and "TYPE#" = '9' and "CTIME" = TO_DATE and "TYPE#" = '10' and "CTIME" = TO_DATE
('2008-07-23 10:44:01.38641', 'yyyy-mm-d ('2008-07-23 10:44:01.38641', 'yyyy-mm-d
d hh24:mi:ss.sssss') and "MTIME" = TO_DA d hh24:mi:ss.sssss') and "MTIME" = TO_DA
TE('2008-07-23 10:44:01.38641', 'yyyy-mm TE('2008-07-23 10:46:19.38779', 'yyyy-mm
-dd hh24:mi:ss.sssss') and "STIME" = TO_ -dd hh24:mi:ss.sssss') and "STIME" = TO_
DATE('2008-07-23 10:44:01.38641', 'yyyy- DATE('4712-12-31 23:59:59.86399', 'yyyy-
mm-dd hh24:mi:ss.sssss') and "STATUS" = mm-dd hh24:mi:ss.sssss') and "STATUS" =
'1' and "FLAGS" = '0' and "OID$" IS NULL '1' and "FLAGS" = '0' and "OID$" IS NULL
and "SPARE1" = '6' and "SPARE2" = '6553 and "SPARE1" = '6' and "SPARE2" = '6553
5' and ROWID = 'AAAAASAABAAADB9AA8'; 5' and ROWID = 'AAAAASAABAAADB9AA8';2008-07-23 10:46:19.38779 commit;
2008-07-23 10:46:41.38801 set transaction read write;
2008-07-23 10:46:41.38801 Unsupported Unsupported
2008-07-23 10:46:41.38801 Unsupported Unsupported
......
把sql_undo这部分的sql语句拷贝出来，由sys用户执行在执行过程中，有几个语句因“Unsupported Type”报错，注释掉这些语句后，执行成功。再次查看user_source视图：
SQL> conn chennan/xxxx
已连接。
SQL> select name,type from user_source group by name,type;NAME TYPE
------------------------------ ------------
P PROCEDURE
NOTE PACKAGE
NOTE PACKAGE BODY
MYPACK PACKAGE
MYPACK PACKAGE BODY
LOGON_TRG TRIGGER
PRINT_TEMP PROCEDURE已选择7行。
SQL> select text from user_source where name='MYPACK' order by type,line;
TEXT
-----------------------------------------------------------------------------
package mypack
is
procedure myproc;
end;
package body mypack
is
procedure myproc
is
begin
deletefrom test;
for x in 1 .. 5 loop
insert into test values(x,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end loop;
commit;
end;
end;已选择16行。
SQL>
成功恢复，但因有sql_undo里有部分语句执行不了，建议在测试环境里恢复好后，抽取其ddl再在开发库里重建。
-- The End --