Find foreign table using a given CONSTRAINT NAME

Ever tried to delete a record only to be told get ORA-02292
"integrity constraint (CONSTRAINT_NAME) violated - child record found"

How do you identify the table that is related to the constraint mentioned above. Of course if you explicitly name ur FK constraints(very unlikely) you have no problem.

ORACLE




SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = 'SYS_C0011582'