Find empty tables in Oracle database

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.