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'', -13391, ''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'', -13391, ''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'', -13391, ''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,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)-7,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'', -13391, ''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'', -13391, ''GeoRaster reserved names cannot be used to create regular triggers.'')';
execute immediate m_stmt;
end if;
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;

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,-127/*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,-127/*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,-127/*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,' $$__## ')-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')
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;

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+rename\s+to\s+',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,-127/*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,-127/*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-15 74592
6vqvn8ya0xybh :2 VARCHAR2(32) 30-APR-15 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.