Oracle 18c Datapump-Database Link
Description:-
In this article we are going to see the Oracle 18c Datapump-Database Link
Let’s start the Demo:
Pre-steps:-We create new schema for testing.
[oracle@testdb ~]$ sqlplus sys/Chennai#123@pdb1 as sysdba SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 21:10:25 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Tue Jul 10 2018 21:10:09 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> show user USER is "SYS" SQL> create user hari_link identified by oracle; User created. SQL> grant connect,resource,unlimited tablespace to hari_link; Grant succeeded.
Step1:- login in to hari_link schema and create table for validation
[oracle@testdb ~]$ sqlplus hari_link/oracle@pdb1 SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 21:14:18 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> create table chennai(no number,sal number); Table created. SQL> insert into chennai values(1,100); 1 row created. SQL> insert into chennai values(2,200); 1 row created. SQL> insert into chennai values(3,300); 1 row created. SQL> insert into chennai values(4,400); 1 row created. SQL> insert into chennai values(5,500); 1 row created. SQL> commit; Commit complete. SQL> select count(*) from chennai; COUNT(*) ---------- 5
Step2:-Login into system schema and create the DB link
[oracle@testdb ~]$ sqlplus system/Chennai#123@pdb1 SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 21:18:24 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Tue Jul 03 2018 03:59:11 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> create database link dblink18c connect to hari_link identified by oracle using 'PDB1'; Database link created. SQL> select count(*) from chennai@dblink18c; COUNT(*) ---------- 5
Step3:-Now,we are going to take export
[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=test_dir dumpfile=hari_link.dmp Export: Release 18.0.0.0.0 - Production on Tue Jul 10 21:23:02 2018 Version 18.1.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/********@pdb1 directory=test_dir dumpfile=hari_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 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: /u01/app/oracle/datapump/hari_link.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jul 10 21:24:00 2018 elapsed 0 00:00:57
Step4:-Drop the DBlink from system schema
[oracle@testdb ~]$ sqlplus system/Chennai#123@pdb1 SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 21:26:42 2018 Version 18.1.0.0.0 Copyright (c) 1982, 2017, Oracle. All rights reserved. Last Successful login time: Tue Jul 10 2018 21:23:02 +00:00 Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.1.0.0.0 SQL> drop database link dblink18c; Database link dropped. SQL> select count(*) from chennai@dblink18c; select count(*) from chennai@dblink18c * ERROR at line 1: ORA-02019: connection description for remote database not found
Step5:-Recreate dblink using dumpfile and check
[oracle@testdb ~]$ impdp system/Chennai#123@pdb1 directory=test_dir dumpfile=hari_link.dmp
Import: Release 18.0.0.0.0 - Production on Tue Jul 10 21:29:30 2018
Version 18.1.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/********@pdb1 directory=test_dir dumpfile=hari_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 Tue Jul 10 21:29:38 2018 elapsed 0 00:00:06
Step6:-Cross check the DBLINK is working fine
[oracle@testdb ~]$ sqlplus system/Chennai#123@pdb1
SQL*Plus: Release 18.0.0.0.0 Production on Tue Jul 10 21:30:59 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Last Successful login time: Tue Jul 10 2018 21:29:31 +00:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> select count(*) from chennai@dblink18c;
COUNT(*)
----------
5
Catch Me On:- Hariprasath Rajaram
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
