Find empty tables in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2018-12-17

Table of Contents:


    Query below lists:

    (A) all tables accessible to the current user in Oracle database with no rows

    (B) all tables in Oracle database with no rows

    Query was executed under the Oracle12c Database version.

    Query

    A. Tables accessible to the current user

    select tab.owner as schema_name,
           tab.table_name
    from sys.all_tables tab
    where num_rows is null 
          or num_rows = 0
          -- excluding some Oracle maintained schemas
          and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
          'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
          'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
          'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
          'WKSYS', 'OUTLN')
    order by schema_name, 
             table_name;
    

    B. If you have privilege on dba_tables

    select tab.owner as schema_name,
           tab.table_name
    from sys.dba_tables tab
    where num_rows is null
          or num_rows = 0
          -- excluding some Oracle maintained schemas
          and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
          'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
          'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
          'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
          'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
          'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
          'WKSYS', 'OUTLN')
    order by schema_name,
             table_name;
    

    Columns

    • schema_name - table schema name
    • table_name - table name

    Rows

    • One row represents one table
    • Scope of rows: all tables without any rows
    • Ordered by schema and table name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept