In this post, we will export and import the database link with data pump in Oracle 18c. In next post, we will demonstrate to prevent the passwords of database link using keystores.
First, we created the user for testing purpose.
[oracle@18c OPatch]$ sqlplus system/sys@pdb18c2 SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 30 10:12:42 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Wed May 30 2018 10:09:45 +05:30 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 SQL> create user dblink_test identified by oracle; User created. SQL> grant create table, unlimited tablespace , create session to dblink_test; Grant succeeded.
Now, we connected to the new user and created one dummy table with some data.
SQL> conn dblink_test/oracle@pdb18c2 Connected. SQL> sho user USER is "DBLINK_TEST" SQL> create table test (id number); Table created. SQL> insert into test values (1); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID ---------- 1 SQL>
Further, we connect to another user and created & tested the database link working.
SQL> conn system/sys@pdb18c2 Connected. SQL> create database link link_test connect to dblink_test identified by oracle using 'PDB18C2'; Database link created. SQL> select * from test@link_test; ID ---------- 1 SQL> create directory dp_dump as '/tmp'; Directory created. SQL> exit Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0
Next step, to take an export with the help of data pump.
[oracle@18c OPatch]$ expdp system/sys@pdb18c2 directory=dp_dump dumpfile=link.dmp Export: Release 18.0.0.0.0 - Production on Wed May 30 10:09:44 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb18c2 directory=dp_dump dumpfile=link.dmp Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/DB_LINK ----->> Export Database link Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/link.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 30 10:11:07 2018 elapsed 0 00:01:17
Now, again connected to the system user and dropped the database link.
[oracle@18c OPatch]$ sqlplus system/sys@pdb18c2 SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 30 10:12:42 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Wed May 30 2018 10:09:45 +05:30 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 SQL> drop database link link_test; Database link dropped. SQL> select * from test@link_test; select * from test@link_test * ERROR at line 1: ORA-02019: connection description for remote database not found SQL> exit Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 [oracle@18c OPatch]$
A final step to run the import which recreates the database link.
[oracle@18c OPatch]$ impdp system/sys@pdb18c2 directory=dp_dump dumpfile=link.dmp Import: Release 18.0.0.0.0 - Production on Wed May 30 10:13:53 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdb18c2 directory=dp_dump dumpfile=link.dmp Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/DB_LINK Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed May 30 10:14:01 2018 elapsed 0 00:00:06 [oracle@18c OPatch]$
Checking database link has created and working fine or not
[oracle@18c OPatch]$ sqlplus system/sys@pdb18c2 SQL*Plus: Release 18.0.0.0.0 - Production on Wed May 30 10:14:55 2018 Version 18.2.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Last Successful login time: Wed May 30 2018 10:13:53 +05:30 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.2.0.0.0 SQL> select * from test@link_test; ID ---------- 1 SQL>
In the next article, we use keystore to protect the obfuscated password for the database link.
Stay tuned for more articles on Oracle 18c
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
we previous versions also we can export/import DB links.
yes but we need to use include parameter.
when we use include=db_link we just export the db links, but in your example it looks like you exported the whole system schema just for a DB link.