alter table新增字段操作究竟有何影响？(上篇)

SQL> desc t; Name Null? Type ----------------------------------------- -------- ---------------------------- TID NOT NULL NUMBER(38) SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. SQL> alter table t add (sex varchar2(1)); Table altered. SQL> alter session set events '10046 trace name context off'; Session altered.

SQL ID: fpur97hs1wpp3 Plan Hash: 0 LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT SQL ID: dwuf634dfa8hf Plan Hash: 0 alter table t add (sex varchar2(1)) SQL ID: 3nkd3g3ju5ph1 Plan Hash: 2853959010 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null SQL ID: b1wc53ddd6h3p Plan Hash: 1637390370 select audit$,options from procedure$ where obj#=:1 SQL ID: 3ktacv9r56b51 Plan Hash: 4184428695 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# SQL ID: 8swypbbr0m372 Plan Hash: 893970548 select order#,columns,types from access$ where d_obj#=:1 SQL ID: 3k0c6241uw582 Plan Hash: 1964643588 select actionsize from trigger$ where obj# = :1 SQL ID: 07pcqtmt58zv9 Plan Hash: 1964643588 select action# from trigger$ where obj# = :1 SQL ID: 6mhctgagpvvhp Plan Hash: 1964643588 select baseobject,type#,update$,insert$,delete$,refnewname,refoldname, whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt, refprtname,rowid,actionlineno,trignameline,trignamecol,trignamecolofs, actioncolno from trigger$ where obj# =:1 SQL ID: f91p5x1pzsmu1 Plan Hash: 1482114444 select tc.type#,tc.intcol#,tc.position#,c.type#, c.length,c.scale, c.precision#,c.charsetid,c.charsetform, decode(bitand(c.property,8388608), 8388608, c.spare3, 0) from triggercol$ tc,col$ c, trigger$ tr where tc.obj#=:1 and tc.intcol#=c.intcol# and tr.obj# = tc.obj# and (bitand(tr.property,32) != 32 or bitand(tc.type#, 20) = 20) and ( c.obj#=:2 and (bitand(tc.type#, 1024) = :3 or tc.type# = 0) or c.obj#=:4 and bitand(tc.type#, 1024) = :5) union select type#, intcol#,position#,69,0,0,0,0,0,0 from triggercol$ where obj#=:6 and intcol#= 1001 union select tc.type#,tc.intcol#,tc.position#,121,0,0,0,0,0,0 from triggercol$ tc,trigger$ tr where tr.obj# = tc.obj# and bitand(tr.property, 32) = 32 and tc.obj# = :7 and bitand(tc.type#,20) != 20 SQL ID: 1a8n1zgb7m90w Plan Hash: 1457651150 select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,exptime, ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP', defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1 SQL ID: ga9j9xk5cy9s0 Plan Hash: 1697022209 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece# SQL ID: cvn54b7yz0s8u Plan Hash: 3246118364 select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece# SQL ID: c6awqs517jpj0 Plan Hash: 1319326155 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece# SQL ID: 39m4sx9k63ba2 Plan Hash: 2317816222 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece# SQL ID: 3g7sxtj9d6zd3 Plan Hash: 742841275 select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0) eclare 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_stmt varchar2(512); m_name varchar2(5120); m_column varchar2(5120); m_cnt NUMBER; m_stmt1 varchar2(512); m_var varchar2(512); m_o_stmt VARCHAR2(5120); PRAGMA AUTONOMOUS_TRANSACTION; 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')) 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_CONTEXT(''USERENV'',''CURRENT_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; m_stmt:='select sdo_geor_def.getSqlText from dual'; execute immediate m_stmt into m_o_stmt; if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER') then m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2'; open m_cur for m_stmt using m_owner,m_name; loop fetch m_cur into m_column; exit when m_cur%NOTFOUND; m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual'; execute immediate m_stmt into m_stmt1 using SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column); if (trim(m_stmt1)='TRUE') then m_stmt:='begin sdo_geor_def.doAlterDropColumn(:1,:2,:3); end;'; execute immediate m_stmt using SYS.DBMS_ASSERT.SCHEMA_NAME(m_owner),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_name),SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(m_column); end if; end loop; end if; if (m_event='DROP' and m_type='USER') then m_stmt:='insert into sdo_geor_ddl__table$$ values (1)'; EXECUTE IMMEDIATE m_stmt; commit; end if; if (m_event='DROP' and m_type='TABLE') then m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2'; EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name; if(m_cnt!=0) then m_stmt:='select count(*) from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt into m_cnt; if((m_cnt=0)and (m_user!='SYS' and m_user!='SYSTEM' and m_user!='MDSYS'and m_owner!='MDSYS' and m_owner!='SYS')) then m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391, ''The referenced raster data table(RDT) cannot be dropped.'')'; execute immediate m_stmt; end if; end if; m_stmt:='insert into sdo_geor_ddl__table$$ values (2)'; EXECUTE IMMEDIATE m_stmt; commit; end if; if(m_user='SYS' or m_user='SYSTEM' or m_user='MDSYS' or m_owner='MDSYS' or m_owner='SYS') then return; end if; if (m_event='RENAME' and m_type='TABLE') then m_stmt:='select count(*) from sdo_geor_sysdata_table where sdo_owner=:1 and RDT_TABLE_NAME=:2'; EXECUTE IMMEDIATE m_stmt into m_cnt using m_owner,m_name; if(m_cnt!=0) then m_stmt:='select count(*) from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt into m_cnt; if(m_cnt=0) then m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391, ''The referenced raster data table(RDT) cannot be renamed directly.'')'; execute immediate m_stmt; end if; end if; end if; if (m_type='TRIGGER' and m_event='DROP') then m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual'; EXECUTE IMMEDIATE m_stmt into m_var using m_name; if(m_var is null) then return; end if; m_stmt:='select count(*) from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt into m_cnt; if(m_cnt=0) then m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391, ''GeoRaster DML triggers cannot be dropped.'')'; execute immediate m_stmt; end if; end if; if (m_type='TRIGGER' and m_event='ALTER') then m_o_stmt:=upper(trim(m_o_stmt)); if(instr(m_o_stmt,' COMPILE ')>0 or instr(m_o_stmt,' ENABLE ')>0 or substr(m_o_stmt,length(m_o_stmt)⑻,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)⑺,7)=' ENABLE' ) then return; end if; m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual'; EXECUTE IMMEDIATE m_stmt into m_var using m_name; if(m_var is null) then return; end if; m_stmt:='select count(*) from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt into m_cnt; if(m_cnt=0) then m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391, ''GeoRaster DML triggers cannot be altered.'')'; execute immediate m_stmt; end if; end if; if (m_type='TRIGGER' and m_event='CREATE') then m_stmt:='select REGEXP_SUBSTR(:1,''GRDMLTR_.+'',1,1,''i'') from dual'; EXECUTE IMMEDIATE m_stmt into m_var using m_name; if(m_var is null) then return; end if; m_stmt:='select count(*) from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt into m_cnt; if(m_cnt=0) then m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391, ''GeoRaster reserved names cannot be used to create regular triggers.'')'; execute immediate m_stmt; end if; end if; Exception when others then if(sqlcode=⑴3391) then m_stmt:=sqlerrm; m_stmt:=substr(m_stmt,11); m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391,'''||m_stmt||''')'; execute immediate m_stmt; end if; end; SQL ID: bcv9qynmu1nv9 Plan Hash: 1388734953 select sys.dbms_standard.dictionary_obj_type from dual SQL ID: dcstr36r0vz0d Plan Hash: 3798950322 select procedure#,procedurename,properties,itypeobj# from procedureinfo$ where obj#=:1 order by procedurename desc, overload# desc SQL ID: 32hbap2vtmf53 Plan Hash: 3421168214 select position#,sequence#,level#,argument,type#,charsetid,charsetform, properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc SQL ID: 8wutkrpf8j81q Plan Hash: 1011998884 select max(procedure#) from procedurec$ where obj#=:1 SQL ID: 2xgubd6ayhyb1 Plan Hash: 3418045132 select max(procedure#) from procedureplsql$ where obj#=:1 SQL ID: 5km6gkvuh3n43 Plan Hash: 2075413643 select max(procedure#) from procedurejava$ where obj#=:1 SQL ID: 971vpk0tp7ahm Plan Hash: 726556434 select procedure#,entrypoint# from procedurec$ where obj#=:1 order by procedure# SQL ID: bgjhtnqhr5u9h Plan Hash: 4040384485 select procedure#,entrypoint# from procedureplsql$ where obj#=:1 order by procedure# SQL ID: 5j4c2v06qdhqq Plan Hash: 423935871 select procedure#,ownerlength,classlength,methodlength,siglength, flagslength, cookiesize from procedurejava$ where obj#=:1 order by procedure# SQL ID: 83cq1aqjw5gmg Plan Hash: 0 select ownername,classname,methodname,signature,flags from procedurejava$ where obj#=:1 and procedure#=:2 order by procedure# SQL ID: 12kw3xcxv1qpg Plan Hash: 1388734953 select sys.dbms_standard.sysevent from dual SQL ID: d7y4tdacc7f3j Plan Hash: 1388734953 select SYS_CONTEXT('USERENV','SESSION_USER') from dual SQL ID: 721d7993vjur9 Plan Hash: 1388734953 select SYS_CONTEXT('USERENV','CURRENT_USER') from dual SQL ID: ar9nmtmd28460 Plan Hash: 1388734953 select sys.dbms_standard.dictionary_obj_owner from dual SQL ID: 4a3ccstvk0ssw Plan Hash: 1388734953 select sys.dbms_standard.dictionary_obj_name from dual SQL ID: cjk1ffy5kmm5s Plan Hash: 1964104430 select obj# from oid$ where user#=:1 and oid$=:2 SQL ID: bcg7084jc4um6 Plan Hash: 1388734953 select sdo_geor_def.getSqlText from dual SQL ID: 32hbap2vtmf53 Plan Hash: 0 select position#,sequence#,level#,argument,type#,charsetid,charsetform, properties,nvl(length, 0), nvl(precision#, 0),nvl(scale, 0),nvl(radix, 0), type_owner,type_name,type_subname,type_linkname,pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc SQL ID: 1mjd9xp80vuqa Plan Hash: 3023518864 select node,owner,name from syn$ where obj#=:1 SQL ID: 1mkmz2c6nr80d Plan Hash: 4171642546 select column_name from dba_tab_columns where owner=:1 and table_name=:2 SQL ID: g3wrkmxkxzhf2 Plan Hash: 749386351 select cols,audit$,textlength,intcols,property,flags,rowid from view$ where obj#=:1 SQL ID: 83taa7kaw59c1 Plan Hash: 3765558045 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2, nvl(scale,⑴27/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182, scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$, rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2, nvl(spare3,0) from col$ where obj#=:1 order by intcol# SQL ID: grwydz59pu6mc Plan Hash: 3684871272 select text from view$ where rowid=:1 SQL ID: 6aq34nj2zb2n7 Plan Hash: 2874733959 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# SQL ID: 2q93zsrvbdw48 Plan Hash: 2874733959 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# SQL ID: 7nuw4xwrnuwxq Plan Hash: 1720483994 select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc SQL ID: 9rfqm06xmuwu0 Plan Hash: 832500465 select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc SQL ID: f3g84j69n0tjh Plan Hash: 2335623859 select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc SQL ID: 6qz82dptj0qr7 Plan Hash: 2819763574 select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l.pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.obj# = :1 order by l.intcol# asc SQL ID: 9g485acn2n30m Plan Hash: 2544153582 select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc SQL ID: 32bhha21dkv0v Plan Hash: 3765558045 select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc SQL ID: 0fr8zhn4ymu3v Plan Hash: 1231101765 select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtype$ where obj# = :1 order by intcol# asc SQL ID: 3w4qs0tbpmxr6 Plan Hash: 1224215794 select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj#=:1 SQL ID: gx4mv66pvj3xz Plan Hash: 1932954096 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0), rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1 SQL ID: 53saa2zkr6wc3 Plan Hash: 3954488388 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ where con#=:1 SQL ID: db78fxqxwxt7r Plan Hash: 3312420081 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket SQL ID: 8w5dxxmd9z3kt Plan Hash: 1388734953 select sdo_geor_def.isDropColumn(:1) from dual SQL ID: dadv22a1xru1y Plan Hash: 1000871750 delete from compression$ where obj#=:1 SQL ID: 1gu8t96d0bdmu Plan Hash: 2035254952 select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols, nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans, t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln, t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1), nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0), nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+) SQL ID: 7ng34ruy5awxq Plan Hash: 3992920156 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property, i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey, i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256), i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null, null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj# SQL ID: 5n1fs4m2n2y0r Plan Hash: 299250003 select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1 SQL ID: 87gaftwrm2h68 Plan Hash: 1218588913 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1 SQL ID: gsfnqdfcvy33q Plan Hash: 2453887050 delete from superobj$ where subobj# = :1 SQL ID: 3kywng531fcxu Plan Hash: 2667651180 delete from tab_stats$ where obj#=:1 SQL ID: b5cr4hhndmbuf Plan Hash: 2918346288 update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#= decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9), audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15, rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21, analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1, null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29, flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34, spare6=:35 where obj#=:1 SQL ID: 60uw2vh6q9vn2 Plan Hash: 0 insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale, null$,offset,fixedstorage,segcollength,deflength,default$,col#,property, charsetid,charsetform,spare1,spare2,spare3) values (:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0, null,:7)),decode(:5,2,decode(:8,⑴27/*MAXSB1MINAL*/,null,:8),178,:8,179,:8, 180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12) ,:13,:14,:15,:16,:17,:18,:19,:20) SQL ID: 4yyb4104skrwj Plan Hash: 2683643009 update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9, dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null SQL ID: dbcjnkpkvgy5w Plan Hash: 511615611 update col$ set name=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5, 182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2, decode(:8,⑴27/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8, 183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12, property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18, deflength=decode(:19,0,null,:19),default$=:20 where obj#=:1 and intcol#=:2 SQL ID: by04k8p2ms8dd Plan Hash: 1314400858 delete from idl_ub1$ where obj#=:1 SQL ID: dn6xac8g73tmu Plan Hash: 2807568952 delete from idl_char$ where obj#=:1 SQL ID: dshhzm1vwgcpu Plan Hash: 2238151678 delete from idl_ub2$ where obj#=:1 SQL ID: fhguu20nbk7b3 Plan Hash: 1017615457 delete from idl_sb4$ where obj#=:1 SQL ID: 1vpham7m95msd Plan Hash: 2087301174 delete from error$ where obj#=:1 SQL ID: 74anujtt8zw4h Plan Hash: 3014793611 select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp, nvl(d.property,0), o.type#, o.subname, d.d_attrs from dependency$ d, obj$ o where d.p_obj#=:1 and (d.p_timestamp=nvl(:2, d.p_timestamp) or d.property=2) and o.owner#=nvl(:3,o.owner#) and d.d_obj#=o.obj# order by o.obj# SQL ID: 2faxux5uhbaaq Plan Hash: 0 update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4 SQL ID: 0muahtjw95f68 Plan Hash: 0 update dependency$ set d_reason = :1 where d_obj# = :2 and p_obj# = :3 SQL ID: 1qub197bt5axw Plan Hash: 0 update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3 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')) 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 (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,' $$__## ')⑴)); 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') then m_stmt:='delete from sdo_geor_ddl__table$$'; EXECUTE IMMEDIATE m_stmt; end if; Exception when others then if(sqlcode=⑴3391) then m_stmt:=sqlerrm; m_stmt:=substr(m_stmt,11); m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', ⑴3391,'''||m_stmt||''')'; execute immediate m_stmt; end if; end; SQL ID: 7dwyqv9sawkyb Plan Hash: 1388734953 select sys.dbms_standard.login_user from dual SQL ID: bwyvhzgc956hw Plan Hash: 0 call sdo_geor_def.doAlterRenameTable() SQL ID: b94mm2v4pt594 Plan Hash: 1388734953 select REGEXP_SUBSTR(:1,'s+renames+tos+',1,1,'i') from dual SQL ID: 6vqvn8ya0xybh Plan Hash: 3870945217 update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5, 182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2, decode(:8,⑴27/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8, 183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12, property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18, deflength=decode(:19,0,null,:19),default$=:20 where obj#=:1 and name=:2 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 212 0.07 0.09 2 27 0 0 Execute 573 0.13 0.16 16 34 39 9 Fetch 1269 0.05 0.09 105 2654 0 1973 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2054 0.26 0.35 123 2715 39 1982 Misses in library cache during parse: 81 Misses in library cache during execute: 65 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 123 0.00 0.07 asynch descriptor resize 1 0.00 0.00 Disk file operations I/O 1 0.00 0.00 23 user SQL statements in session. 502 internal SQL statements in session. 525 SQL statements in session. ******************************************************************************** Trace file: bisal_ora_3470.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 23 user SQL statements in trace file. 502 internal SQL statements in trace file. 525 SQL statements in trace file. 83 unique SQL statements in trace file. 11858 lines in trace file. 63 elapsed seconds in trace file.

SQL ID: fpur97hs1wpp3 Plan Hash: 0 LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAIT

SQL ID: dwuf634dfa8hf Plan Hash: 0 alter table t add (sex varchar2(1))

SQL ID: 6vqvn8ya0xybh Plan Hash: 3870945217 update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5, 182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2, decode(:8,⑴27/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8, 183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12, property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18, deflength=decode(:19,0,null,:19),default$=:20 where obj#=:1 and name=:2

SQL> col sql_id format a20 SQL> col name format a20 SQL> col datatype_string format a14 SQL> col value_string format a20 SQL> select sql_id, name, datatype_string, last_captured, value_string 2 from v$sql_bind_capture where sql_id = '6vqvn8ya0xybh' 3 order by last_captured, position; SQL_ID NAME DATATYPE_STRIN LAST_CAPT VALUE_STRING -------------------- ---------- -------------- --------- -------------------- 6vqvn8ya0xybh :1 NUMBER 30-APR⑴5 74592 6vqvn8ya0xybh :2 VARCHAR2(32) 30-APR⑴5 SEX 6vqvn8ya0xybh :3 NUMBER 6vqvn8ya0xybh :4 NUMBER 6vqvn8ya0xybh :5 NUMBER 6vqvn8ya0xybh :6 NUMBER 6vqvn8ya0xybh :5 NUMBER 6vqvn8ya0xybh :7 NUMBER 6vqvn8ya0xybh :7 NUMBER 6vqvn8ya0xybh :7 NUMBER 6vqvn8ya0xybh :7 NUMBER 6vqvn8ya0xybh :5 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :8 NUMBER 6vqvn8ya0xybh :9 NUMBER 6vqvn8ya0xybh :10 NUMBER 6vqvn8ya0xybh :11 NUMBER 6vqvn8ya0xybh :12 NUMBER 6vqvn8ya0xybh :13 NUMBER 6vqvn8ya0xybh :14 NUMBER 6vqvn8ya0xybh :15 NUMBER 6vqvn8ya0xybh :16 NUMBER 6vqvn8ya0xybh :17 NUMBER 6vqvn8ya0xybh :18 NUMBER 6vqvn8ya0xybh :19 NUMBER 6vqvn8ya0xybh :19 NUMBER 6vqvn8ya0xybh :20 VARCHAR2(32)

SQL> col object_name for a5 SQL> select object_name, object_id from dba_objects 2 where object_id = 74592; OBJEC OBJECT_ID ----- ---------- T 74592

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 212 0.07 0.09 2 27 0 0 Execute 573 0.13 0.16 16 34 39 9 Fetch 1269 0.05 0.09 105 2654 0 1973 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2054 0.26 0.35 123 2715 39 1982 Misses in library cache during parse: 81 Misses in library cache during execute: 65 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 123 0.00 0.07 asynch descriptor resize 1 0.00 0.00 Disk file operations I/O 1 0.00 0.00 23 user SQL statements in session. 502 internal SQL statements in session. 525 SQL statements in session. ******************************************************************************** Trace file: bisal_ora_3470.trc Trace file compatibility: 11.1.0.7 Sort options: default 1 session in tracefile. 23 user SQL statements in trace file. 502 internal SQL statements in trace file. 525 SQL statements in trace file. 83 unique SQL statements in trace file. 11858 lines in trace file. 63 elapsed seconds in trace file.