Find a foreign key references to a table

Foreign key can be created in the table creation or by "alter table ..."

CREATE TABLE my_table
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constrint_fk
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (col_1, col_2, ... col_n)
);

ALTER TABLE my_table ADD (
  CONSTRAINT constrint_fk
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (col_1, col_2, ... col_n)
  enable validate);

Here, my_table is the child table to the foreign key constrint_fk. Columns (col_1, col_2, ... col_n) shall be the primary key or an unique index of the parent_table. Columns (column1, column2, ... column_n) shall be an index of my_table to avoid 'eng: TM' locks in a busy database when the parent_table gets DELETE or UPDATE operations frequently.

When I tried to truncate a table before refreshing it, I got error "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". Below query will identify all foreign keys referenced by child tables:

SELECT DISTINCT a.table_name CHILD_NAME, a.constraint_name F_KEY, a.status, a.last_change
  FROM dba_constraints a, dba_constraints b
WHERE a.OWNER = UPPER('<owner>')
   AND   a.r_constraint_name = b.constraint_name
   AND   a.constraint_type = 'R'
   AND   b.constraint_type in ('P', 'U')
   AND   b.table_name = UPPER('<MY_TABLE>');

Then, run "alter table owner.CHILD_NAME disable constraint F_KEY;" to disable all constraints (foreign keys). After that, I can truncate table MY_TABLE. But if I want to drop the table, I need to drop the constraints first.

One table can have many foreign keys referencing multiple parent tables. Below query finds all foreign keys that links MY_TABLE to all parent tables:

SELECT c.table_name, c.constraint_name, c.constraint_type, c2.table_name P_table, c2.constraint_name P_constraint
  FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
 WHERE  c.OWNER = UPPER('<owner>')
     AND c.table_name =UPPER('<MY_TABLE>')
     AND c.constraint_TYPE = 'R';

When I delete/update data in table MY_TABLE, I do not need to disable foreign keys to all parent table generated from above query.