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

很久以前就有个疑问，见过一些表设计时会留出几个reverse的字段，目的是为了以后扩展，但此时设计的字段类型、长度等都是预计的，未来是否可用，不好说，那为什么会这么做呢？可能的原因是：“我现在设定好字段，需要的时候直接用就行了，不需要新增字段的操作”。

那么，问题就是，新增字段的操作究竟有什么影响？增加表字段的时候，是否会锁表？对DML、DDL有什么影响？如果搞清楚这些，才能对上面的问题给出科学的答案。

为了证明增加字段的操作究竟做了什么，有什么影响，打算使用10046事件来看看。

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.

使用tkprof格式化trace文件，关键的信息如下：

SQL ID: fpur97hs1wpp3Plan Hash: 0LOCK TABLE "T" IN ROW EXCLUSIVE MODE NOWAITSQL ID: dwuf634dfa8hfPlan Hash: 0alter table t add (sex varchar2(1))SQL ID: 3nkd3g3ju5ph1Plan Hash: 2853959010select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,spare2fromobj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is nulland linkname is null and subname is nullSQL ID: b1wc53ddd6h3pPlan Hash: 1637390370select audit$,optionsfromprocedure$ where obj#=:1SQL ID: 3ktacv9r56b51Plan Hash: 4184428695select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,nvl(property,0),subname,type#,d_attrsfromdependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#SQL ID: 8swypbbr0m372Plan Hash: 893970548select order#,columns,typesfromaccess$ where d_obj#=:1SQL ID: 3k0c6241uw582Plan Hash: 1964643588select actionsizefromtrigger$ where obj# = :1SQL ID: 07pcqtmt58zv9Plan Hash: 1964643588select action#fromtrigger$ where obj# = :1SQL ID: 6mhctgagpvvhpPlan Hash: 1964643588select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid,actionlineno,trignameline,trignamecol,trignamecolofs,actioncolnofromtrigger$ where obj# =:1SQL ID: f91p5x1pzsmu1Plan Hash: 1482114444select 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)fromtriggercol$ 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 fromtriggercol$ tc,trigger$ tr where tr.obj# = tc.obj# and bitand(tr.property,32) = 32 and tc.obj# = :7 and bitand(tc.type#,20) != 20SQL ID: 1a8n1zgb7m90wPlan Hash: 1457651150select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,exptime,ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2fromuser$ where name=:1SQL ID: ga9j9xk5cy9s0Plan Hash: 1697022209select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piecefromidl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#SQL ID: cvn54b7yz0s8uPlan Hash: 3246118364select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piecefromidl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#SQL ID: c6awqs517jpj0Plan Hash: 1319326155select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piecefromidl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#SQL ID: 39m4sx9k63ba2Plan Hash: 2317816222select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piecefromidl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#SQL ID: 3g7sxtj9d6zd3Plan Hash: 742841275select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))fromobjauth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#,0)eclareTYPE 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;beginm_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'))thenreturn;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')thenm_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;loopfetch 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')thenm_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')thenm_stmt:='insert into sdo_geor_ddl__table$$ values (1)';EXECUTE IMMEDIATE m_stmt;commit;end if;if (m_event='DROP' and m_type='TABLE')thenm_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)thenm_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'))thenm_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')thenreturn;end if;if (m_event='RENAME' and m_type='TABLE')thenm_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)thenm_stmt:='select count(*) from sdo_geor_ddl__table$$';EXECUTE IMMEDIATE m_stmt into m_cnt;if(m_cnt=0)thenm_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')thenm_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)thenreturn;end if;m_stmt:='select count(*) from sdo_geor_ddl__table$$';EXECUTE IMMEDIATE m_stmt into m_cnt;if(m_cnt=0)thenm_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')thenm_o_stmt:=upper(trim(m_o_stmt));if(instr(m_o_stmt,' COMPILE ')>0 or instr(m_o_stmt,' ENABLE ')>0or substr(m_o_stmt,length(m_o_stmt)-8,8)=' COMPILE' or substr(m_o_stmt,length(m_o_stmt)-7,7)=' ENABLE' )thenreturn;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)thenreturn;end if;m_stmt:='select count(*) from sdo_geor_ddl__table$$';EXECUTE IMMEDIATE m_stmt into m_cnt;if(m_cnt=0)thenm_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')thenm_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)thenreturn;end if;m_stmt:='select count(*) from sdo_geor_ddl__table$$';EXECUTE IMMEDIATE m_stmt into m_cnt;if(m_cnt=0)thenm_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;Exceptionwhen others thenif(sqlcode=-13391)thenm_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: bcv9qynmu1nv9Plan Hash: 1388734953select sys.dbms_standard.dictionary_obj_typefromdualSQL ID: dcstr36r0vz0dPlan Hash: 3798950322select procedure#,procedurename,properties,itypeobj#fromprocedureinfo$ where obj#=:1 order by procedurename desc, overload# descSQL ID: 32hbap2vtmf53Plan Hash: 3421168214select 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_typefromargument$ where obj#=:1 and procedure#=:2 order by sequence# descSQL ID: 8wutkrpf8j81qPlan Hash: 1011998884select max(procedure#)fromprocedurec$ where obj#=:1SQL ID: 2xgubd6ayhyb1Plan Hash: 3418045132select max(procedure#)fromprocedureplsql$ where obj#=:1SQL ID: 5km6gkvuh3n43Plan Hash: 2075413643select max(procedure#)fromprocedurejava$ where obj#=:1SQL ID: 971vpk0tp7ahmPlan Hash: 726556434select procedure#,entrypoint#fromprocedurec$ where obj#=:1 order by procedure#SQL ID: bgjhtnqhr5u9hPlan Hash: 4040384485select procedure#,entrypoint#fromprocedureplsql$ where obj#=:1 order by procedure#SQL ID: 5j4c2v06qdhqqPlan Hash: 423935871select procedure#,ownerlength,classlength,methodlength,siglength, flagslength,cookiesizefromprocedurejava$ where obj#=:1 order by procedure#SQL ID: 83cq1aqjw5gmgPlan Hash: 0select ownername,classname,methodname,signature,flagsfromprocedurejava$ where obj#=:1 and procedure#=:2 order by procedure#SQL ID: 12kw3xcxv1qpgPlan Hash: 1388734953select sys.dbms_standard.syseventfromdualSQL ID: d7y4tdacc7f3jPlan Hash: 1388734953select SYS_CONTEXT('USERENV','SESSION_USER')fromdualSQL ID: 721d7993vjur9Plan Hash: 1388734953select SYS_CONTEXT('USERENV','CURRENT_USER')fromdualSQL ID: ar9nmtmd28460Plan Hash: 1388734953select sys.dbms_standard.dictionary_obj_ownerfromdualSQL ID: 4a3ccstvk0sswPlan Hash: 1388734953select sys.dbms_standard.dictionary_obj_namefromdualSQL ID: cjk1ffy5kmm5sPlan Hash: 1964104430select obj#fromoid$ where user#=:1 and oid$=:2SQL ID: bcg7084jc4um6Plan Hash: 1388734953select sdo_geor_def.getSqlTextfromdualSQL ID: 32hbap2vtmf53Plan Hash: 0select 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_typefromargument$ where obj#=:1 and procedure#=:2 order by sequence# descSQL ID: 1mjd9xp80vuqaPlan Hash: 3023518864select node,owner,namefromsyn$ where obj#=:1SQL ID: 1mkmz2c6nr80dPlan Hash: 4171642546select column_namefromdba_tab_columns where owner=:1 and table_name=:2SQL ID: g3wrkmxkxzhf2Plan Hash: 749386351select cols,audit$,textlength,intcols,property,flags,rowidfromview$ where obj#=:1SQL ID: 83taa7kaw59c1Plan Hash: 3765558045select 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)fromcol$ where obj#=:1 order by intcol#SQL ID: grwydz59pu6mcPlan Hash: 3684871272select textfromview$ where rowid=:1SQL ID: 6aq34nj2zb2n7Plan Hash: 2874733959select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))fromobjauth$ where obj#=:1 and col# is not null group by privilege#, col#,grantee# order by col#, grantee#SQL ID: 2q93zsrvbdw48Plan Hash: 2874733959select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))fromobjauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order bygrantee#SQL ID: 7nuw4xwrnuwxqPlan Hash: 1720483994select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,nvl(typidcol#, 0)fromcoltype$ where obj#=:1 order by intcol# descSQL ID: 9rfqm06xmuwu0Plan Hash: 832500465select intcol#, toid, version#, intcols, intcol#s, flags, synobj#fromsubcoltype$ where obj#=:1 order by intcol# ascSQL ID: f3g84j69n0tjhPlan Hash: 2335623859select col#,intcol#,ntab#fromntab$ where obj#=:1 order by intcol# ascSQL ID: 6qz82dptj0qr7Plan Hash: 2819763574select 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.freepoolsfromlob$ l where l.obj# = :1 order by l.intcol# ascSQL ID: 9g485acn2n30mPlan Hash: 2544153582select col#,intcol#,reftyp,stabid,expctoidfromrefcon$ where obj#=:1 order by intcol# ascSQL ID: 32bhha21dkv0vPlan Hash: 3765558045select col#,intcol#,charsetid,charsetformfromcol$ where obj#=:1 order by intcol# ascSQL ID: 0fr8zhn4ymu3vPlan Hash: 1231101765select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnumfromopqtype$ where obj# = :1 order by intcol# ascSQL ID: 3w4qs0tbpmxr6Plan Hash: 1224215794select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3fromcdef$ where robj#=:1SQL ID: gx4mv66pvj3xzPlan Hash: 1932954096select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3fromcdef$ where obj#=:1SQL ID: 53saa2zkr6wc3Plan Hash: 3954488388select intcol#,nvl(pos#,0),col#,nvl(spare1,0)fromccol$ where con#=:1SQL ID: db78fxqxwxt7rPlan Hash: 3312420081select /*+ rule */ bucket, endpoint, col#, epvaluefromhistgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucketSQL ID: 8w5dxxmd9z3ktPlan Hash: 1388734953select sdo_geor_def.isDropColumn(:1)fromdualSQL ID: dadv22a1xru1yPlan Hash: 1000871750delete from compression$whereobj#=:1SQL ID: 1gu8t96d0bdmuPlan Hash: 2035254952select 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.logicalreadfromtab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)SQL ID: 7ng34ruy5awxqPlan Hash: 3992920156select 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.logicalreadfromind$ 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 wherei.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#SQL ID: 5n1fs4m2n2y0rPlan Hash: 299250003select pos#,intcol#,col#,spare1,bo#,spare2,spare3fromicol$ where obj#=:1SQL ID: 87gaftwrm2h68Plan Hash: 1218588913select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subnamefromobj$ o where o.obj#=:1SQL ID: gsfnqdfcvy33qPlan Hash: 2453887050delete from superobj$wheresubobj# = :1SQL ID: 3kywng531fcxuPlan Hash: 2667651180delete from tab_stats$whereobj#=:1SQL ID: b5cr4hhndmbufPlan Hash: 2918346288update 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=:35whereobj#=:1SQL ID: 60uw2vh6q9vn2Plan Hash: 0insert 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: 4yyb4104skrwjPlan Hash: 2683643009update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14whereowner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linknameis null and subname is nullSQL ID: dbcjnkpkvgy5wPlan Hash: 511615611update 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$=:20whereobj#=:1 and intcol#=:2SQL ID: by04k8p2ms8ddPlan Hash: 1314400858delete from idl_ub1$whereobj#=:1SQL ID: dn6xac8g73tmuPlan Hash: 2807568952delete from idl_char$whereobj#=:1SQL ID: dshhzm1vwgcpuPlan Hash: 2238151678delete from idl_ub2$whereobj#=:1SQL ID: fhguu20nbk7b3Plan Hash: 1017615457delete from idl_sb4$whereobj#=:1SQL ID: 1vpham7m95msdPlan Hash: 2087301174delete from error$whereobj#=:1SQL ID: 74anujtt8zw4hPlan Hash: 3014793611select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,nvl(d.property,0), o.type#, o.subname, d.d_attrsfromdependency$ 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#) andd.d_obj#=o.obj# order by o.obj#SQL ID: 2faxux5uhbaaqPlan Hash: 0update dependency$ set p_timestamp=:1, p_obj#=:2whered_obj#=:3 and p_obj#=:4SQL ID: 0muahtjw95f68Plan Hash: 0update dependency$ set d_reason = :1whered_obj# = :2 and p_obj# = :3SQL ID: 1qub197bt5axwPlan Hash: 0update dependency$ set d_attrs = :1whered_obj# = :2 and p_obj# = :3declareTYPE 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);beginm_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'))thenreturn;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')thenm_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) loopif (instr(m_ret,' $$__## ')!=0)thenm_ret1:=trim(substr(m_ret,1,instr(m_ret,' $$__## ')-1));m_ret:=trim(substr(m_ret,instr(m_ret,' $$__## ')+8));elsem_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')thenm_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')thenm_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')thenm_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')thenm_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')thenm_stmt:='call sdo_geor_def.doRenameTable()';execute immediate m_stmt;end if;if (m_event='DROP' and m_type='TABLE')thenm_stmt:='delete from sdo_geor_ddl__table$$ where id=2';EXECUTE IMMEDIATE m_stmt;end if;if (m_type='USER' and m_event='DROP')thenm_stmt:='delete from sdo_geor_ddl__table$$';EXECUTE IMMEDIATE m_stmt;end if;Exceptionwhen others thenif(sqlcode=-13391)thenm_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: 7dwyqv9sawkybPlan Hash: 1388734953select sys.dbms_standard.login_userfromdualSQL ID: bwyvhzgc956hwPlan Hash: 0call sdo_geor_def.doAlterRenameTable()SQL ID: b94mm2v4pt594Plan Hash: 1388734953select REGEXP_SUBSTR(:1,'\s+rename\s+to\s+',1,1,'i')fromdualSQL ID: 6vqvn8ya0xybhPlan Hash: 3870945217update 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$=:20whereobj#=:1 and name=:2OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 212 0.07 0.09 2 27 0 0Execute 573 0.13 0.16 16 34 39 9Fetch 1269 0.05 0.09 105 2654 0 1973------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2054 0.26 0.35 123 2715 39 1982Misses in library cache during parse: 81Misses in library cache during execute: 65Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------db file sequential read 123 0.00 0.07asynch descriptor resize 1 0.00 0.00Disk file operations I/O 1 0.00 0.0023 user SQL statements in session.502 internal SQL statements in session.525 SQL statements in session.********************************************************************************Trace file: bisal_ora_3470.trcTrace file compatibility: 11.1.0.7Sort options: default1 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.

就是一个alter table增加字段的操作，trace文件如此之长，真崩溃。。。

全部的信息，以现在的功力，不能都解释清楚，但针对这个问题，通过一些关键的点，应该可以看出端倪：

1. 10046开始记录后的第一条语句：

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

说明此时对T以NOWAIT方式，加了ROW EXCLUSIVE模式锁(关于ROW EXCLUSIVE，会在下篇中实验)。

2. 接下来就是执行的新增字段的SQL语句：

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

3. 接下来就是各种查，有数据字典表的，有PLSQL，虽然不是都明白，但感觉就一个字：乱，再加一字：晕。但大部分SQL执行的时间都在x毫秒。

4. 我们直接看关闭10046事件之前的最后一句：

SQL ID: 6vqvn8ya0xybhPlan Hash: 3870945217update 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$=:20whereobj#=:1 and name=:2

执行的是col$表的更新语句。从表名看，col$是列的数据字典表，使用了绑定变量，那这些值是什么，就成了问题的关键。

5. 为了查看绑定变量值，从网上学了一招：

使用v$sql_bind_capture可以查看仍在内存中的SQL绑定变量值，找了其中一些，有的已经查不到了，但上面10046前的最后一句SQL使用的绑定变量值如下：

SQL> col sql_id format a20SQL> col name format a20SQL> col datatype_string format a14SQL> col value_string format a20SQL> select sql_id, name, datatype_string, last_captured, value_string2 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 745926vqvn8ya0xybh :2 VARCHAR2(32) 30-APR-15 SEX6vqvn8ya0xybh :3 NUMBER6vqvn8ya0xybh :4 NUMBER6vqvn8ya0xybh :5 NUMBER6vqvn8ya0xybh :6 NUMBER6vqvn8ya0xybh :5 NUMBER6vqvn8ya0xybh :7 NUMBER6vqvn8ya0xybh :7 NUMBER6vqvn8ya0xybh :7 NUMBER6vqvn8ya0xybh :7 NUMBER6vqvn8ya0xybh :5 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :8 NUMBER6vqvn8ya0xybh :9 NUMBER6vqvn8ya0xybh :10 NUMBER6vqvn8ya0xybh :11 NUMBER6vqvn8ya0xybh :12 NUMBER6vqvn8ya0xybh :13 NUMBER6vqvn8ya0xybh :14 NUMBER6vqvn8ya0xybh :15 NUMBER6vqvn8ya0xybh :16 NUMBER6vqvn8ya0xybh :17 NUMBER6vqvn8ya0xybh :18 NUMBER6vqvn8ya0xybh :19 NUMBER6vqvn8ya0xybh :19 NUMBER6vqvn8ya0xybh :20 VARCHAR2(32)

看来可以解释许多问题了，

(1) update col$语句中一共有20个绑定变量，上面SQL显示的绑定变量值，同样是20个，说明是对应的。

(2) update col$的where条件是编号为1和2的绑定变量值，这里显示的是74592和SEX。

(3) 再来看看这个74592是什么，从上面SQL的条件obj#=:1，猜测是一个对象，

SQL> col object_name for a5SQL> select object_name, object_id from dba_objects2 where object_id = 74592;OBJEC OBJECT_ID----- ----------T 74592

74592就是这张表T，SEX是新增字段名。

于是，可以猜测，之前已经将SEX字段加入了相应的数据字典表，最后一句就是更新col$中T表SEX字段的一些信息。

6. trace文件的最后列出了所有递归调用语句的消耗统计：

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 212 0.07 0.09 2 27 0 0Execute 573 0.13 0.16 16 34 39 9Fetch 1269 0.05 0.09 105 2654 0 1973------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2054 0.26 0.35 123 2715 39 1982Misses in library cache during parse: 81Misses in library cache during execute: 65Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------db file sequential read 123 0.00 0.07asynch descriptor resize 1 0.00 0.00Disk file operations I/O 1 0.00 0.0023 user SQL statements in session.502 internal SQL statements in session.525 SQL statements in session.********************************************************************************Trace file: bisal_ora_3470.trcTrace file compatibility: 11.1.0.7Sort options: default1 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.

可以看出，为了一个alter table新增字段的操作，总共执行了几百次的内部SQL，大部分是通过索引方式扫描，执行的时间是100多毫秒，很快，因此感觉不到，但实际Oracle自己做了这么多后台操作，感叹他的强大，一个简单的新增字段操作，就有如此复杂的实现，但性能上基本让你感觉不到，佩服得五体投地。

总结：

本篇文章主要说明了alter table新增字段操作，Oracle究竟做了什么，至于这操作有什么影响，其实就是ROW EXCLUSIVE会有什么影响，下篇文章会进行一些实验来说明ROW EXCLUSIVE的作用和影响。