Find tables with digits in their names in Oracle database

Query below lists:

(A) all tables accessible to the current user in Oracle database having names containing digits

(B) all tables in Oracle database having names containing digits

Query was executed under the Oracle12c Database version.

Query

A. Tables accessible to the current user

select owner as schema_name,
       table_name
from sys.all_tables
where regexp_like (table_name, '[0-9]') 
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   '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')
order by owner,
         table_name;

B. If you have privilege on dba_tables

select owner as schema_name,
       table_name
from sys.dba_tables
where regexp_like (table_name, '[0-9]') 
-- excluding some Oracle maintained schemas
and owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   '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')
order by owner,
         table_name;

Columns

  • schema_name - schema name, owner of the table
  • table_name - name of the table

Rows

  • One row represents one table having name containing digits
  • Scope of rows: (A) all tables accessible to the current user in Oracle database having names containing digits, (B) all tables in Oracle database having names containing digits
  • Ordered by schema name, table_name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.