Background so that the company needs to build system, automated tests, when performing automated cases must each reload the database, the principle of data from a database backup copy user insert to the current user has changes under the table, now the problem is every time to do some regular operations, there are about 40-50 list have change, reload the database takes too long, need to almost 10 minutes (if there are changes only 1-2 tables, perform only a few seconds), the 50 table I also view data volume up to 50000 bar, at least a few, an average of about 10000 or so, as I write my storage process, hope to have a great god can help me change, optimize, improve execution efficiency,

The create or replace procedure reloadDB as
Counter number (10);
Sql_statement varchar2 (255);
Cursor cur_test is
The select table_name
The from all_tables
Where the owner='TEST_DEV'
MODIFICATIONS and table_name not in (' $', 'PLAN_TABLE')
And table_name not in
(select mview_name
The from all_mviews
Where the owner='TEST_DEV');
The begin
/* no support for foreign key constraints. */

/* truncate and reload all tables that has had changed */
Sql_statement:='SELECT sum (COUNT_MODIFICATIONS) FROM TEST_DEV. MODIFICATIONS $WHERE TABLE_NAME=: tablename';

For rec_table cur_test in loop
The execute immediate sql_statement
Into counter
Using upper (rec_table table_name);

If the counter & gt; 0 then
The execute immediate 'ALTER TABLE TEST_DEV.' | | rec_table. Table_name | | 'DISABLE ALL TRIGGERS;

The execute immediate 'TRUNCATE TABLE TEST_DEV. | | rec_table. Table_name;

The execute immediate 'INSERT/* + append the parallel (4 a) */INTO TEST_DEV.' | | rec_table. Table_name | | 'a SELECT * FROM SAVE_TEST_DEV.' | | rec_table. Table_name | | 'NOLOGGING';

The execute immediate 'ALTER TABLE TEST_DEV.' | | rec_table. Table_name | | 'ENABLE ALL TRIGGERS;
end if;
end loop;

/* delete concurrent entrys */
The execute immediate 'DELETE' | | 'TEST_DEV' | |
'$where MODIFICATIONS concurrent_entry=1';
Reset/* */modification counter
The execute immediate 'UPDATE' | | 'TEST_DEV' | |
'MODIFICATIONS $SET COUNT_MODIFICATIONS=0';

commit;
end;