建立表物件時，oracle做了些什麼？

SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl3939/orcl3939/trace/orcl3939_ora_5019.trc

下面是摘自部分轉儲檔案：

=====================
PARSING IN CURSOR #1 len=38 dep=0 uid=0 oct=1 lid=0 tim=1430587498608239 hv=4223418474 ad='2fbe8754' sqlid='c5nvzurxvsh3a'
 create table diy as select * from  tt
END OF STMT
PARSE #1:c=6999,e=44170,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=77869534,tim=1430587498608168
=====================
PARSING IN CURSOR #3 len=202 dep=1 uid=0 oct=3 lid=0 tim=1430587498608598 hv=3819099649 ad='349a5d20' sqlid='3nkd3g3ju5ph1'
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
END OF STMT
PARSE #3:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2853959010,tim=1430587498608596
BINDS #3:
 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=00bea494  bln=22  avl=01  flg=05
  value=0
 Bind#1
  oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=00bea468  bln=32  avl=03  flg=05
  value="DIY"
．．．
．．．
select sys.dbms_standard.sysevent from dual
select SYS_CONTEXT('USERENV','SESSION_USER') from dual
select SYS_CONTEXT('USERENV','CURRENT_USER') from dual
select sys.dbms_standard.dictionary_obj_owner from dual
select sys.dbms_standard.dictionary_obj_name from dual
select sdo_geor_def.getSqlText from dual
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
select obj# from objerror$
select obj#, owner, node from syn$ where name=:1
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
select 1 from sys.streams$_prepare_ddl p  where  ((p.global_flag = 1 and :1 is null) or           (p.global_flag = 0 and p.usrid = :2)) and rownum = 1
select file# from file$ where ts#=:1
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL,:17),:18,:19)
insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols,audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances,dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)values(:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null,:9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
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)
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)
select sys.dbms_standard.dictionary_obj_type from dual
select sys.dbms_standard.sysevent from dual
select SYS_CONTEXT('USERENV','SESSION_USER') from dual
select sys.dbms_standard.login_user  from dual
select sys.dbms_standard.dictionary_obj_owner from dual
select sys.dbms_standard.dictionary_obj_name from dual
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
．．．
．．．
從上面可以看出，建立一張表時（ddl操作）oracle做了一系列複雜dml操作，如向obj$表中插入物件diy,tab$記錄表資料，seg$記錄資料segment的資訊，col$記錄欄位資訊
可以具體分析其過程每個dml語句，感興趣可以研究。
記得我們前面關於oracle open初始化過程時的文章提到過sql.bsq指令碼，它包含了資料字典表的定義及註釋說明，資料字典表用來儲存表，約束，索引及其他資料庫資訊，資料字典表通常以$結尾(OBJ$,CON$...)
在11g中，根據功能的不同，被分歸到不同的.bsq檔案，sql.bsq是它們的入口：
sql.bsq:
....
....
dcore.bsq
dsqlddl.bsq
dmanage.bsq
dplsql.bsq
dtxnspc.bsq
dfmap.bsq
denv.bsq
drac.bsq
dsec.bsq
doptim.bsq
dobj.bsq
djava.bsq
dpart.bsq
drep.bsq
daw.bsq
dsummgt.bsq
dtools.bsq
dexttab.bsq
ddm.bsq
dlmnr.bsq
ddst.bsq
下面摘自部分dcore.bsq:
....
....
create table tab$                                             /* table table */
( obj#          number not null,                            /* object number */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#      number,                          /* data layer object number */
  ts#           number not null,                        /* tablespace number */
  file#         number not null,               /* segment header file number */
  block#        number not null,              /* segment header block number */
  bobj#         number,                /* base object number (cluster / iot) */
  tab#          number,    /* table number in cluster, NULL if not clustered */
  cols          number not null,                        /* number of columns */
  clucols       number,/* number of clustered columns, NULL if not clustered */
  pctfree$      number not null, /* minimum free space percentage in a block */
  pctused$      number not null, /* minimum used space percentage in a block */
  initrans      number not null,            /* initial number of transaction */
  maxtrans      number not null,            /* maximum number of transaction */
  flags         number not null, /* 0x00     = unmodified since last backup 
                                    0x01     = modified since then 
                                    0x02     = DML locks restricted to <= SX 
    0x04     = DML locks <= SX not acquired
                                    0x08     = CACHE  
                                    0x10     = table has been analyzed
                                    0x20     = table has no logging
                                    0x40     = 7.3 -> 8.0 data object
                                               migration required    
                                    0x0080   = current summary dependency
                                    0x0100   = user-specified stats
                                    0x0200   = global stats           
                                    0x0800   = table has security policy 
                                    0x020000 = Move Partitioned Rows 
                                   0x0400000 = table has sub tables
                                  0x00800000 = row dependencies enabled */
                /* 0x10000000 = this IOT has a  physical rowid mapping table */
                /* 0x20000000 = mapping table of an IOT(with physical rowid) */
  audit$        varchar2("S_OPFL") not null,             /* auditing options */
  rowcnt        number,                                    /* number of rows */
  blkcnt        number,                                  /* number of blocks */
  empcnt        number,                            /* number of empty blocks */
  avgspc        number,       /* average available free space/iot ovfl stats */
  chncnt        number,                            /* number of chained rows */
  avgrln        number,                                /* average row length */
  avgspc_flb    number,       /* avg avail free space of blocks on free list */
  flbcnt        number,                             /* free list block count */
  analyzetime   date,                        /* timestamp when last analyzed */
  samplesize    number,                 /* number of rows sampled by Analyze */
/* 
 * Legal values for degree, instances: 
 *     NULL (used to represent 1 on disk/dictionary and implies noparallel), or
 *     2 thru EB2MAXVAL-1 (user supplied values), or
 *     EB2MAXVAL (implies use default value) 
 */
  degree        number,      /* number of parallel query slaves per instance */
  instances     number,        /* number of OPS instances for parallel query */
/* => the number of dictionary columns => the number of columns 
 * that have dictionary meta-data associated with them. This is a superset of 
 * and . 
 *     = +
 */
  intcols       number not null,               /* number of internal columns */
/* => the number of REAL columns (ie) columns that actually
 * store data.
 */
  kernelcols    number not null,          /* number of REAL (kernel) columns */
  property      number not null,            /* table properties (bit flags): */
                              /* 0x01 = typed table, 0x02 = has ADT columns, */
                 /* 0x04 = has nested-TABLE columns, 0x08 = has REF columns, */
                      /* 0x10 = has array columns, 0x20 = partitioned table, */
               /* 0x40 = index-only table (IOT), 0x80 = IOT w/ row OVerflow, */
             /* 0x100 = IOT w/ row CLustering, 0x200 = IOT OVeRflow segment, */
               /* 0x400 = clustered table, 0x800 = has internal LOB columns, */
        /* 0x1000 = has primary key-based OID$ column, 0x2000 = nested table */
                    /* 0x4000 = View is Read Only, 0x8000 = has FILE columns */
       /* 0x10000 = obj view's OID is system-gen, 0x20000 = used as AQ table */
                                   /* 0x40000 = has user-defined lob columns */
                               /* 0x00080000 = table contains unused columns */
                            /* 0x100000 = has an on-commit materialized view */
                             /* 0x200000 = has system-generated column names */
                                      /* 0x00400000 = global temporary table */
                            /* 0x00800000 = session-specific temporary table */
                                        /* 0x08000000 = table is a sub table */
                                        /*   0x20000000 = pdml itl invariant */
                                          /* 0x80000000 = table is external  */
                          /* PFLAGS2: 0x400000000 = delayed segment creation */
  /* PFLAGS2: 0x20000000000 = result cache mode FORCE enabled on this table  */
  /* PFLAGS2: 0x40000000000 = result cache mode MANUAL enabled on this table */
  /* PFLAGS2: 0x80000000000 = result cache mode AUTO enabled on this table   */
  trigflag      number,   /* first two bytes for trigger flags, the rest for */
                   /* general use, check tflags_kqldtvc in kqld.h for detail */
                                            /* 0x00000001 deferred RPC Queue */
                                                  /* 0x00000002 snapshot log */
                                        /* 0x00000004 updatable snapshot log */
                                             /* 0x00000008 = context trigger */
                                    /* 0x00000010 = synchronous change table */
                                             /* 0x00000020 = Streams trigger */
                                        /* 0x00000040 = Content Size Trigger */
                                         /* 0x00000080 = audit vault trigger */
                           /* 0x00000100 = Streams Auxiliary Logging trigger */
                     /* 0x00010000 = server-held key encrypted columns exist */
                       /* 0x00020000 = user-held key encrypted columns exist */
                                          /* 0x00200000 = table is read only */
                                     /* 0x00400000 = lobs use shared segment */
                                                 /* 0x00800000 = queue table */
                                   /* 0x10000000 = streams unsupported table */
                                            /* enabled at some point in past */
                            /* 0x80000000 = Versioning enabled on this table */
  spare1        number,                       /* used to store hakan_kqldtvc */
  spare2        number,         /* committed partition # used by drop column */
  spare3        number,                           /* summary sequence number */
  spare4        varchar2(1000),         /* committed RID used by drop column */
  spare5        varchar2(1000),      /* summary related information on table */
  spare6        date                                  /* flashback timestamp */
)
....
....
是不是很複雜！