Find tables without relationships - Loner Tables - in Oracle database

Ania - Dataedo Team Ania 2019-06-04

Table of Contents:


    This query listed tables that have no foreign keys, meaning they are not referencing any table or are not on the "many" side of FK.

    Query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something we called Loner Tables. This diagram illustrates the concept:

    Learn more about Loner Tables

    Query below lists:

    (A) all tables accessible to the current user in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys

    (B) all tables in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys

    Query was executed under the Oracle9i Database version.

    Query

    A. Tables accessible to the current user

    select 'No FKs >-' refs,
           t.owner as schema_name,
           t.table_name,
           '>- no FKs' fks
    from sys.all_tables t 
    left join (select distinct owner as schema_name,
                               table_name
               from sys.all_constraints 
               where constraint_type = 'R'
               ) fks
               on t.owner = fks.schema_name
               and t.table_name = fks.table_name
    left join (select distinct cons.r_owner as schema_name,
                               ref_cons.table_name as table_name
               from sys.all_constraints cons
               inner join sys.all_constraints ref_cons 
                          on cons.r_owner = ref_cons.owner
                          and cons.r_constraint_name = ref_cons.constraint_name
               where cons.constraint_type = 'R'
               ) referenced_by_others          
               on t.owner = referenced_by_others.schema_name
               and t.table_name = referenced_by_others.table_name
    where 
    fks.table_name is null
    and referenced_by_others.table_name is null
    -- excluding some Oracle maintained schemas
    and t.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', 'WKSYS',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
    order by t.owner,
             t.table_name;
    

    B. If you have privilege on dba_tables and dba_constraints

    select 'No FKs >-' refs,
           t.owner as schema_name,
           t.table_name,
           '>- no FKs' fks
    from sys.dba_tables t 
    left join (select distinct owner as schema_name,
                               table_name
               from sys.dba_constraints 
               where constraint_type = 'R'
               ) fks
               on t.owner = fks.schema_name
               and t.table_name = fks.table_name
    left join (select distinct cons.r_owner as schema_name,
                               ref_cons.table_name as table_name
               from sys.dba_constraints cons
               inner join sys.dba_constraints ref_cons 
                          on cons.r_owner = ref_cons.owner
                          and cons.r_constraint_name = ref_cons.constraint_name
               where cons.constraint_type = 'R'
               ) referenced_by_others          
               on t.owner = referenced_by_others.schema_name
               and t.table_name = referenced_by_others.table_name
    where 
    fks.table_name is null
    and referenced_by_others.table_name is null
    -- excluding some Oracle maintained schemas
    and t.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', 'WKSYS',
       'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
    order by t.owner,
             t.table_name;
    

    Columns

    • refs - symbol indicating lack of references by foreign key constraints
    • schema_name - table owner, schema name
    • table_name - table name
    • fks - symbol indicating lack of foreign key constraints

    Rows

    • One row represents one table that doesn't have foreign keys and is not referenced by other tables with foreign keys
    • Scope of rows: tables that are not related (not referencing and not being referenced with foreign key constraints) with any table: (A) all tables accessible to the current user in Oracle database, (B) all tables in Oracle database
    • Ordered by schema name, 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