DBA_TABLES - Query Oracle Table Metadata & Statistics

DBA_TABLES

Overview

DBA_TABLES describes all relational tables in the database, providing one row per table regardless of owner. It is the primary source for schema analysis, capacity planning, and optimizer statistics management. DBAs query it constantly to audit table structure, identify stale statistics, locate tables consuming excessive space, and understand partitioning and compression configurations.

View Type: Data Dictionary View (static) Available Since: Oracle 7 Required Privileges: SELECT on DBA_TABLES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

The DBA_ prefix means the view shows all tables in the database. The equivalent USER_TABLES shows only tables owned by the current user, and ALL_TABLES shows tables the current user can access. Both have an identical column structure.

Key Columns

Column	Datatype	Description
OWNER	VARCHAR2(128)	Schema that owns the table
TABLE_NAME	VARCHAR2(128)	Name of the table
TABLESPACE_NAME	VARCHAR2(30)	Tablespace storing the table; NULL for partitioned, temporary, or index-organized tables
NUM_ROWS	NUMBER	Estimated row count from the last statistics gather; NULL if never analyzed
BLOCKS	NUMBER	Number of data blocks currently used by the table
AVG_ROW_LEN	NUMBER	Average row length in bytes, as recorded at last statistics gather
LAST_ANALYZED	DATE	Timestamp of the most recent DBMS_STATS gather; NULL if never analyzed
PARTITIONED	VARCHAR2(3)	YES if the table is partitioned, NO otherwise
COMPRESSION	VARCHAR2(8)	ENABLED if any form of table compression is active, DISABLED otherwise
COMPRESS_FOR	VARCHAR2(30)	Compression type: BASIC, ADVANCED, QUERY LOW/HIGH, ARCHIVE LOW/HIGH, or NULL
ROW_MOVEMENT	VARCHAR2(8)	ENABLED allows rows to change physical location (required for online redefinition and some partitioning operations)
TEMPORARY	VARCHAR2(1)	Y for global or private temporary tables, N for permanent tables
IOT_TYPE	VARCHAR2(12)	IOT for index-organized tables, IOT_OVERFLOW for overflow segments, NULL for heap tables
DEGREE	VARCHAR2(10)	Degree of parallelism for full table scans; DEFAULT defers to system setting
CACHE	VARCHAR2(5)	Y if the table is marked to be pinned in the buffer cache (CACHE hint / ALTER TABLE … CACHE)
LOGGING	VARCHAR2(3)	YES if redo logging is enabled for DML, NO for NOLOGGING tables
DURATION	VARCHAR2(15)	For temporary tables: SYS$SESSION or SYS$TRANSACTION; NULL for permanent tables
MONITORING	VARCHAR2(3)	YES if table monitoring is active (tracks DML changes for the auto-stats job)

Essential Queries

Largest Tables by Block Count

Identify tables consuming the most storage, ranked by allocated blocks. Useful as a first step in space audits and capacity planning:

SELECT
    owner,
    table_name,
    tablespace_name,
    num_rows,
    blocks,
    ROUND(blocks * 8192 / 1024 / 1024, 2)   AS size_mb,
    avg_row_len,
    partitioned,
    compression,
    last_analyzed
FROM
    dba_tables
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                  'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
    AND blocks IS NOT NULL
ORDER BY
    blocks DESC
FETCH FIRST 30 ROWS ONLY;

Tables with Stale or Missing Statistics

Find tables whose optimizer statistics are stale (older than 7 days) or have never been gathered. Stale statistics are a leading cause of poor execution plans:

SELECT
    owner,
    table_name,
    num_rows,
    blocks,
    last_analyzed,
    CASE
        WHEN last_analyzed IS NULL THEN 'NEVER ANALYZED'
        WHEN last_analyzed < SYSDATE - 7 THEN 'STALE (>' || TRUNC(SYSDATE - last_analyzed) || ' days)'
        ELSE 'CURRENT'
    END                                      AS stats_status,
    partitioned,
    monitoring
FROM
    dba_tables
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                  'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
    AND (last_analyzed IS NULL OR last_analyzed < SYSDATE - 7)
ORDER BY
    last_analyzed ASC NULLS FIRST,
    blocks DESC NULLS LAST;

Partitioned Table Summary

List all partitioned tables with their partition count and compression status. Joins to DBA_TAB_PARTITIONS to aggregate partition-level detail:

SELECT
    t.owner,
    t.table_name,
    t.tablespace_name,
    t.compression,
    t.compress_for,
    t.num_rows,
    t.last_analyzed,
    COUNT(p.partition_name)                  AS partition_count,
    SUM(p.blocks)                            AS total_blocks,
    ROUND(SUM(p.blocks) * 8192 / 1024 / 1024, 2) AS total_size_mb
FROM
    dba_tables       t
    JOIN dba_tab_partitions p
        ON  p.table_owner = t.owner
        AND p.table_name  = t.table_name
WHERE
    t.partitioned = 'YES'
    AND t.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                        'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                        'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
GROUP BY
    t.owner,
    t.table_name,
    t.tablespace_name,
    t.compression,
    t.compress_for,
    t.num_rows,
    t.last_analyzed
ORDER BY
    total_blocks DESC NULLS LAST;

Tables Not Recently Analyzed (with DML Monitoring)

Combine DBA_TABLES with DBA_TAB_MODIFICATIONS to find tables that have had significant DML activity since the last statistics gather, making their current statistics especially likely to be misleading:

SELECT
    t.owner,
    t.table_name,
    t.num_rows,
    t.last_analyzed,
    m.inserts,
    m.updates,
    m.deletes,
    m.truncated,
    ROUND((m.inserts + m.updates + m.deletes)
          / NULLIF(t.num_rows, 0) * 100, 1)  AS pct_changed,
    m.timestamp                              AS last_modified
FROM
    dba_tables        t
    JOIN dba_tab_modifications m
        ON  m.table_owner = t.owner
        AND m.table_name  = t.table_name
        AND m.partition_name IS NULL
WHERE
    t.owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                    'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                    'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
    AND (m.inserts + m.updates + m.deletes) > 1000
ORDER BY
    pct_changed DESC NULLS LAST;

Table Compression Audit

Survey compression usage across a schema or the entire database. Useful before planning compression migrations or estimating potential space savings:

SELECT
    owner,
    compression,
    compress_for,
    COUNT(*)                                 AS table_count,
    SUM(num_rows)                            AS total_rows,
    SUM(blocks)                              AS total_blocks,
    ROUND(SUM(blocks) * 8192 / 1024 / 1024, 2) AS total_size_mb
FROM
    dba_tables
WHERE
    owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'MDSYS', 'ORDSYS', 'EXFSYS', 'CTXSYS', 'WMSYS',
                  'XDB', 'ORDPLUGINS', 'DMSYS', 'APEX_040200')
GROUP BY
    owner,
    compression,
    compress_for
ORDER BY
    owner,
    compression,
    compress_for;

Common Use Cases

• Optimizer statistics management — Query LAST_ANALYZED and NUM_ROWS to build a targeted statistics-gathering script for tables whose stats are out of date, instead of running a costly full-schema gather
• Capacity planning — Sum BLOCKS by TABLESPACE_NAME and owner to understand how storage is distributed and project growth based on current table sizes
• Partitioning audit — Filter on PARTITIONED = ‘YES’ and join to DBA_PART_TABLES to verify that large tables are partitioned appropriately and that partition pruning will apply to common queries
• Compression rollout assessment — Use the compression audit query to identify schemas where BASIC or ADVANCED compression would deliver the greatest space savings before scheduling an ALTER TABLE … COMPRESS operation
• Temporary table inventory — Filter on TEMPORARY = ‘Y’ to find all global and private temporary tables and verify they are properly scoped (SYS$SESSION vs SYS$TRANSACTION) for the application’s transaction pattern
• IOT identification — Filter on IOT_TYPE = ‘IOT’ to locate index-organized tables, which have different physical characteristics and tuning considerations than standard heap tables

Related Views

• DBA_INDEXES — Index metadata for all tables, including clustering factor and status
• DBA_OBJECTS — Object-level metadata including creation date and invalid status; shares TABLE_NAME and OWNER with DBA_TABLES
• DBA_TAB_COLUMNS — Column-level detail (data types, nullability, low/high values) for all tables
• DBA_SEGMENTS — Actual allocated segment sizes in bytes, which can differ from the BLOCKS estimate in DBA_TABLES for partitioned and compressed objects
• DBA_TAB_PARTITIONS — One row per partition for partitioned tables; provides partition-level statistics and storage attributes
• DBA_TAB_MODIFICATIONS — Tracks inserts, updates, and deletes since the last statistics gather, powering the stale-statistics detection logic above

Version Notes

• Oracle 10g: MONITORING column behavior changed — monitoring is now always enabled by default and the column always returns YES for permanent tables
• Oracle 11g: COMPRESS_FOR column added to distinguish between BASIC, OLTP (later renamed ADVANCED), and other compression types
• Oracle 12c (Multitenant): Queries from the CDB root against CDB_TABLES include a CON_ID column; within a PDB, DBA_TABLES behaves identically to pre-12c
• Oracle 12c: INMEMORY column family added (INMEMORY, INMEMORY_PRIORITY, INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION, INMEMORY_DUPLICATE) for In-Memory Column Store configuration
• Oracle 19c / 21c: No structural changes to core columns; automatic statistics gathering enhancements in the maintenance window affect LAST_ANALYZED values
• Oracle 23ai: Real-Time Statistics (introduced in 19c) can cause LAST_ANALYZED to be very recent for tables with active online stats collection, even without a manual DBMS_STATS call