11.2.0.2重命名新功能实现原理
=====================
PARSING IN CURSOR #3041965520 len=32 dep=1 uid=0 oct=7 lid=0 tim=1323806332068299 hv=2990570705 ad='2d5b38c4' sqlid='8tp9fa2t40z6j'
delete from user$ where user#=:1
END OF STMT
PARSE #3041965520:c=1000,e=1113,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1323806332068297
BINDS #3041965520:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551c280 bln=22 avl=02 flg=05
value=85
WAIT #3041965520: nam='db file sequential read' ela= 65 file#=3 block#=8419 blocks=1 obj#=0 tim=1323806332071651
EXEC #3041965520:c=4000,e=3682,p=1,cr=1,cu=7,mis=1,r=1,dep=1,og=4,plh=3816956542,tim=1323806332072277
STAT #3041965520 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE USER$ (cr=1 pr=1 pw=0 time=930 us)'
STAT #3041965520 id=2 cnt=1 pid=1 pos=1 obj=47 op='INDEX RANGE SCAN I_USER2 (cr=1 pr=0 pw=0 time=41 us cost=1 size=37 card=1)'
CLOSE #3041965520:c=0,e=14,dep=1,type=0,tim=1323806332072647
=====================
PARSING IN CURSOR #3041965520 len=438 dep=1 uid=0 oct=2 lid=0 tim=1323806332074048 hv=1755424277 ad='2d533948' sqlid='2trzmq5na39hp'
insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,defrole,resource$,ltime,exptime,astatus,lcount,defschclass,
spare1,spare4,ext_username,spare2)values (:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :4),
:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'), '0000-00-00', to_date(NULL), :10),DECODE(to_char(:11, 'YYYY-MM-DD'), '0000-00-00',
to_date(NULL), :11),:12,:13,:14,:15,:16,:17,:18)
END OF STMT
PARSE #3041965520:c=1000,e=1325,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1323806332074045
BINDS #3041965520:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551be74 bln=22 avl=02 flg=05
value=85
Bind#1
oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=2d50086e bln=32 avl=03 flg=09
value="XFF"
Bind#2
oacdty=01 mxl=32(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=2d50088e bln=32 avl=16 flg=09
value="72829DD49D510C88"
Bind#3
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=2d5008e1 bln=08 avl=07 flg=09
value="12/14/2011 3:54:26"
Bind#4
No oacdef for this bind.
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551be50 bln=24 avl=02 flg=05
value=4
Bind#6
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551be2c bln=24 avl=02 flg=05
value=3
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551be08 bln=24 avl=02 flg=05
value=1
Bind#8
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bde4 bln=24 avl=02 flg=05
value=1
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bdc0 bln=24 avl=01 flg=05
value=0
Bind#10
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=2d5008ef bln=08 avl=07 flg=09
value="0/0/-10100 -1:-1:-1"
Bind#11
No oacdef for this bind.
Bind#12
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=2d5008e8 bln=08 avl=07 flg=09
value="0/0/-10100 -1:-1:-1"
Bind#13
No oacdef for this bind.
Bind#14
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bd9c bln=24 avl=01 flg=05
value=0
Bind#15
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bd78 bln=24 avl=01 flg=05
value=0
Bind#16
oacdty=01 mxl=32(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=2d5008ae bln=32 avl=22 flg=09
value="DEFAULT_CONSUMER_GROUP"
Bind#17
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bd54 bln=24 avl=01 flg=05
value=0
Bind#18
oacdty=01 mxl=128(62) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=852 siz=128 off=0
kxsbbbfp=2d500902 bln=128 avl=62 flg=09
value="S:4CB6F7F259A3E37046AEA521C6F644DAE4C3EE6136FFB7B512CB71B1DFD4"
Bind#19
oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#20
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=b551bd30 bln=24 avl=00 flg=05
EXEC #3041965520:c=7000,e=6819,p=0,cr=1,cu=5,mis=1,r=1,dep=1,og=4,plh=0,tim=1323806332081153
STAT #3041965520 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=461 us)'
CLOSE #3041965520:c=0,e=13,dep=1,type=0,tim=1323806332081334
PARSING IN CURSOR #3041965520 len=3509 dep=1 uid=57 oct=47 lid=57 tim=1323806332169524 hv=1920321438 ad='2d60623c' sqlid='38spc81t7bjwy'
declare
TYPE attrs_cur IS REF CURSOR;
m_cur attrs_cur;
m_event varchar2(512);
m_user varchar2(512);
m_owner varchar2(512);
m_user1 varchar2(512);
m_type varchar2(512);
m_name varchar2(5120);
m_column varchar2(5120);
m_cnt NUMBER;
m_stmt varchar2(512);
m_ret varchar2(3000);
m_ret1 varchar2(512);
m_o_stmt VARCHAR2(5120);
begin
m_stmt:='select sys.dbms_standard.dictionary_obj_type from dual';
execute immediate m_stmt into m_type;
if(not (m_type='TABLE' or m_type='TRIGGER' or m_type='USER' or m_type='TABLESPACE'))
then
return;
end if;
m_stmt:='select sys.dbms_standard.sysevent from dual';
execute immediate m_stmt into m_event;
m_stmt:='select SYS_CONTEXT(''USERENV'',''SESSION_USER'') from dual';
execute immediate m_stmt into m_user;
m_stmt:='select sys.dbms_standard.login_user from dual';
execute immediate m_stmt into m_user1;
m_stmt:='select sys.dbms_standard.dictionary_obj_owner from dual';
execute immediate m_stmt into m_owner;
m_stmt:='select sys.dbms_standard.dictionary_obj_name from dual';
execute immediate m_stmt into m_name;
if((instr(upper(m_name),'MDRT_')>0) and m_event='DROP')
then
return;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='CREATE')
then
m_stmt:='select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2) from dual';
execute immediate m_stmt into m_ret using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name);
m_ret:=trim(m_ret);
while (length(m_ret)!=0) loop
if (instr(m_ret,' $$__## ')!=0)
then
m_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));
m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ')+8));
else
m_ret1:=trim(m_ret);
m_ret:='';
end if;
m_stmt:='begin SDO_GEOR_UTL.createDMLTrigger(:1,:2); end;';
execute immediate m_stmt using m_owner||'.'||m_name,SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(m_ret1);
end loop;
return;
end if;
if (m_name!='MDSYS' and m_type='USER' and m_event='DROP')
then
m_stmt:='call sdo_geor_def.doDropUserAndTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='DROP')
then
m_stmt:='call sdo_geor_def.doDropUserAndTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='TRUNCATE')
then
m_stmt:='call sdo_geor_def.doTruncateTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
then
m_stmt:='call sdo_geor_def.doAlterRenameTable()';
execute immediate m_stmt;
end if;
if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='RENAME')
then
m_stmt:='call sdo_geor_def.doRenameTable()';
execute immediate m_stmt;
end if;
if (m_event='DROP' and m_type='TABLE')
then
m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
EXECUTE IMMEDIATE m_stmt;
end if;
if ((m_type='USER' and m_event='DROP') or (m_type='TABLESPACE' and m_event='DROP'))
then
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
end if;
Exception
when others then
if(sqlcode=-13391)
then
m_stmt:=sqlerrm;
m_stmt:=substr(m_stmt,11);
m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391,'''||m_stmt||''')';
execute immediate m_stmt;
end if;
end;
通过10046生成的trace文件分析,该功能主要是通过drop user+create user+modify object dict来实现该功能