11galter table问题

aaaaaaaa2000

版主你好：

alter table te1 add job varchar2(2) default 'ta' ;

alter table te1 add ge varchar2(2) default 't1' not null;----这个好像是直接更改数据字典，因为做了2个10046的trace想看一下具体区别在那，但是看不明白，请版主给理一下思路，看看到底这2个语句做了啥

[size=1em]some types of tables (for example, tables without LOB columns), if you specify both a NOT NULL constraint and a default value, the database can optimize the column add operation and greatly reduce the amount of time that the table is locked for DML

Maclean Liu(刘相兵

Before Oracle 11g adding new columns with DEFAULT values and NOT NULL constraint required both an exclusive lock on the table and the default value to be stored in all existing records.

Now in Oracle 11g the database can optimize the resource usage and storage requirements for this operation, the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced and space is saved.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
* Add a NOT NULL column with a default value
* Add a nullable column without a default value
* Add a virtual column

release 1002000300 -


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:30.43

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0


release 1101000600 –


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:00.10

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0


From http://tonguc.wordpress.com/2008 ... lumn-functionality/

Maclean Liu(刘相兵

ODM Finding

SQL> conn maclean/maclean
Connected.


SQL> create table mac(t1 int);

Table created.



SQL> alter session set events '10046 trace name context forever,level 1';

Session altered.

SQL> alter table mac add mac1 varchar2(10) default 'maclean' not null;

Table altered.


SQL> select * from v$diag_info;
         2 Default Trace File
/s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_7389.trc





oracle@vrh2 ~]$ egrep -i "insert|update|delete" /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_7389.trc|grep -v STAT
   m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
   m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
delete from compression$ where obj#=:1
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1
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
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)
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
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


insert into ecol$ values (:1, :2, :3)


update con$ set con#=:3,spare1=:4 where owner#=:1 and name=:2
insert into con$(owner#,name,con#,spare1)values(:1,:2,:3,:4)
delete from idl_ub1$ where obj#=:1
delete from idl_char$ where obj#=:1
delete from idl_ub2$ where obj#=:1
delete from idl_sb4$ where obj#=:1
delete from error$ where obj#=:1
update dependency$ set p_timestamp=:1, p_obj#=:2 where d_obj#=:3 and p_obj#=:4
update dependency$ set d_reason = :1 where d_obj# = :2 and p_obj# = :3
update dependency$ set d_attrs = :1 where d_obj# = :2 and p_obj# = :3
   m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
   m_stmt:='delete from sdo_geor_ddl__table$$';
delete from superobj$ where subobj# = :1
delete from tab_stats$ where obj#=:1
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
insert into ccol$(con#,obj#,intcol#,pos#,col#,spare1) values(:1,:2,:3,decode(:4,0,null,:4),:5, :6)
insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1,spare2,spare3)values(:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),decode(:8,0,null,:8),decode(:9,0,null,:9),decode(:10,0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15,:16,:17)
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,terminal,action#,returncode, logoff$lread,logoff$pread,logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid) values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),     :4,:5,:6,:7,:8,     :9,:10,:11,:12,     cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,:18,     :19,:20,:21,:22)




you  can find ecol$ from 11g  $ORACLE_HOME/rdbms/admin/dcore.bsq

Maclean Liu(刘相兵

REM This table is an extension to col$ and is used (for now) to store the
REM default value with which a column was added
create table ecol$
(
  tabobj#      number,
  colnum       number,
  binaryDefVal blob)
tablespace system
/

REM index on ecol$
create index ecol_ix1 on ecol$(tabobj#, colnum);









ecol$ an extension to col$ and is used (for now) to store the default value with which a column was added



The SQL trace (10046) of the statement references "ecol$":

...
select binaryDefVal, length(binaryDefVal) from ecol$ where tabobj# = :1 and colnum = :2
...

"ecol$" holds some internal information for added columns.