如题：不知密码的情况下如何重建db_link？

处理过程

1.导出dmp

[oracle@lncs dmp]$ expdp \'/ as sysdba\' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 15:39:33 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /oracle/dmp/d.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 15:40:10 2023 elapsed 0 00:00:36

检查导出的db_link脚本是否符合预期？

[oracle@lncs dmp]$ impdp \'/ as sysdba\' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link sqlfile=d.sql full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 15:40:27 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link sqlfile=d.sql full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Dec 5 15:40:30 2023 elapsed 0 00:00:03

[oracle@lncs dmp]$ more d.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
-- CONNECT GISTAR
CREATE DATABASE LINK "LNDB57"
   CONNECT TO "GISTAR" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
-- CONNECT SYS
CREATE PUBLIC DATABASE LINK "SMSDB"
   CONNECT TO "UOP_OCS" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
CREATE PUBLIC DATABASE LINK "ZYGL"
   CONNECT TO "ZYGL" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
-- CONNECT QUEST
CREATE DATABASE LINK "QUEST_SOO_SCZY1"
   USING 'sczy1';
CREATE DATABASE LINK "QUEST_SOO_SCZY2"
   USING 'sczy2';

2.删除dblink

注意：不是自己的dblink是不能删除的，所以需要这样：

1)sys用户下创建删除的存储过程：

create or replace procedure drop_dblink(schemaName varchar2, dbLink varchar2 ) is
            plsql   varchar2(1000);
            cur     number;
            uid     number;
            rc      number;
    begin
            select   u.user_id into uid from dba_users u
           where   u.username = schemaName;
             plsql := 'drop database link "'||dbLink||'"';
             cur := SYS.DBMS_SYS_SQL.open_cursor;
             SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => plsql,
                   language_flag => DBMS_SQL.native,
                   userID => uid
          );
             rc := SYS.DBMS_SYS_SQL.execute(cur);
             SYS.DBMS_SYS_SQL.close_cursor(cur);
   end;
/

2)执行删除操作：

SQL> exec drop_dblink('QUEST','QUEST_SOO_SCZY1');

PL/SQL procedure successfully completed.

3)删掉存储过程(临时用的过程记得安全还原)：

drop procedure drop_dblink;

3.导入dblink

[oracle@lncs dmp]$ impdp \'/ as sysdba\' directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 15:53:00 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=ORADMP cluster=n dumpfile=d.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-31684: Object type DB_LINK:"GISTAR"."LNDB57" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."SMSDB" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."ZYGL" already exists
Job "SYS"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Dec 5 15:53:04 2023 elapsed 0 00:00:02

当然导入的时候不一定用sys用户，有权限的用户也是可以的，比如impdp jyc/jyc这样

检查ok:

提示：活学活用，不用sys用户导入导出也是可以的，测试如下：

[oracle@lncs dmp]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 16:14:08 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_FULL_01":  jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d.log parallel=1 content=metadata_only include=db_link full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "JYC"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_FULL_01 is:
  /oracle/dmp/d2.dmp
Job "JYC"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 16:14:15 2023 elapsed 0 00:00:06

[oracle@lncs dmp]$ expdp jyc/jyc directory=ORADMP cluster=n dumpfile=d3.dmp logfile=d.log parallel=1 include=db_link full=y

Export: Release 11.2.0.4.0 - Production on Tue Dec 5 16:14:29 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_FULL_01":  jyc/******** directory=ORADMP cluster=n dumpfile=d3.dmp logfile=d.log parallel=1 include=db_link full=y 
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "JYC"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_FULL_01 is:
  /oracle/dmp/d3.dmp
Job "JYC"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 5 16:14:37 2023 elapsed 0 00:00:08

[oracle@lncs dmp]$ ll d*.dmp
-rw-r----- 1 oracle oinstall 233472 Dec  5 16:14 d2.dmp
-rw-r----- 1 oracle oinstall 233472 Dec  5 16:14 d3.dmp
-rw-r----- 1 oracle oinstall 233472 Dec  5 15:40 d.dmp
[oracle@lncs dmp]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp sqlfile=d2.sql logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 16:15:19 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "JYC"."SYS_SQL_FILE_FULL_01":  jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp sqlfile=d2.sql logfile=d1.log parallel=1 content=metadata_only include=db_link full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "JYC"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Dec 5 16:15:22 2023 elapsed 0 00:00:01

[oracle@lncs dmp]$ more d2.sql
-- CONNECT JYC
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
-- CONNECT GISTAR
CREATE DATABASE LINK "LNDB57"
   CONNECT TO "GISTAR" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
-- CONNECT JYC
CREATE PUBLIC DATABASE LINK "SMSDB"
   CONNECT TO "UOP_OCS" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
CREATE PUBLIC DATABASE LINK "ZYGL"
   CONNECT TO "ZYGL" IDENTIFIED BY VALUES ':1'
   USING 'lndb57';
-- CONNECT QUEST
CREATE DATABASE LINK "QUEST_SOO_SCZY1"
   USING 'sczy1';
CREATE DATABASE LINK "QUEST_SOO_SCZY2"
   USING 'sczy2';
[oracle@lncs dmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 5 16:15:48 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop PUBLIC DATABASE LINK "SMSDB";

Database link dropped.

SQL> exec drop_dblink('QUEST','QUEST_SOO_SCZY1');

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lncs dmp]$ impdp jyc/jyc directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 5 16:16:22 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_FULL_01":  jyc/******** directory=ORADMP cluster=n dumpfile=d2.dmp logfile=d1.log parallel=1 content=metadata_only include=db_link full=y 
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
ORA-31684: Object type DB_LINK:"GISTAR"."LNDB57" already exists
ORA-31684: Object type DB_LINK:"PUBLIC"."ZYGL" already exists
ORA-31684: Object type DB_LINK:"QUEST"."QUEST_SOO_SCZY2" already exists
Job "JYC"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Tue Dec 5 16:16:27 2023 elapsed 0 00:00:04