oracle 数据字典表之:DBA_TABLES

最新推荐文章于 2023-03-06 15:24:10 发布

最新推荐文章于 2023-03-06 15:24:10 发布 · 279 阅读

文章标签：

#数据库

本文提供了Oracle数据库中DBA_TABLES视图的创建语句及其字段解释。该视图包含了数据库表的相关信息，如所有者、表名、表空间名称等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//数据字典表 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 ) AS SELECT 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 ksppi WHERE o.owner# = u.user# AND o.obj# = t.obj# AND BITAND (t.property, 1) = 0 AND BITAND (o.flags, 128) = 0 AND 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.indx AND ksppi.ksppinm = '_dml_monitoring_enabled';

来源:http://www.eygle.com/archives/2008/04/dba_tables.html