hierarchical query in oracle Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume the number of tables is huge in a schema. What would be the query that gives me the list of tables in that order which I can perform migration.
To do that you could try:

select
   level,
   owner, name,
   referenced_owner, referenced_name
from
   dba_dependencies
start with
    owner = '${YOUR_TABLE_OWNER}'
and name = '${YOUR_TABLE_NAME}'
connect by prior referenced_owner = owner
       and prior referenced_name = name
       and prior referenced_type = type
and type = 'TABLE';


Datapump uses a similar strategy to prevent constraint violations.
I hope this helps.