Consider this query:
with ri_tables as
(select parent, parent_key, child, foreign_key, min(ri_depth) as ri_depth
from (select parent, parent_key, child, foreign_key, level as ri_depth
from (select parent_table.table_name parent,
parent_constraint.constraint_name as parent_key,
child_table.table_name child,
child_constraint.constraint_name as foreign_key
from all_tables parent_table
inner join all_constraints parent_constraint
on parent_table.table_name = parent_constraint.table_name
inner join all_constraints child_constraint
on child_constraint.r_constraint_name = parent_constraint.constraint_name
inner join all_tables child_table
on child_table.table_name = child_constraint.table_name
where parent_constraint.constraint_type IN( 'P', 'U' )
and child_constraint.constraint_type = 'R'
and child_table.table_name != parent_table.table_name
)
start with parent = 'POLYGON'
connect by nocycle prior child = parent
)
group by parent, child, parent_key, foreign_key
)
select ri_tables.parent as source_table,
pc.column_name as source_key_column,
ri_tables.child as target_table,
cc.column_name as target_key_column,
pc.position as position
from ri_tables
inner join all_cons_columns pc
on ri_tables.parent = pc.table_name
and ri_tables.parent_key = pc.constraint_name
inner join all_cons_columns cc
on ri_tables.child = cc.table_name
and ri_tables.foreign_key = cc.constraint_name
and pc.position = cc.position
order by ri_tables.ri_depth, ri_tables.parent, ri_tables.child,
pc.position;
This walks the foreign key hierarchy, listing all of the table dependencies in order, showing what how the foreign key column links to the parent.
There isn't a trivial way to build the insert statements you want without trawling through user_tab_columns for each table you're copying data across. The SQL above at least shows which columns you don't need to copy but instead need to use a sequence value.
Of course, if you were to have any multi-column primary or foreign key, any code depending on the SQL above would completely break.
I think it's time to consider whether you NEED to duplicate all this data versus you WANT to duplicate all of this data. Having duplicative data in relational databases is generally considered A Bad Idea.
