Database links within pluggable databases

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.