--oracle中有时在库间迁移时，按schema导出时不会导出public对象，可以通过数据泵expdp和impdp的方式进行迁移
--测试如下：
环境：11gr2 redhat7 单节点
步骤：
1、创建public dblink
create public database link test
connect to r_test IDENTIFIED BY test123
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb11g)
)
)';
测试dblink连通性
select * from dual@test;
--DUMMY
--X
2、创建导出目录
CREATE DIRECTORY test AS '/oracle/dump_dir';
3、按dblink名称导出dblink,注意命令行的字符转义，如果不想用转义可以通过parfile方式导
expdp r_test/test123 DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log full=y include=DB_LINK:\"IN \(\'TEST\'\)\"
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 "R_TEST"."SYS_EXPORT_FULL_01": r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=expdp_public_dblink.log full=y include=DB_LINK:"IN ('TEST')"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Master table "R_TEST"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for R_TEST.SYS_EXPORT_FULL_01 is:
/oracle/dump_dir/expdp_public_dblink.dmp
Job "R_TEST"."SYS_EXPORT_FULL_01" successfully completed at Mon Jul 15 17:09:49 2019 elapsed 0 00:00:08
4、删除dblink
drop public database link test;
5、导入
impdp r_test/test123 DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log
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 "R_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "R_TEST"."SYS_IMPORT_FULL_01": r_test/******** DIRECTORY=test dumpfile=expdp_public_dblink.dmp LOGFILE=impdp_public_dblink.log
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Job "R_TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 15 17:12:20 2019 elapsed 0 00:00:01
6、检查导入结果，检查dblink连通性
select * from dba_db_links;
select * from dual@test;
--DUMMY
--X
--测试成功，可以通过expdp和impdp实现public dblink的迁移，这在不知道创建dblink的用户密码时比较有用。同理还有public synonym也可以用这种方式