| |
 |
|
DBA_TABLES view tips
Oracle Tips by Burleson Consulting |
DBA_TABLES
<< Return to the index
Oracle 11g makes the following comments about the DBA_TABLES table: "Description of all relational tables in the database"
Related notes on DBA_TABLES:
Column description of the DBA_TABLES view:BACKED_UPDescription of DBA_TABLES.BACKED_UP: "Has table been backed up since last modification?" NUM_ROWSDescription of DBA_TABLES.NUM_ROWS: "The number of rows in the table" BLOCKSDescription of DBA_TABLES.BLOCKS: "The number of used blocks in the table" EMPTY_BLOCKSDescription of DBA_TABLES.EMPTY_BLOCKS: "The number of empty (never used) blocks in the table" AVG_SPACEDescription of DBA_TABLES.AVG_SPACE: "The average available free space in the table" CHAIN_CNTDescription of DBA_TABLES.CHAIN_CNT: "The number of chained rows in the table" AVG_ROW_LENDescription of DBA_TABLES.AVG_ROW_LEN: "The average row length, including row overhead" AVG_SPACE_FREELIST_BLOCKSDescription of DBA_TABLES.AVG_SPACE_FREELIST_BLOCKS: "The average freespace of all blocks on a freelist" NUM_FREELIST_BLOCKSDescription of DBA_TABLES.NUM_FREELIST_BLOCKS: "The number of blocks on the freelist" DEGREEDescription of DBA_TABLES.DEGREE: "The number of threads per instance for scanning the table" INSTANCESDescription of DBA_TABLES.INSTANCES: "The number of instances across which the table is to be scanned" CACHEDescription of DBA_TABLES.CACHE: "Whether the table is to be cached in the buffer cache" TABLE_LOCKDescription of DBA_TABLES.TABLE_LOCK: "Whether table locking is enabled or disabled" SAMPLE_SIZEDescription of DBA_TABLES.SAMPLE_SIZE: "The sample size used in analyzing this table" LAST_ANALYZEDDescription of DBA_TABLES.LAST_ANALYZED: "The date of the most recent time this table was analyzed" PARTITIONEDDescription of DBA_TABLES.PARTITIONED: "Is this table partitioned? YES or NO" IOT_TYPEDescription of DBA_TABLES.IOT_TYPE: "If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL" TEMPORARYDescription of DBA_TABLES.TEMPORARY: "Can the current session only see data that it place in this object itself?" SECONDARYDescription of DBA_TABLES.SECONDARY: "Is this table object created as part of icreate for domain indexes?" NESTEDDescription of DBA_TABLES.NESTED: "Is the table a nested table?" BUFFER_POOLDescription of DBA_TABLES.BUFFER_POOL: "The default buffer pool to be used for table blocks" ROW_MOVEMENTDescription of DBA_TABLES.ROW_MOVEMENT: "Whether partitioned row movement is enabled or disabled" GLOBAL_STATSDescription of DBA_TABLES.GLOBAL_STATS: "Are the statistics calculated without merging underlying partitions?" USER_STATSDescription of DBA_TABLES.USER_STATS: "Were the statistics entered directly by the user?" DURATIONDescription of DBA_TABLES.DURATION: "If temporary table, then duration is sys$session or sys$transaction else NULL" SKIP_CORRUPTDescription of DBA_TABLES.SKIP_CORRUPT: "Whether skip corrupt blocks is enabled or disabled" MONITORINGDescription of DBA_TABLES.MONITORING: "Should we keep track of the amount of modification?" CLUSTER_OWNERDescription of DBA_TABLES.CLUSTER_OWNER: "Owner of the cluster, if any, to which the table belongs" DEPENDENCIESDescription of DBA_TABLES.DEPENDENCIES: "Should we keep track of row level dependencies?" COMPRESSIONDescription of DBA_TABLES.COMPRESSION: "Whether table compression is enabled or not" COMPRESS_FORDescription of DBA_TABLES.COMPRESS_FOR: "Compress what kind of operations" DROPPEDDescription of DBA_TABLES.DROPPED: "Whether table is dropped and is in Recycle Bin" READ_ONLYDescription of DBA_TABLES.READ_ONLY: "Whether table is read only or not" OWNERDescription of DBA_TABLES.OWNER: "Owner of the table" TABLE_NAMEDescription of DBA_TABLES.TABLE_NAME: "Name of the table" TABLESPACE_NAMEDescription of DBA_TABLES.TABLESPACE_NAME: "Name of the tablespace containing the table" CLUSTER_NAMEDescription of DBA_TABLES.CLUSTER_NAME: "Name of the cluster, if any, to which the table belongs" IOT_NAMEDescription of DBA_TABLES.IOT_NAME: "Name of the index-only table, if any, to which the overflow or mapping table entry belongs" STATUSDescription of DBA_TABLES.STATUS: "Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,
VALID otherwise" PCT_FREEDescription of DBA_TABLES.PCT_FREE: "Minimum percentage of free space in a block" PCT_USEDDescription of DBA_TABLES.PCT_USED: "Minimum percentage of used space in a block" INI_TRANSDescription of DBA_TABLES.INI_TRANS: "Initial number of transactions" MAX_TRANSDescription of DBA_TABLES.MAX_TRANS: "Maximum number of transactions" INITIAL_EXTENTDescription of DBA_TABLES.INITIAL_EXTENT: "Size of the initial extent in bytes" NEXT_EXTENTDescription of DBA_TABLES.NEXT_EXTENT: "Size of secondary extents in bytes" MIN_EXTENTSDescription of DBA_TABLES.MIN_EXTENTS: "Minimum number of extents allowed in the segment" MAX_EXTENTSDescription of DBA_TABLES.MAX_EXTENTS: "Maximum number of extents allowed in the segment" PCT_INCREASEDescription of DBA_TABLES.PCT_INCREASE: "Percentage increase in extent size" FREELISTSDescription of DBA_TABLES.FREELISTS: "Number of process freelists allocated in this segment" FREELIST_GROUPSDescription of DBA_TABLES.FREELIST_GROUPS: "Number of freelist groups allocated in this segment" LOGGINGDescription of DBA_TABLES.LOGGING: "Logging attribute"
DBA_TABLES View SourceOracle 11g's data dictionary defines the DBA_TABLES view using the following source query:
select u.name, o.name,
decode(bitand(t.property,2151678048), 0, ts.name,
decode(t.ts#, 0, null, ts.name)),
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(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 0, null,
decode(bitand(s.spare1, 16777216), 16777216,
'FOR ALL OPERATIONS', 'DIRECT LOAD ONLY'))),
decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
decode(bitand(t.trigflag, 2097152), 2097152, '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'
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
 |
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
