Skip to Main Content
  • Questions
  • Hierarchical structure from children tables

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 27, 2016 - 2:48 pm UTC

Last updated: October 31, 2016 - 11:20 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hi,

I need to copy data from a source database to a remote destination database (via dblink).
Currently, my procedure (that runs periodically once a day), acts in this way:

1) disables all foreign keys on the destination database;
2) copies the data from the tables (in no particular order regarding the tables in parent-child relationship), using a MERGE statement, in order to insert the new data created on the source after the last execution, or upgrade existing ones;
3) re-enables all the foreign keys on the destination database.

My problem is that when copying, the source database is used and then new records may have been inserted in the tables with parent-child relationship. For example, if I have two tables:

TABLE_A
TABLE_B ---> references TABLE_A


since TABLE_A has a large amount of data, the MERGE statement may take some time to enter the data in TABLE_A of the destination. But while this statement is running, new data may have been created in TABLE_A and TABLE_B of source. When later the procedure starts to copy the data of TABLE_B, it also copies the new data that reference new rows of TABLE_A but that have not been copied from the previous MERGE statement of TABLE_A on destination.
In other words, the time sequence is the following:

Initial situation:
TABLE_A (source): ROW_A1
                  ROW_A2
                  ROW_A3
                  ...

TABLE_B (source): ROW_B1
                  ROW_B2 (referencing ROW_A2)
                  ROW_B3 (referencing ROW_B3)
                  ...

TABLE_A (destination): Empty
TABLE_B (destination): Empty


on destination database ---> MERGE INTO TABLE_A ... (can take a long time)

on source database ---> (while the MERGE statement is still running on the destination)
                        INSERT INTO TABLE_A (...) INSERT INTO TABLE_B (...)
                    
                        Current situation on the source:
                            TABLE_A (source): ROW_A1
                                              ROW_A2
                                              ROW_A3
                                              ...
                                              NEW_ROW_A4
                                              NEW_ROW_A5

                            TABLE_B (source): ROW_B1
                                              ROW_B2 (referencing ROW_A2)
                                              ROW_B3 (referencing ROW_B3)
                                              ...
                                              NEW_ROW_B4 (referencing NEW_ROW_A4)
                                              NEW_ROW_B5 (referencing NEW_ROW_A5)
             
on destination database ---> MERGE INTO TABLE_B ...
                        
                        Current situation on sogente:
                            TABLE_A (destination): ROW_A1
                                                   ROW_A2
                                                   ROW_A3
                                                   ...

                            TABLE_B (destination): ROW_B1
                                                   ROW_B2 (referencing ROW_A2)
                                                   ROW_B3 (referencing ROW_B3)
                                                   ...
                                                   NEW_ROW_B4 (should referencing NEW_ROW_A4)
                                                   NEW_ROW_B5 (should referencing NEW_ROW_A5)


This situation obviously generates an error when the procedure tries to reactivate the foreign keys of TABLE_B on destination.

To solve the problem, it would be enough to copy the tables following a precise order, namely starting from the children tables and then work backwards to the parent tables (it does not matter if there are records in the parent tables without children; these missing records would then be copied in the next execution). But as the table structure is quite complex, and the tables are numerous (and can be a fairly high level nesting), do "by hand" this work is almost impossible. I need to know if there is a way to retrieve the correct sequence of tables.

Any suggestions? Even suggestions about a possible other strategy to be followed are welcome ;-)

Thanks in advance.

and Chris said...

In principle your method will work. You can find all the child tables with no children themselves with a query like:

select p.table_name
from   user_constraints p
where  p.constraint_type in ('P', 'U')
-- tables with no children
and    not exists (
  select * from user_constraints c
  where  p.constraint_name = c.r_constraint_name
  and    p.owner = c.r_owner
)
-- that do have a parent
and    exists (
  select * from user_constraints gp
  where  p.table_name = gp.table_name
  and    gp.constraint_type = 'R'
);


Then use these to work "back up" the tree of FKs to find their parent with:

select prior t.table_name, p.table_name par_tab
from   user_tables t
join   user_constraints p
on     t.table_name = p.table_name 
and    p.constraint_type in ('U', 'P')
left join user_constraints c
on     t.table_name = c.table_name 
and    c.constraint_type='R'
start with t.table_name = :child_table
connect by p.constraint_name = prior c.r_constraint_name;


As you say, this could get messy. Particularly if you have circular FKs in your database!

If this is a problem for you, you can overcome it by fetching data at a particular point in time. Fortunately Oracle Database enables you to do this :)

How?

With flashback query!

Before you start your process, find the current_scn for the source database:

select current_scn from v$database;


Then add the "as of scn :start_scn" clause to your queries to get everything at the same point in time. For example:

declare
  start_scn integer;
begin
  select current_scn into start_scn from v$database;
  
  insert into t@dblink
    select * from t as of scn start_scn;
  
end;
/


Make sure your value for undo_retention is larger than the runtime (in seconds) for this process. Or Oracle may be unable to run the flashback query!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!

A reader, November 03, 2016 - 1:47 pm UTC

Both solutions are great. Thank you!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library