sys用户下为其他用户的创建私有db link的案例

记录一下scheduler job执行失败，而不知到job拥有者密码的情况下，如何重建私有db link的案例

1.查询job执行情况

1.1查看执行失败的job概况

select * from dba_scheduler_job_log where status<>'SUCCEEDED' 
LOG_ID        LOG_DATE                            OWNER            JOB_NAME         STATUS OPERATION
305351	22-5月 -23 06.10.44.119197000 下午 +07:00	EWF	Migration_Update_SingStatus	FAILED	RUN
305352	22-5月 -23 06.25.44.108925000 下午 +07:00	EWF	Migration_Update_SingStatus	FAILED	RUN
305354	22-5月 -23 06.40.44.101936000 下午 +07:00	EWF	Migration_Update_SingStatus	FAILED	RUN
305356	22-5月 -23 06.55.44.103044000 下午 +07:00	EWF	Migration_Update_SingStatus	FAILED	RUN		

1.2.进一步查看

select ADDITIONAL_INFO from dba_scheduler_job_RUN_DETAILS where LOG_ID IN (select LOG_ID from dba_scheduler_job_log where status<>'SUCCEEDED')
ADDITIONAL_INFO
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :
"
"ORA-06550: line ORA-06550: line 4, column 1:
PLS-00905: object EWF.PRO_1540200_03 is invalid
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
, column :

2.确认根因

以上输出，看上去有一个对象：EWF.PRO_1540200_03失效了，重新编译一下

ALTER PROCEDURE EWF.PRO_1540200_03 COMPILE
ORA-04052: error occurred when looking up remote object FM.SUBMIT_REC@ORCLDB01IY.FM
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred
04052. 00000 -  "error occurred when looking up remote object %s%s%s%s%s"
*Cause:    An error occurred when trying to look up a remote object.
*Action:   Fix the error.  Make sure the remote database system has run
           CATRPC.SQL to create necessary views used for querying or looking up
           objects stored in the database.

原来是dblink肇事，看看这个link指向哪里

SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='ORCLDB01IY.FM'
OWNER   DB_LINK     USERNAME                       HOST       
EWF	  ORCLDB01IY.FM	  FM	(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.20.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))	

发现db link指向的目标主机ip不对，原来目标主机的ip已经修改为172.19.5.217,而DB LINK没有修改
这个db link是属于用户ewf下的私有Link,而我不知道ewf的密码，于是想到使用current_schema试试为ewf重现创建db_link:
先获取原来dblink的ddl

SELECT DBMS_METADATA.GET_DDL('DB_LINK','ORCLDB01IY.FM','EWF') AS DDL_SQL FROM DUAL
                                  DDL_SQL
  CREATE DATABASE LINK "ORCLDB01IY.FM"
   CONNECT TO "FM" IDENTIFIED BY VALUES '05EEF5EF712A07D0C698D886FB43958BAF'
   USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.20.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))'

3.重建DB LINK

3.1使用current_schema方式

切换current_schema,并drop原来的dblink

alter session set current_schema=ewf;
Session 已更改.
 drop database link "EWF"."ORCLDB01IY.FM"
 SQL 錯誤: ORA-02024: database link not found
02024. 00000 -  "database link not found"
*Cause:    Database link to be dropped is not found in dictionary
*Action:   Correct the database link nam

不管他，尝试创建db link

 CREATE DATABASE LINK ORCLDB01IY.FM
   CONNECT TO "FM" IDENTIFIED BY VALUES '05EEF5EF712A07D0C698D886FB43958BAF'
   USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))'
Database link ORCLDB01IY.FM 已建立.  

确认一下

SELECT * FROM DBA_DB_LINKS WHERE DB_LINK='ORCLDB01IY.FM'
OWNER     DB_LINK       USERNAME
EWF	    ORCLDB01IY.FM	  FM
SYSTEM	ORCLDB01IY.FM	  FM

新的dblink却被创建在system用户，看来current_schema不支持私有db link的创建

3.2使用procedure方式

最后使用procedure的方式：
procedure:

CREATE OR REPLACE PROCEDURE ewf.crdb_link
IS
BEGIN
EXECUTE IMMEDIATE 'drop database link ORCLDB01IY.FM';
EXECUTE IMMEDIATE   'CREATE DATABASE LINK ORCLDB01IY.FM
   CONNECT TO FM IDENTIFIED BY VALUES ''05EEF5EF712A07D0C698D886FB43958BAF''
   USING ''(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))''';
end;

drop 掉上面创建于system下的db liink,再执行这个procedure

SHOW USER;
USER 為 "SYS"
drop database link ORCLDB01IY.FM
Database link ORCLDB01IY.FM 已刪除.
exec ewf.crdb_link
Procedure CRDB_LINK 已編譯
已順利完成 PL/SQL 程序.

再次验证

EWF	ORCLDB01IY.FM	FM	(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.19.5.217)(PORT=1521)))(CONNECT_DATA=(SID=PCNORCL)))

可以看到，db_link已经顺利重建

4.重新编译失效的对象，并手动执行job

ALTER PROCEDURE EWF.PRO_1540200_03 COMPILE;
Procedure EWF.PRO_1540200_03 已更改.
EXEC dbms_scheduler.run_job('EWF."Migration_Update_SingStatus"');
已順利完成 PL/SQL 程序.