DBA_TABLES

Description of all relational tables in the database

Name Datatype Length Mandatory Comments
OWNER VARCHAR2 (30) Yes Owner of the table
TABLE_NAME VARCHAR2 (30) Yes Name of the table
TABLESPACE_NAME VARCHAR2 (30)
Name of the tablespace containing the table
CLUSTER_NAME VARCHAR2 (30)
Name of the cluster, if any, to which the table belongs
IOT_NAME VARCHAR2 (30)
Name of the index-only table, if any, to which the overflow or mapping table entry belongs
PCT_FREE NUMBER

Minimum percentage of free space in a block
PCT_USED NUMBER

Minimum percentage of used space in a block
INI_TRANS NUMBER

Initial number of transactions
MAX_TRANS NUMBER

Maximum number of transactions
INITIAL_EXTENT NUMBER

Size of the initial extent in bytes
NEXT_EXTENT NUMBER

Size of secondary extents in bytes
MIN_EXTENTS NUMBER

Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER

Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER

Percentage increase in extent size
FREELISTS NUMBER

Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER

Number of freelist groups allocated in this segment
LOGGING VARCHAR2 (3)
Logging attribute
BACKED_UP VARCHAR2 (1)
Has table been backed up since last modification?
NUM_ROWS NUMBER

The number of rows in the table
BLOCKS NUMBER

The number of used blocks in the table
EMPTY_BLOCKS NUMBER

The number of empty (never used) blocks in the table
AVG_SPACE NUMBER

The average available free space in the table
CHAIN_CNT NUMBER

The number of chained rows in the table
AVG_ROW_LEN NUMBER

The average row length, including row overhead
AVG_SPACE_FREELIST_BLOCKS NUMBER

The average freespace of all blocks on a freelist
NUM_FREELIST_BLOCKS NUMBER

The number of blocks on the freelist
DEGREE VARCHAR2 (10)
The number of threads per instance for scanning the table
INSTANCES VARCHAR2 (10)
The number of instances across which the table is to be scanned
CACHE VARCHAR2 (5)
Whether the table is to be cached in the buffer cache
TABLE_LOCK VARCHAR2 (8)
Whether table locking is enabled or disabled
SAMPLE_SIZE NUMBER

The sample size used in analyzing this table
LAST_ANALYZED DATE

The date of the most recent time this table was analyzed
PARTITIONED VARCHAR2 (3)
Is this table partitioned? YES or NO
IOT_TYPE VARCHAR2 (12)
If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL
TEMPORARY VARCHAR2 (1)
Can the current session only see data that it place in this object itself?
SECONDARY VARCHAR2 (1)
Is this table object created as part of icreate for domain indexes?
NESTED VARCHAR2 (3)
Is the table a nested table?
BUFFER_POOL VARCHAR2 (7)
The default buffer pool to be used for table blocks
ROW_MOVEMENT VARCHAR2 (8)
Whether partitioned row movement is enabled or disabled
GLOBAL_STATS VARCHAR2 (3)
Are the statistics calculated without merging underlying partitions?
USER_STATS VARCHAR2 (3)
Were the statistics entered directly by the user?
DURATION VARCHAR2 (15)
If temporary table, then duration is sys$session or sys$transaction else NULL
SKIP_CORRUPT VARCHAR2 (8)
Whether skip corrupt blocks is enabled or disabled
MONITORING VARCHAR2 (3)
Should we keep track of the amount of modification?
CLUSTER_OWNER VARCHAR2 (30)
Owner of the cluster, if any, to which the table belongs
DEPENDENCIES VARCHAR2 (8)
Should we keep track of row level dependencies?
COMPRESSION VARCHAR2 (8)
Whether table compression is enabled or not

Usage:


CURSOR cur_table_dets(c_name in dba_tables.table_name%type
                    , c_owner in dba_objects.owner%type)
   -- List table details
   IS select tab.table_name
      ,      tab.tablespace_name
      ,      tab.pct_free
      ,      tab.pct_used
      ,      tab.temporary
      ,      tab.duration
      from dba_tables tab
      where table_name = c_name
      and owner = c_owner
      order by tab.table_name;


  -- is this an Apps Database?
  cursor cur_apps
  is
  select count(table_name) app_count
  from dba_tables
  where owner = 'APPLSYS'
  and table_name = 'FND_APPLICATION';