在做EBS迁移的时候，我们往往要停掉JOB和重建DBlink。而这2种对象由于在常规模式下，不能用sys删除其他用户下的job或dblink，我们通常需要登录到某用户下，停掉该用户下的job和删掉该用户下的DBlink，在迁移时，对于大型系统，里面有很多用户，很多用户甚至不知道密码，无法登陆，那么上述的操作就非常不便了。
其实我们可以用下面的方法来操作：
一、对于job，我们可以用dbms_ijob来操作，这个就比较简单：
|
1 2 3 4 5 |
1.准备恢复脚本： sqlplus "/ as sysdba" set line 500 select 'exec sys.dbms_ijob.broken('||job||',false);' as text from dba_jobs where broken='N'; 记下出来的结果 |
|
1 2 3 4 5 |
2.批量停job sqlplus "/ as sysdba" set line 500 select 'exec sys.dbms_ijob.broken('||job||',true);' as text from dba_jobs where broken='N'; 执行上述的结果 |
|
1 2 |
3.迁移后恢复 执行第一步中备份的脚本即可。 |
二、对于dblink，我们利用exec immediate的存储过程来执行：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
1.检查dblink，如： select owner,db_link,username from dba_db_links; OWNER DB_LINK USERNAME ------------------------------ -------------------- ------------------------------ EPSINTF LINK1 PLANINTF SYS LINK2.WORLD PLANINTF PUBLIC PUB_LINK1 VOU_LOADER EPSINTF EPSLINK123.WORLD PLANINTF Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
在各个用户下建立用于执行的存储过程: select 'create or replace procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute(p_str in varchar2) as'||CHR(10) ||'begin'||CHR(10) ||'execute immediate p_str;'||CHR(10) ||'end;'||CHR(10) ||'/' as TEXT from dba_db_links group by owner TEXT ------------------------------------------------------------------------------- create or replace procedure EPSINTF.p_execute(p_str in varchar2) as begin execute immediate p_str; end; / create or replace procedure p_execute(p_str in varchar2) as begin execute immediate p_str; end; / create or replace procedure SYS.p_execute(p_str in varchar2) as begin execute immediate p_str; end; / 执行上述语句生成的脚本： sys@ORA9I(192.168.1.23)> create or replace procedure EPSINTF.p_execute(p_str in varchar2) as 2 begin 3 execute immediate p_str; 4 end; 5 / Procedure created. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> sys@ORA9I(192.168.1.23)> create or replace procedure p_execute(p_str in varchar2) as 2 begin 3 execute immediate p_str; 4 end; 5 / Procedure created. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> sys@ORA9I(192.168.1.23)> create or replace procedure SYS.p_execute(p_str in varchar2) as 2 begin 3 execute immediate p_str; 4 end; 5 / Procedure created. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
3.生成备份dblink的脚本（特别注意，备份dblink脚本只在9i有效，因为9i以上dblink的密码不保存在sys.link$中）： select 'exec '||DECODE(u.name, 'SYS','','PUBLIC','',u.name||'.')||'p_execute('||'''' ||'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK ' || L.NAME ||' CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||nvl(L.PASSWORD,'<Plz Input Password if Oracle version over 9i>')||'" USING '||''''''||L.HOST||''''''''||');' as TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#; TEXT -------------------------------------------------------------------------------------------------------------------------------------- exec EPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1'''); exec p_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2'''); exec p_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3'''); exec EPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4'''); Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> 记录下上述的脚本 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
4.批量删除dblink（注：所以oracle版本都适用） select 'exec '||DECODE(u.name, 'SYS','','PUBLIC','',u.name||'.')||'p_execute('||'''' ||'DROP '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK ' || L.NAME||''''||');' as TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#; TEXT -------------------------------------------------------------------------------------------- exec EPSINTF.p_execute('DROP DATABASE LINK LINK1'); exec p_execute('DROP DATABASE LINK LINK2.WORLD'); exec p_execute('DROP public DATABASE LINK PUB_LINK1'); exec EPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD'); Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> 执行上述语句即可批量删除不同用户下的dblink： sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('DROP DATABASE LINK LINK1'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec p_execute('DROP DATABASE LINK LINK2.WORLD'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec p_execute('DROP public DATABASE LINK PUB_LINK1'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('DROP DATABASE LINK EPSLINK123.WORLD'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> 检查dblink已经删除干净： sys@ORA9I(192.168.1.23)> select owner,db_link,username from dba_db_links; no rows selected Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
5.迁移后，恢复dblink： 用第3步记下的脚本恢复： sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('CREATE DATABASE LINK LINK1 CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD1" USING ''tns1'''); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec p_execute('CREATE DATABASE LINK LINK2.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD2" USING ''tns2'''); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec p_execute('CREATE public DATABASE LINK PUB_LINK1 CONNECT TO VOU_LOADER IDENTIFIED BY "MYPASSWORD3" USING ''tns3'''); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> exec EPSINTF.p_execute('CREATE DATABASE LINK EPSLINK123.WORLD CONNECT TO PLANINTF IDENTIFIED BY "MYPASSWORD4" USING ''tns4'''); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> select owner,db_link,username from dba_db_links; OWNER DB_LINK USERNAME ------------------------------ -------------------- ------------------------------ EPSINTF LINK1 PLANINTF SYS LINK2.WORLD PLANINTF PUBLIC PUB_LINK1 VOU_LOADER EPSINTF EPSLINK123.WORLD PLANINTF Elapsed: 00:00:00.00 sys@ORA9I(192.168.1.23)> |
|
1 2 3 4 5 |
6.清扫战场，删除用来批量清理dblink的存储过程。 select 'drop procedure '||DECODE(owner,'PUBLIC','',owner||'.')||'p_execute;' as TEXT from dba_objects where object_name=upper('p_execute') and object_type='PROCEDURE' group by owner; |