In this blog we are going to learn about the export and import in datapump using database link.
DESCRIPTION:
A database link , is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.
After you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending at DB_LINK name to the table or view name. You can query a table or view on the other database with the select statement. You can also access remote tables and views using any insert or update or delete statement.
I have mentioned below example operation for database link concept in export and import data pump.
DATABASE_LINK PARAMETER
[oracle@doyensys ~]$ sqlplus shan1/dba@doyendbSQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 11 09:03:03 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> create user dblink_trainee identified by oracle;
User created.
SQL> grant create table, unlimited tablespace , create session to dblink_trainee;
Grant succeeded.
SQL> conn dblink_trainee/oracle;
Connected.
SQL> create table test (id number);
Table created.
SQL> conn shan1/dba@doyendb
Connected.
SQL> create database link db_linker connect to dblink_trainee identified by oracle using ‘doyendb’;
Database link created.
SQL> select * from test@db_linker;
ID
———-
1
2
3
4
5
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@doyensys ~]$ expdp shan1/dba@doyendb directory=tablez dumpfile=linker.dmpExport: Release 12.2.0.1.0 – Production on Wed Nov 11 09:07:42 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Starting “shan1”.”SYS_EXPORT_SCHEMA_01″: shan1/********@doyendb directory=tablez dumpfile=linker.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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
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 “shan1”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for shan1.SYS_EXPORT_SCHEMA_01 is:
/u01/export/linker.dmp
Job “shan1”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Wed Nov 11 09:08:20 2020 elapsed 0 00:00:38
[oracle@doyensys ~]$ sqlplus shan1/dba@doyendbSQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 11 10:08:25 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> drop database link db_linker;
Database link dropped.
SQL> select * from test@db_linker;
select * from test@db_linker
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@doyensys ~]$ impdp shan1/dba@doyendb directory=tablez dumpfile=linker.dmpImport: Release 12.2.0.1.0 – Production on Wed Nov 11 10:09:36 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
Master table “shan1”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “shan1”.”SYS_IMPORT_FULL_01″: shan1/********@doyendb directory=tablez dumpfile=linker.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”shan1″ already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Job “shan1”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Wed Nov 11 10:09:40 2020 elapsed 0 00:00:03
[oracle@doyensys ~]$ sqlplus shan1/dba@doyendbSQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 11 10:09:51 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select * from test@db_linker;
ID
———-
1
2
3
4
5
