oracle 数据字典表之:DBA_TABLES

rktkrzadtj

　　//数据字典表 DBA_TABLES 与 ALL_TABLES 字段结构相同,//其创建语句如下（以下脚本来自Oracle10g 10.2.0.3）：CREATE OR REPLACE FORCE VIEW SYS.dba_tables (owner,table_name,tablespace_name,cluster_name,iot_name,status,pct_free,pct_used,ini_trans,max_trans,initial_extent,next_extent,min_extents,max_extents,pct_increase,FREELISTS,freelist_groups,LOGGING,backed_up,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,avg_space_freelist_blocks,num_freelist_blocks,DEGREE,INSTANCES,CACHE,table_lock,sample_size,last_analyzed,partitioned,iot_type,TEMPORARY,secondary,NESTED,BUFFER_POOL,row_movement,global_stats,user_stats,DURATION,skip_corrupt,MONITORING,cluster_owner,dependencies,compression,dropped)ASSELECT u.NAME, o.NAME,DECODE (BITAND (t.property, 2151678048), 0, ts.NAME, NULL),DECODE (BITAND (t.property, 1024), 0, NULL, co.NAME),DECODE ((BITAND (t.property, 512) + BITAND (t.flags, 536870912)),0, NULL,co.NAME),DECODE (BITAND (t.trigflag, 1073741824),1073741824, 'UNUSABLE','VALID'),DECODE (BITAND (t.property, 32 + 64),0, MOD (t.pctfree$, 100),64, 0,NULL),DECODE (BITAND (ts.flags, 32),32, TO_NUMBER (NULL),DECODE (BITAND (t.property, 32 + 64),0, t.pctused$,64, 0,NULL)),DECODE (BITAND (t.property, 32), 0, t.INITRANS, NULL),DECODE (BITAND (t.property, 32), 0, t.MAXTRANS, NULL),s.iniexts * ts.BLOCKSIZE,DECODE (BITAND (ts.flags, 3),1, TO_NUMBER (NULL),s.extsize * ts.BLOCKSIZE),s.minexts, s.maxexts,DECODE (BITAND (ts.flags, 3), 1, TO_NUMBER (NULL), s.extpct),DECODE (BITAND (ts.flags, 32),32, TO_NUMBER (NULL),DECODE (BITAND (o.flags, 2),2, 1,DECODE (s.lists, 0, 1, s.lists))),DECODE (BITAND (ts.flags, 32),32, TO_NUMBER (NULL),DECODE (BITAND (o.flags, 2),2, 1,DECODE (s.GROUPS, 0, 1, s.GROUPS))),DECODE (BITAND (t.property, 32 + 64),0, DECODE (BITAND (t.flags, 32), 0, 'YES', 'NO'),NULL),DECODE (BITAND (t.flags, 1), 0, 'Y', 1, 'N', '?'), t.rowcnt,DECODE (BITAND (t.property, 64), 0, t.blkcnt, NULL),DECODE (BITAND (t.property, 64), 0, t.empcnt, NULL), t.avgspc,t.chncnt, t.avgrln, t.avgspc_flb,DECODE (BITAND (t.property, 64), 0, t.flbcnt, NULL),LPAD (DECODE (t.DEGREE, 32767, 'DEFAULT', NVL (t.DEGREE, 1)), 10),LPAD (DECODE (t.INSTANCES, 32767, 'DEFAULT', NVL (t.INSTANCES, 1)),10),LPAD (DECODE (BITAND (t.flags, 8), 8, 'Y', 'N'), 5),DECODE (BITAND (t.flags, 6), 0, 'ENABLED', 'DISABLED'),t.samplesize, t.analyzetime,DECODE (BITAND (t.property, 32), 32, 'YES', 'NO'),DECODE (BITAND (t.property, 64),64, 'IOT',DECODE (BITAND (t.property, 512),512, 'IOT_OVERFLOW',DECODE (BITAND (t.flags, 536870912),536870912, 'IOT_MAPPING',NULL))),DECODE (BITAND (o.flags, 2), 0, 'N', 2, 'Y', 'N'),DECODE (BITAND (o.flags, 16), 0, 'N', 16, 'Y', 'N'),DECODE (BITAND (t.property, 8192),8192, 'YES',DECODE (BITAND (t.property, 1), 0, 'NO', 'YES')),DECODE (BITAND (o.flags, 2),2, 'DEFAULT',DECODE (s.cachehint,0, 'DEFAULT',1, 'KEEP',2, 'RECYCLE',NULL)),DECODE (BITAND (t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),DECODE (BITAND (t.flags, 512), 0, 'NO', 'YES'),DECODE (BITAND (t.flags, 256), 0, 'NO', 'YES'),DECODE (BITAND (o.flags, 2),0, NULL,DECODE (BITAND (t.property, 8388608),8388608, 'SYS$SESSION','SYS$TRANSACTION')),DECODE (BITAND (t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),DECODE (BITAND (o.flags, 2),2, 'NO',DECODE (BITAND (t.property, 2147483648),2147483648, 'NO',DECODE (ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),DECODE (BITAND (t.property, 1024), 0, NULL, cu.NAME),DECODE (BITAND (t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),DECODE (BITAND (t.property, 32),32, NULL,DECODE (BITAND (s.spare1, 2048),2048, 'ENABLED','DISABLED')),DECODE (BITAND (o.flags, 128), 128, 'YES', 'NO')FROM SYS.user$ u,SYS.ts$ ts,SYS.seg$ s,SYS.obj$ co,SYS.tab$ t,SYS.obj$ o,SYS.obj$ cx,SYS.user$ cu,x$ksppcv ksppcv,x$ksppi ksppiWHERE o.owner# = u.user#AND o.obj# = t.obj#AND BITAND (t.property, 1) = 0AND BITAND (o.flags, 128) = 0AND t.bobj# = co.obj#(+)AND t.ts# = ts.ts#AND t.file# = s.file#(+)AND t.block# = s.block#(+)AND t.ts# = s.ts#(+)AND t.dataobj# = cx.obj#(+)AND cx.owner# = cu.user#(+)AND ksppi.indx = ksppcv.indxAND ksppi.ksppinm = '_dml_monitoring_enabled';
　　来源:http://www.eygle.com/archives/2008/04/dba_tables.html