Commit for every 'n' records in 'insert... select from' .
Our application has about 3000 tables. This application has a concept called 'company'. Each application company can be related to a physical site of the company. For example, if we have two sites one at London and one at Florida then we have two sets of companies. Hence our application would have 3000 + 3000 = 6000 tables.
Each table name will have the company attached at the end. like 'payroll422', 'payroll522' represent same tables pointing to different sets of companies 422, 522. If a user in London enter data it goes into 'paroll422' and if a user in Florida enters data it goes into 'payroll522' table.
Once in very month I have to populate the data of this companies into 'test' companies for the purpose of testing the new pathes of application. The test database is in a different machine, which is having very less hp (memory and CPU is less). I can create a new set of test companies say 423, 523 in test environment. My application easily creates the 3000 tables and indexes needed for new companies.
Now my job is to populate the data from production companies 422, 522 into 423, 523. I have created database_links between the PROD and TEST database. I have created a small script for replication/copy of data as follows
stmt_str := 'insert into ' || dst_tab_name || ' select * from ' || tab_name || '@prod' ;
execute immediate stmt_str ;
This script is working OK. It populates data from PROD tables in to TEST tables with one exception. Some of your tables
like 'general_leger422 has abut 3+ million records. The above statement fails (basically it fills out the entire redolog tablespace) if the number of records are huge. It is doing this because I am commiting just once for entire table. So for tables with 3+ million records, I am not commiting untill all records (3+ million) are filled. Redolog is filled and it hangs.
So my question is , is it possible to commit for every 1000 records in the above 'insert into ... select from' statemtn. If so how do I specify this.
Or if there is any other way to accomplish this replication/copy please suggest.
Click Here to Expand Forum to Full Width