Load data simultaneously in 10 table

Question

My Requirement : I have 72 oracle tables which have data inside it. Few tables have billion and million of records. I need to copy data from 72 tables to another 72 tables as it is. What is best way to do it ? I want to copy the data at one shot in 5-6 tables so that it should not go and load data one by one in-order to save the time. Not sure how should i achieve it in oracle.

Answer 1

If both the source and target tables are present in the Database schema then you can follow below approach:

a.) Create a configuration table with the metadata information to define the rules and create the custom insert statement for the tables. (Optional)

b.) Write a procedure with the logic to move the data from one table to another.

c.) Write a procedure with DBMS_SCHEDULER in a loop to schedule the call the copy procedure defined in step b). The scheduler will take case of opening and closing a session.