Oracle - How to get all tables that have FKs of a specfic table?

The r_constraint_name( primary/unique constraint name in the parent table) column in the all_constraints view is referenced by the constraint_name (foreign key(referential integrity) constraint in the child table), when the constraint_type is ‘R’. Using this definition, let us find out all the foreign key(referential integrity) constraints referencing the table: 

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name

from all_constraints
where constraint_type='R'and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='TABLE_NAME');

Here in sub-query, we are supplying ONLY the primary and unique key constraint types with the qualifier where constraint_type in (‘P’,’U’).

For more information you can visit: Finding Foreign Key Constraints in Oracle

Happy Coding :)