Question: I need a query to find all
tables that reference my table. I tried to delete my table but I
got a foreign key constraint error:
Evidently this is an Oracle DELETE TABLE constraint violation.
How do I find all tables that reference my table?
Answer: I have complete notes on using the
drop table command.
Also see my notes on script
to display all objects related to an Oracle table.
Also see my notes on
dba_constraints.
You can check for foreign key constraint errors by looking at the
dba_constraints and the dba_cons_columns views.
Using the cascade constraints clause in a drop
table will force a cascade delete to occur in all child
tables.
Oracle also has the
drop
table if exists feature.
There are several data dictionary views that allow you to find
all foreign reference to an Oracle table, namely the
dba_constraints and dba_cons_columns views.
Here is one script to display all references to a table using a
non-correlated subquery:
select *
from
all_constraints
where
r_constraint_name in
(select constraint_name
from
all_constraints
where
table_name='MYTAB')
;
This SQL will show all dependent tables for a
table, all reference to the table within other tables:
select
cons.owner
as child_owner,
cons.table_name as child_table,
cons.constraint_name
constaint_name,
cons.constraint_type constraint_type,
col.owner
parent_owner,
col.table_name parent_table,
col.column_name
column_name
from
see
BC Oracle code depot
for alternate scripts.
dba_cons_columns col,
dba_constraints
cons
where
cons.r_owner =
col.owner
and
cons.r_constraint_name
= col.constraint_name
and
cons.table_name = '<TABLE NAME>'
You can also run a query to
find all tables within a PL/SQL procedure.
There are many other ways to see all related tables and you can
download working scripts at the
BC Oracle
code depot.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
