由于10.2.0.1至10.2.0.4上的BUG
4766344的影响 ,删除私有同义词非常缓慢(打补丁后就非常快),采用以下方式,通过自动作业来并行执行删除操作。
1.先创建存储过程
Create Or Replace Procedure Zl_Private_Snynoyms_DropCreate Or Replace Procedure Zl_Private_Snynoyms_Drop
(
n_Parallel_In Number,
n_Process_In Number
) As
n_First Number := 1;
Begin
For R In (Select *
From (Select Mod(Rownum, n_Parallel_In) As Rn, 'DROP SYNONYM ' || Owner || '.' || Synonym_Name || '' Sql_Text
From Dba_Synonyms
Where Owner In (Select 用户名 From 上机人员表) And Table_Owner In (Select 所有者 From zlSystems)
Order By Owner)
Where Rn = (n_Process_In - 1)) Loop
If n_First = 1 Then
n_First := 0;
--首次执行时等10秒,以便让其他并行作业执行完查询
Dbms_Lock.Sleep(10);
End If;
Begin
Execute Immediate r.Sql_Text;
Exception
When Others Then
Null;
End;
End Loop;
End Zl_Private_Snynoyms_Drop;
/
2.创建自动作业来执行存储过程
--set serverout on;
Declare
n_Parallel Number(2) := 7; --并行度
n_Job Number(5);
Begin
Dbms_Output.Put_Line('了解作业执行情况,请查询视图dba_jobs,Dba_Jobs_Running,执行完后会自动删除,如果因意外而没有删除,请执行下面的脚本。');
For I In 1 .. n_Parallel Loop
Dbms_Job.Submit(n_Job, 'Zl_Private_Snynoyms_Drop(' || n_Parallel || ',' || I || ');');
Commit;
Dbms_Output.Put_Line('exec Dbms_Job.remove(' || n_Job || ');');
End Loop;
End;
/
3.等执行完成后,删除自动作业
1.先创建存储过程
Create Or Replace Procedure Zl_Private_Snynoyms_DropCreate Or Replace Procedure Zl_Private_Snynoyms_Drop
(
n_Parallel_In Number,
n_Process_In Number
) As
n_First Number := 1;
Begin
For R In (Select *
From (Select Mod(Rownum, n_Parallel_In) As Rn, 'DROP SYNONYM ' || Owner || '.' || Synonym_Name || '' Sql_Text
From Dba_Synonyms
Where Owner In (Select 用户名 From 上机人员表) And Table_Owner In (Select 所有者 From zlSystems)
Order By Owner)
Where Rn = (n_Process_In - 1)) Loop
If n_First = 1 Then
n_First := 0;
--首次执行时等10秒,以便让其他并行作业执行完查询
Dbms_Lock.Sleep(10);
End If;
Begin
Execute Immediate r.Sql_Text;
Exception
When Others Then
Null;
End;
End Loop;
End Zl_Private_Snynoyms_Drop;
/
2.创建自动作业来执行存储过程
--set serverout on;
Declare
n_Parallel Number(2) := 7; --并行度
n_Job Number(5);
Begin
Dbms_Output.Put_Line('了解作业执行情况,请查询视图dba_jobs,Dba_Jobs_Running,执行完后会自动删除,如果因意外而没有删除,请执行下面的脚本。');
For I In 1 .. n_Parallel Loop
Dbms_Job.Submit(n_Job, 'Zl_Private_Snynoyms_Drop(' || n_Parallel || ',' || I || ');');
Commit;
Dbms_Output.Put_Line('exec Dbms_Job.remove(' || n_Job || ');');
End Loop;
End;
/
3.等执行完成后,删除自动作业
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/117319/viewspace-1839901/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/117319/viewspace-1839901/