How To Get Parent And Child Tables In Oracle

This query gives list of children tables of a given table:

SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');

And this one gives list of parent tables of a given table:

SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('&tab');

Useful Point: For apps schema we cant get result as they r synonyms, need to go owner of table schema and run the above query.

Reference:http://www.oracleinformation.com/2014/09/how-to-get-parent-and-child-tables-in.html