Wednesday, December 7, 2022
No menu items!
HomeDatabase ManagementDatabase links within pluggable databases

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)
)
)

[[email protected] admin]$ sqlplus sys/[email protected]//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/[email protected] as sysdba
Connected.

SQL> create user kerry identified by password;

User created.

SQL> grant dba to kerry;

Grant succeeded.

SQL> conn julian/[email protected]
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 [email protected]_pump_link;
select sysdate from [email protected]_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 [email protected]_pump_link;

SYSDATE

10-NOV-22

SQL>

— and now it is time to do the import:

[[email protected] datapump]$ impdp julian/[email protected] 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

[[email protected] 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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments