?????create or replace???PL/SQL??
- by Liu Maclean(???)
????T.Askmaclean.com?????10gR2??????procedure,?????????create or replace ??????????????????,????Oracle???????????????????procedure?
??Maclean ??2?10gR2???????????PL/SQL?????:
??1: ??Flashback Query ????,?????????????flashback database,??????????create or replace???SQL??source$??????????undo data,????????????:
SQL> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
SQL> create or replace procedure maclean_proc as
2 begin
3 execute immediate 'select 1 from dual';
4 end;
5 /
Procedure created.
SQL> select * from dba_source where name='MACLEAN_PROC';
OWNER NAME TYPE LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS MACLEAN_PROC PROCEDURE 1 procedure maclean_proc as
SYS MACLEAN_PROC PROCEDURE 2 begin
SYS MACLEAN_PROC PROCEDURE 3 execute immediate 'select 1 from dual';
SYS MACLEAN_PROC PROCEDURE 4 end;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2660057
create or replace procedure maclean_proc as
begin
-- I am new procedure
execute immediate 'select 2 from dual';
end;
/
Procedure created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2660113
SQL> select * from dba_source where name='MACLEAN_PROC';
OWNER NAME TYPE LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS MACLEAN_PROC PROCEDURE 1 procedure maclean_proc as
SYS MACLEAN_PROC PROCEDURE 2 begin
SYS MACLEAN_PROC PROCEDURE 3 -- I am new procedure
SYS MACLEAN_PROC PROCEDURE 4 execute immediate 'select 2 from dual';
SYS MACLEAN_PROC PROCEDURE 5 end;
SQL> create table old_source as select * from dba_source as of scn 2660057 where name='MACLEAN_PROC';
Table created.
SQL> select * from old_source where name='MACLEAN_PROC';
OWNER NAME TYPE LINE TEXT
---------- ------------------------------ ------------ ---------- --------------------------------------------------
SYS MACLEAN_PROC PROCEDURE 1 procedure maclean_proc as
SYS MACLEAN_PROC PROCEDURE 2 begin
SYS MACLEAN_PROC PROCEDURE 3 execute immediate 'select 1 from dual';
SYS MACLEAN_PROC PROCEDURE 4 end;
?????????scn??flashback query????,????????as of timestamp??????????,????PL/SQL????????????????undo??????????,????????????replace/drop ??????PL/SQL???
??2 ??logminer??replace/drop PL/SQL?????SQL???DELETE??,??logminer?UNDO SQL???PL/SQL??????
????????????????archivelog????,??????????????? minimal supplemental logging,??????????Unsupported SQLREDO???:
create or replace?? ?? procedure???????SQL??????, ??????procedure????????????????, source$??????????????:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> create or replace procedure maclean_proc as
2 begin
3 execute immediate 'select 1 from dual';
4 end;
5 /
Procedure created.
SQL>
SQL> oradebug setmypid;
Statement processed.
SQL>
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL>
SQL> create or replace procedure maclean_proc as
2 begin
3 execute immediate 'select 2 from dual';
4 end;
5 /
Procedure created.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_4305.trc
[oracle@vrh8 ~]$ egrep "update|insert|delete|merge" /s01/admin/G10R25/udump/g10r25_ora_4305.trc
delete from procedureinfo$ where obj#=:1
delete from argument$ where obj#=:1
delete from procedurec$ where obj#=:1
delete from procedureplsql$ where obj#=:1
delete from procedurejava$ where obj#=:1
delete from vtable$ where obj#=:1
insert into procedureinfo$(obj#,procedure#,overload#,procedurename,properties,itypeobj#) values (:1,:2,:3,:4,:5,:6)
insert into argument$( obj#,procedure$,procedure#,overload#,position#,sequence#,level#,argument,type#,default#,in_out,length,precision#,scale,radix,charsetid,charsetform,properties,type_owner,type_name,type_subname,type_linkname,pls_type) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23)
insert into procedureplsql$(obj#,procedure#,entrypoint#) values (:1,:2,:3)
update procedure$ set audit$=:2,options=:3 where obj#=:1
delete from source$ where obj#=:1
insert into source$(obj#,line,source) values (:1,:2,:3)
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
update idl_sb4$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub1$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_char$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub2$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_sb4$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub1$ where obj#=:1 and part=:2 and version<>:3
delete from idl_char$ where obj#=:1 and part=:2 and version<>:3
delete from idl_ub2$ where obj#=:1 and part=:2 and version<>:3
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
update idl_sb4$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
update idl_ub1$ set piece#=:1 ,length=:2 , piece=:3 where obj#=:4 and part=:5 and piece#=:6 and version=:7
delete from idl_char$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from idl_ub2$ where obj#=:1 and part=:2 and (piece#<:3 or piece#>:4) and version=:5
delete from error$ where obj#=:1
delete from settings$ where obj# = :1
insert into settings$(obj#, param, value) values (:1, :2, :3)
delete from warning_settings$ where obj# = :1
insert into warning_settings$(obj#, warning_num, global_mod, property) values (:1, :2, :3, :4)
delete from dependency$ where d_obj#=:1
delete from access$ where d_obj#=:1
insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp, property, d_attrs)values (:1,:2,:3,:4,:5,:6, :7)
insert into access$(d_obj#,order#,columns,types) values (:1,:2,:3,:4)
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
?drop procedure??????source$???PL/SQL?????:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> drop procedure maclean_proc;
Procedure dropped.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_4331.trc
delete from context$ where obj#=:1
delete from dir$ where obj#=:1
delete from type_misc$ where obj#=:1
delete from library$ where obj#=:1
delete from procedure$ where obj#=:1
delete from javaobj$ where obj#=:1
delete from operator$ where obj#=:1
delete from opbinding$ where obj#=:1
delete from opancillary$ where obj#=:1
delete from oparg$ where obj# = :1
delete from com$ where obj#=:1
delete from source$ where obj#=:1
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from error$ where obj#=:1
delete from settings$ where obj# = :1
delete from procedureinfo$ where obj#=:1
delete from argument$ where obj#=:1
delete from procedurec$ where obj#=:1
delete from procedureplsql$ where obj#=:1
delete from procedurejava$ where obj#=:1
delete from vtable$ where obj#=:1
delete from dependency$ where d_obj#=:1
delete from access$ where d_obj#=:1
delete from objauth$ where obj#=:1
update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
??????????source$???redo:
SQL> alter system switch logfile;
System altered.
SQL> select sequence#,name from v$archived_log where sequence#=(select max(sequence#) from v$archived_log);
SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
242
/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_242_7vnm13k6_.arc
SQL> exec dbms_logmnr.add_logfile ('/s01/flash_recovery_area/G10R25/archivelog/2012_05_21/o1_mf_1_242_7vnm13k6_.arc',options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SQL> select sql_redo,sql_undo from v$logmnr_contents where seg_name = 'SOURCE$' and operation='DELETE';
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '1' and "SOURCE" = 'procedure maclean_proc as
' and ROWID = 'AAAABIAABAAALpyAAN';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','1','procedure maclean_proc as
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '2' and "SOURCE" = 'begin
' and ROWID = 'AAAABIAABAAALpyAAO';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','2','begin
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '3' and "SOURCE" = 'execute immediate ''select 1 from dual'';
' and ROWID = 'AAAABIAABAAALpyAAP';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','3','execute immediate ''select 1 from dual'';
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '4' and "SOURCE" = 'end;' and ROWID = 'AAAABIAABAAALpyAAQ';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','4','end;');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '1' and "SOURCE" = 'procedure maclean_proc as
' and ROWID = 'AAAABIAABAAALpyAAJ';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','1','procedure maclean_proc as
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '2' and "SOURCE" = 'begin
' and ROWID = 'AAAABIAABAAALpyAAK';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','2','begin
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '3' and "SOURCE" = 'execute immediate ''select 2 from dual'';
' and ROWID = 'AAAABIAABAAALpyAAL';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','3','execute immediate ''select 2 from dual'';
');
delete from "SYS"."SOURCE$" where "OBJ#" = '56059' and "LINE" = '4' and "SOURCE" = 'end;' and ROWID = 'AAAABIAABAAALpyAAM';
insert into "SYS"."SOURCE$"("OBJ#","LINE","SOURCE") values ('56059','4','end;');
???? logminer???UNDO SQL???????source$????,?DELETE????????????,????SOURCE????????????PL/SQL???DDL???
