Sometimes, you might need a database link between 2 schemas within the same (pluggable) database.
Why? There are several reasons. Here is one: may be you want to refresh one schema from another using Data Pump via network link? This is very common practice for development databases. I will show in this blog how this can be done step-by-step.
Here is what is needed before you can start: 2 tnsnames.ora entries pointing to the same service name, just with different names. I will need a logical directory, say schema_dir, although I will not place anything there.
I am doing the schema cloning within the same PDB in a 21c CDB, although nothing is preventing us from doing the same in 12c, 18c or 19c.
The schema julian will be duplicated into another schema called kerry:
julian1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB1.laika2.laika.oraclevcn.com)
)
)
julian2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB1.laika2.laika.oraclevcn.com)
)
)
[oracle@aeg admin]$ sqlplus sys/password@//localhost:1521/PDB1.laika2.laika.oraclevcn.com as sysdba
SQL*Plus: Release 21.0.0.0.0 – Production on Thu Nov 10 10:48:47 2022
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 21c EE High Perf Release 21.0.0.0.0 – Production
Version 21.1.0.0.0
SQL> CREATE OR REPLACE DIRECTORY schema_dir AS ‘/u01/app/oracle/homes/OraDB21Home1/datapump’;
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY schema_dir TO julian;
Grant succeeded.
SQL> conn julian/password@julian1 as sysdba
Connected.
SQL> create user kerry identified by password;
User created.
SQL> grant dba to kerry;
Grant succeeded.
SQL> conn julian/password@julian1
Connected.
— Now, let us create the database link:
SQL> create database link data_pump_link connect to kerry identified by password using ‘julian2’;
Database link created.
SQL> select sysdate from dual@data_pump_link;
select sysdate from dual@data_pump_link
*
ERROR at line 1:
ORA-02085: database link DATA_PUMP_LINK.LAIKA2.LAIKA.ORACLEVCN.COM connects to
PDB1.LAIKA2.LAIKA.ORACLEVCN.COM
SQL> show parameter global
NAME TYPE VALUE
allow_global_dblinks boolean FALSE
global_names boolean TRUE
global_txn_processes integer 1
SQL> alter system set global_names=false scope=memory;
System altered.
SQL> select sysdate from dual@data_pump_link;
SYSDATE
10-NOV-22
SQL>
— and now it is time to do the import:
[oracle@aeg datapump]$ impdp julian/password@julian1 DIRECTORY=schema_dir NETWORK_LINK=data_pump_link schemas=julian remap_schema=julian:kerry
Import: Release 21.0.0.0.0 – Production on Thu Nov 10 11:12:22 2022
Version 21.1.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c EE High Perf Release 21.0.0.0.0 – Production
Starting “JULIAN”.”SYS_IMPORT_SCHEMA_01″: julian/@julian1 DIRECTORY=schema_dir NETWORK_LINK=data_pump_link schemas=julian remap_schema=julian:kerry
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.162 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”KERRY” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ORACLE_OBJECT_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39129: Object type TABLE: “JULIAN”.”SYS_IMPORT_SCHEMA_01″ not imported. Name conflicts with the master table
. . imported “KERRY”.”SALES” 37790720 rows
. . imported “KERRY”.”BLOGS” 73991 rows
. .
. .
. . imported “KERRY”.”RDBMS_BRANDS” 12 rows
. . imported “KERRY”.”SHARDINGADVISOR_ECPREDS” 1 rows
. . imported “KERRY”.”SHARDINGADVISOR_PREDS” 4 rows
. . imported “KERRY”.”SHARDINGADVISOR_CONFIGDETAILS” 0 rows
. . imported “KERRY”.”SHARDINGADVISOR_CONFIGURATIONS” 0 rows
. . imported “KERRY”.”SHARDINGADVISOR_IMPORTANT_TABS” 0 rows
. . imported “KERRY”.”SHARDINGADVISOR_QUERYTYPES” 0 rows
. . imported “KERRY”.”USER_TABLE” 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39083: Object type COMMENT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
COMMENT ON TABLE “KERRY”.”SYS_IMPORT_SCHEMA_01″ IS ‘Data Pump Master Table IMPORT SCHEMA ‘
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:”KERRY”.”SYS_MTABLE_00001374A_IND_3″ failed to create with error:
ORA-00942: table or view does not exist
…
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_ZONEMAP
Job “JULIAN”.”SYS_IMPORT_SCHEMA_01″ completed with 9 error(s) at Thu Nov 10 11:34:49 2022 elapsed 0 00:22:22
[oracle@aeg datapump]$
Note the errors related to the master table SYS_IMPORT_SCHEMA_01 which Data Pump uses for processing exports and imports. Of course, being in the same pluggable database, there is a conflict in replacing the Master Table and hence these can be totally ignored.
Note also the importance of GLOBAL_NAMES when creating the DB link.
Read MoreJulian Dontcheff’s Database Blog
