Here I am giving an example to find out the tables without having relationships in oracle database.
Tables that are not referencing and are not referenced by other tables. Something we called "Loner Tables".
Query 1: 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.
SELECT 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;
Query 2: All tables in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys. (If you have privilege on dba_tables and dba_constraints)
SELECT 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;
Output:
Comments
Post a Comment