Import all database links using dbms_datapump

database-linkdbms-datapumporacle

Source DB version: 11.2.0.3
Destination DB version: 12.1.0.2

I'm attempting to import all database links from one database to another using dbms_datapump. The code is running on the above destination DB, and pulling the DB Links from the above Source DB via a Database Link.

I've used dbms_datapump.metadata_filter in the past to only import certain objects (like functions or procedures). However, the following returns ORA-39001: invalid argument value:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINK'')');

Therefore, I tried the plural version which also returned ORA-39001:

dbms_datapump.metadata_filter(h1,'INCLUDE_PATH_EXPR','IN (''DATABASE LINKS'')');

I know I must be missing something basic here, but searching on this issue has been annoying since search results with "dbms_datapump" and "database link" in the same search has only returned results with how to import over a Database link, and not how to import the Database Links directly.

I used to be able to import the Database Links directly using:

select DBMS_LOB.SUBSTR@dblinkimp(DBMS_METADATA.GET_DDL@dblinkimp
('DB_LINK', '[DBlinkName]', '[SchemaName]')) from dual@dblinkimp;

Then I would run the output from the above statement using execute immediate, but this is no longer a valid option starting in 11.2.0.4 (See Metalink Doc ID 1905221.1).

Best Answer

It is not DATABASE LINK or DATABASE LINKS, it is DB_LINK. You can find the valid values in DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS.