DBA_TABLES describes all relational tables in the database. Its columns are the same as those in ALL_TABLES. To gather statistics for this view, use the ANALYZE SQL statement.
- ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the ANALYZE SQL statement.
- USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
| Column | Datatype | NULL | Description |
| OWNER | VARCHAR2(30) | NOT NULL | Owner of the table |
| TABLE_NAME | VARCHAR2(30) | NOT NULL | Name of the table |
| TABLESPACE_NAME | VARCHAR2(30) | | Name of the tablespace containing the table; NULL for partitioned, temporary, and index-organized tables |
| CLUSTER_NAME | VARCHAR2(30) | | Name of the cluster, if any, to which the table belongs |
| IOT_NAME | VARCHAR2(30) | | Name of the index-organized table, if any, to which the overflow or mapping table entry belongs. If the IOT_TYPE column is not NULL, then this column contains the base table name. |
| STATUS | VARCHAR2(8) | | If a previous DROP TABLE operation failed, indicates whether the table is unusable (UNUSABLE) or valid (VALID) |
| PCT_FREE | NUMBER | | Minimum percentage of free space in a block; NULL for partitioned tables |
| PCT_USED | NUMBER | | Minimum percentage of used space in a block; NULL for partitioned tables |
| INI_TRANS | NUMBER | | Initial number of transactions; NULL for partitioned tables |
| MAX_TRANS | NUMBER | | Maximum number of transactions; NULL for partitioned tables |
| INITIAL_EXTENT | NUMBER | | Size of the initial extent (in bytes); NULL for partitioned tables |
| NEXT_EXTENT | NUMBER | | Size of secondary extents (in bytes); NULL for partitioned tables |
| MIN_EXTENTS | NUMBER | | Minimum number of extents allowed in the segment; NULL for partitioned tables |
| MAX_EXTENTS | NUMBER | | Maximum number of extents allowed in the segment; NULL for partitioned tables |
| PCT_INCREASE | NUMBER | | Percentage increase in extent size; NULL for partitioned tables |
| FREELISTS | NUMBER | | Number of process freelists allocated to the segment; NULL for partitioned tables |
| FREELIST_GROUPS | NUMBER | | Number of freelist groups allocated to the segment; NULL for partitioned tables |
| LOGGING | VARCHAR2(3) | | Indicates whether or not changes to the table are logged; NULL for partitioned tables:
|
| BACKED_UP | VARCHAR2(1) | | Indicates whether the table has been backed up since the last modification (Y) or not (N) |
| NUM_ROWS* | NUMBER | | Number of rows in the table |
| BLOCKS* | NUMBER | | Number of used data blocks in the table |
| EMPTY_BLOCKS | NUMBER | | Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the ANALYZE statement. |
| AVG_SPACE* | NUMBER | | Average amount of free space, in bytes, in a data block allocated to the table |
| CHAIN_CNT* | NUMBER | | Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID |
| AVG_ROW_LEN* | NUMBER | | Average length of a row in the table (in bytes) |
| AVG_SPACE_FREELIST _BLOCKS | NUMBER | | Average freespace of all blocks on a freelist |
| NUM_FREELIST_BLOCKS | NUMBER | | Number of blocks on the freelist |
| DEGREE | VARCHAR2(10) | | Number of threads per instance for scanning the table, or DEFAULT |
| INSTANCES | VARCHAR2(10) | | Number of instances across which the table is to be scanned, or DEFAULT |
| CACHE | VARCHAR2(5) | | Indicates whether the table is to be cached in the buffer cache (Y) or not (N) |
| TABLE_LOCK | VARCHAR2(8) | | Indicates whether table locking is enabled (ENABLED) or disabled (DISABLED) |
| SAMPLE_SIZE | NUMBER | | Sample size used in analyzing this table |
| LAST_ANALYZED | DATE | | Date on which this table was most recently analyzed |
| PARTITIONED | VARCHAR2(3) | | Indicates whether the table is partitioned (YES) or not (NO) |
| IOT_TYPE | VARCHAR2(12) | | If the table is an index-organized table, then IOT_TYPE is IOT, IOT_OVERFLOW, or IOT_MAPPING. If the table is not an index-organized table, then IOT_TYPE is NULL. |
| TEMPORARY | VARCHAR2(1) | | Indicates whether the table is temporary (Y) or not (N) |
| SECONDARY | VARCHAR2(1) | | Indicates whether the table is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y) or not (N) |
| NESTED | VARCHAR2(3) | | Indicates whether the table is a nested table (YES) or not (NO) |
| BUFFER_POOL | VARCHAR2(7) | | Default buffer pool for the table; NULL for partitioned tables:
|
| ROW_MOVEMENT | VARCHAR2(8) | | Indicates whether partitioned row movement is enabled (ENABLED) or disabled (DISABLED) |
| GLOBAL_STATS | VARCHAR2(3) | | For partitioned tables, indicates whether statistics were collected for the table as a whole (YES) or were estimated from statistics on underlying partitions and subpartitions (NO) |
| USER_STATS | VARCHAR2(3) | | Indicates whether statistics were entered directly by the user (YES) or not (NO) |
| DURATION | VARCHAR2(15) | | Indicates the duration of a temporary table:
Null - Permanent table |
| SKIP_CORRUPT | VARCHAR2(8) | | Indicates whether Oracle Database ignores blocks marked corrupt during table and index scans (ENABLED) or raises an error (DISABLED). To enable this feature, run the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS procedure. |
| MONITORING | VARCHAR2(3) | | Indicates whether the table has the MONITORING attribute set (YES) or not (NO) |
| CLUSTER_OWNER | VARCHAR2(30) | | Owner of the cluster, if any, to which the table belongs |
| DEPENDENCIES | VARCHAR2(8) | | Indicates whether row-level dependency tracking is enabled (ENABLED) or disabled (DISABLED) |
| COMPRESSION | VARCHAR2(8) | | Indicates whether table compression is enabled (ENABLED) or not (DISABLED); NULL for partitioned tables |
| COMPRESS_FOR | VARCHAR2(18) | | Default compression for what kind of operations:
|
| DROPPED | VARCHAR2(3) | | Indicates whether the table has been dropped and is in the recycle bin (YES) or not (NO); NULL for partitioned tables |
Note:
- Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package.
- DBA_TABLES.NUM_ROWS can be used to check whether the dictionary statistics are stale, i.e., no longer representative of the data in the tables and indexes.
- DBA_TABLES.DEPENDENCIES is used to indicate if a table is set with ORWDEPENDENCIES. There is a bug (Bug 6653934) confirmed in 10.1.0.5/10.2.0.4 that dump/block corruption may occur from ONLINE segment shrink with ROWDEPENDENCIES.
Oracle data dictionary views
Oracle dynamic performance views
| Last updated: July 27, 2009 |
