背景是這樣，公司需要搭建系統，進行自動化測驗，在執行自動化用例時必須每條都reload 資料庫。原理是從資料庫備份user 復制資料insert到當前user下的有改動的表。現在問題出在每次做一些常規操作，大概有40-50張表有改動，reload資料庫的時候耗時太長，需要將近10分鐘（如果只有1-2張表有改動，執行只需幾秒），這50張表我也一一查看資料量最多50000條，最少幾條，平均大概10000左右。我把我寫的存盤程序貼上，希望有大神能幫我改改，優化下，提升執行效率。
create or replace procedure reloadDB as
counter number(10);
sql_statement varchar2(255);
cursor cur_test is
select table_name
from all_tables
where owner = 'TEST_DEV'
and table_name not in ('MODIFICATIONS$', 'PLAN_TABLE')
and table_name not in
(select mview_name
from all_mviews
where owner = 'TEST_DEV');
begin
/* no support for foreign key constraints! */
/* truncate and reload all tables that has been changed*/
sql_statement := 'SELECT sum(COUNT_MODIFICATIONS) FROM TEST_DEV.MODIFICATIONS$ WHERE TABLE_NAME = :tablename';
for rec_table in cur_test loop
execute immediate sql_statement
into counter
using upper(rec_table.table_name);
if counter > 0 then
execute immediate 'ALTER TABLE TEST_DEV.' || rec_table.table_name || ' DISABLE ALL TRIGGERS';
execute immediate 'TRUNCATE TABLE TEST_DEV.' || rec_table.table_name;
execute immediate 'INSERT /*+append parallel(4,a)*/ INTO TEST_DEV.' || rec_table.table_name || ' a SELECT * FROM SAVE_TEST_DEV.' || rec_table.table_name ||' NOLOGGING';
execute immediate 'ALTER TABLE TEST_DEV.' || rec_table.table_name || ' ENABLE ALL TRIGGERS';
end if;
end loop;
/* delete concurrent entrys */
execute immediate 'DELETE ' || 'TEST_DEV' ||
'.MODIFICATIONS$ where concurrent_entry = 1';
/* reset modification counter */
execute immediate 'UPDATE ' || 'TEST_DEV' ||
'.MODIFICATIONS$ SET COUNT_MODIFICATIONS = 0';
commit;
end;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/209965.html
標籤：基礎和管理
上一篇：Debugging: MISCONF Redis is configured to save RDB snapshots（譯）