| |
 |
|
DBA_ALL_TABLES view tips
Oracle Tips by Burleson Consulting |
DBA_ALL_TABLES
<< Return to the index
Oracle 11g makes the following comments about the DBA_ALL_TABLES table: "Description of all object and relational tables in the database"
Related notes on DBA_ALL_TABLES:
Column description of the DBA_ALL_TABLES view:OBJECT_ID_TYPEDescription of DBA_ALL_TABLES.OBJECT_ID_TYPE: "If user-defined OID, then USER-DEFINED, else if system generated OID, then SYST
EM GENERATED" TABLE_TYPE_OWNERDescription of DBA_ALL_TABLES.TABLE_TYPE_OWNER: "Owner of the type of the table if the table is an object table" TABLE_TYPEDescription of DBA_ALL_TABLES.TABLE_TYPE: "Type of the table if the table is an object table" TEMPORARYDescription of DBA_ALL_TABLES.TEMPORARY: "Can the current session only see data that it place in this object itself?" SECONDARYDescription of DBA_ALL_TABLES.SECONDARY: "Is this table object created as part of icreate for domain indexes?" NESTEDDescription of DBA_ALL_TABLES.NESTED: "Is the table a nested table?" BUFFER_POOLDescription of DBA_ALL_TABLES.BUFFER_POOL: "The default buffer pool to be used for table blocks" ROW_MOVEMENTDescription of DBA_ALL_TABLES.ROW_MOVEMENT: "Whether partitioned row movement is enabled or disabled" GLOBAL_STATSDescription of DBA_ALL_TABLES.GLOBAL_STATS: "Are the statistics calculated without merging underlying partitions?" USER_STATSDescription of DBA_ALL_TABLES.USER_STATS: "Were the statistics entered directly by the user?" DURATIONDescription of DBA_ALL_TABLES.DURATION: "If temporary table, then duration is sys$session or sys$transaction else NULL" SKIP_CORRUPTDescription of DBA_ALL_TABLES.SKIP_CORRUPT: "Whether skip corrupt blocks is enabled or disabled" MONITORINGDescription of DBA_ALL_TABLES.MONITORING: "Should we keep track of the amount of modification?" CLUSTER_OWNERDescription of DBA_ALL_TABLES.CLUSTER_OWNER: "Owner of the cluster, if any, to which the table belongs" DEPENDENCIESDescription of DBA_ALL_TABLES.DEPENDENCIES: "Should we keep track of row level dependencies?" COMPRESSIONDescription of DBA_ALL_TABLES.COMPRESSION: "Whether table compression is enabled or not" COMPRESS_FORDescription of DBA_ALL_TABLES.COMPRESS_FOR: "Compress what kind of operations" DROPPEDDescription of DBA_ALL_TABLES.DROPPED: "Whether table is dropped and is in Recycle Bin" OWNERDescription of DBA_ALL_TABLES.OWNER: "Owner of the table" TABLE_NAMEDescription of DBA_ALL_TABLES.TABLE_NAME: "Name of the table" TABLESPACE_NAMEDescription of DBA_ALL_TABLES.TABLESPACE_NAME: "Name of the tablespace containing the table" CLUSTER_NAMEDescription of DBA_ALL_TABLES.CLUSTER_NAME: "Name of the cluster, if any, to which the table belongs" IOT_NAMEDescription of DBA_ALL_TABLES.IOT_NAME: "Name of the index-only table, if any, to which the overflow or mapping table entry belongs" STATUSDescription of DBA_ALL_TABLES.STATUS: "Status of the table will be UNUSABLE if a previous DROP TABLE operation failed,
VALID otherwise" PCT_FREEDescription of DBA_ALL_TABLES.PCT_FREE: "Minimum percentage of free space in a block" PCT_USEDDescription of DBA_ALL_TABLES.PCT_USED: "Minimum percentage of used space in a block" INI_TRANSDescription of DBA_ALL_TABLES.INI_TRANS: "Initial number of transactions" MAX_TRANSDescription of DBA_ALL_TABLES.MAX_TRANS: "Maximum number of transactions" INITIAL_EXTENTDescription of DBA_ALL_TABLES.INITIAL_EXTENT: "Size of the initial extent in bytes" NEXT_EXTENTDescription of DBA_ALL_TABLES.NEXT_EXTENT: "Size of secondary extents in bytes" MIN_EXTENTSDescription of DBA_ALL_TABLES.MIN_EXTENTS: "Minimum number of extents allowed in the segment" MAX_EXTENTSDescription of DBA_ALL_TABLES.MAX_EXTENTS: "Maximum number of extents allowed in the segment" PCT_INCREASEDescription of DBA_ALL_TABLES.PCT_INCREASE: "Percentage increase in extent size" FREELISTSDescription of DBA_ALL_TABLES.FREELISTS: "Number of process freelists allocated in this segment" FREELIST_GROUPSDescription of DBA_ALL_TABLES.FREELIST_GROUPS: "Number of freelist groups allocated in this segment" LOGGINGDescription of DBA_ALL_TABLES.LOGGING: "Logging attribute" BACKED_UPDescription of DBA_ALL_TABLES.BACKED_UP: "Has table been backed up since last modification?" NUM_ROWSDescription of DBA_ALL_TABLES.NUM_ROWS: "The number of rows in the table" BLOCKSDescription of DBA_ALL_TABLES.BLOCKS: "The number of used blocks in the table" EMPTY_BLOCKSDescription of DBA_ALL_TABLES.EMPTY_BLOCKS: "The number of empty (never used) blocks in the table" AVG_SPACEDescription of DBA_ALL_TABLES.AVG_SPACE: "The average available free space in the table" CHAIN_CNTDescription of DBA_ALL_TABLES.CHAIN_CNT: "The number of chained rows in the table" AVG_ROW_LENDescription of DBA_ALL_TABLES.AVG_ROW_LEN: "The average row length, including row overhead" AVG_SPACE_FREELIST_BLOCKSDescription of DBA_ALL_TABLES.AVG_SPACE_FREELIST_BLOCKS: "The average freespace of all blocks on a freelist" NUM_FREELIST_BLOCKSDescription of DBA_ALL_TABLES.NUM_FREELIST_BLOCKS: "The number of blocks on the freelist" DEGREEDescription of DBA_ALL_TABLES.DEGREE: "The number of threads per instance for scanning the table" INSTANCESDescription of DBA_ALL_TABLES.INSTANCES: "The number of instances across which the table is to be scanned" CACHEDescription of DBA_ALL_TABLES.CACHE: "Whether the table is to be cached in the buffer cache" TABLE_LOCKDescription of DBA_ALL_TABLES.TABLE_LOCK: "Whether table locking is enabled or disabled" SAMPLE_SIZEDescription of DBA_ALL_TABLES.SAMPLE_SIZE: "The sample size used in analyzing this table" LAST_ANALYZEDDescription of DBA_ALL_TABLES.LAST_ANALYZED: "The date of the most recent time this table was analyzed" PARTITIONEDDescription of DBA_ALL_TABLES.PARTITIONED: "Is this table partitioned? YES or NO" IOT_TYPEDescription of DBA_ALL_TABLES.IOT_TYPE: "If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL"
DBA_ALL_TABLES View SourceOracle 11g's data dictionary defines the DBA_ALL_TABLES view using the following source query:
select 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, NULL, NULL, NULL, TEMPORARY, SECONDARY, NESTED,
BUFFER_POOL, ROW_MOVEMENT,
GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING,
CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED
from dba_tables
union all
select "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","OBJECT_ID_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","SECONDARY","NESTED","BUFFER_POOL","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURATION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","COMPRESS_FOR","DROPPED" from dba_object_tables
 |
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
|
|
