DATABASE_LINK PARAMETER
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. You can query a table or view on the other database with the SELECT statement.
Step 1:
Connecting to the database
[oracle@doyensys ~]$ sqlplus scott/scotty@doyendb
SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 13 20: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
Step 2:
Creating user and Grant permissions
SQL> create user dblink_test3 identified by oracle;
User created.
SQL> grant create table, unlimited tablespace , create session to dblink_test3;
Grant succeeded.
Step 3:
Connecting to the user and create a sample table
SQL> conn dblink_test3/oracle;
Connected.
SQL> create table test (id number);
Table created.
Step 4:
Creating Database link
SQL> conn scott/scotty@doyendb
Connected.
SQL> create database link db_linker connect to dblink_test3 identified by oracle using ‘doyendb’;
Database link created.
Step 5:
Viewing the table using table name with the database link name
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
Step 6:
Exporting the schema along with the database link
[oracle@doyensys ~]$ expdp scott/scotty@doyendb directory=tablez dumpfile=linker.dmp
Export: Release 12.2.0.1.0 – Production on Tue Oct 13 20: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 “SCOTT”.”SYS_EXPORT_SCHEMA_01″: scott/********@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 “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/u01/export/linker.dmp
Job “SCOTT”.”SYS_EXPORT_SCHEMA_01″ successfully completed at Wed Jan 13 20:08:20 2021 elapsed 0 00:00:38
Step 7:
Connecting the database and trying to drop the created DB link
[oracle@doyensys ~]$ sqlplus scott/scotty@doyendb
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan13 20:08:25 2021
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
Step 8:
Import using the dump file “linker.dmp”
[oracle@doyensys ~]$ impdp scott/scotty@doyendb directory=tablez dumpfile=linker.dmp
Import: Release 12.2.0.1.0 – Production on Wed Jan 13 20:09:36 2021
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 “SCOTT”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_FULL_01″: scott/********@doyendb directory=tablez dumpfile=linker.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”SCOTT” 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 “SCOTT”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Wed Jan 13 20:09:40 2021 elapsed 0 00:00:03
Step 9:
[oracle@doyensys ~]$ sqlplus scott/scotty@doyendb
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 13 20:09:51 2021
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
