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.

List of the Oracle Database 23c New Features

On October 18th 2022, Oracle announced the new long term release of 23c Beta.

Here is a compiled list of the new features I am aware of:

OLTP and Core DB:

Accelerate SecureFiles LOB Write Performance
Automatic SecureFiles Shrink
Automatic Transaction Abort
Escrow Column Concurrency Control
Fast Ingest (Memoptimize for Write) Enhancements
Increased Column Limit to 4k
Managing Flashback Database Logs Outside the Fast Recovery Area
Remove One-Touch Restrictions after Parallel DML
Annotations – Define Metadata for Database Objects
SELECT Without the FROM Clause
Usage of Column Alias in GROUP BY and HAVING
Table Value Constructor – Group Multiple Rows of Data in a Single DML or SELECT statement
Better Error Messages to Explain why a Statement Failed to Execute
New Developer Role: dbms_developer_admin.grant_privs(‘JULIAN’);
Schema Level Privileges
RUR’s are transitioning to MRPs (available on Linux x86-64)

Application Development:

Aggregation over INTERVAL Data Types
Asynchronous Programming
Blockchain Table Enhancements
DEFAULT ON NULL for UPDATE Statements
Direct Joins for UPDATE and DELETE Statements
GROUP BY Column Alias or Position
Introduction to Javascript Modules and MLE Environments MLE – Module Calls
New Database Role for Application Developers
OJVM Web Services Callout Enhancement
OJVM Allow HTTP and TCP Access While Disabling Other OS Calls
Oracle Text Indexes with Automatic Maintenance
Sagas for Microservices
SQL Domains
SQL Support for Boolean Datatype
SQL UPDATE RETURN Clause Enhancements
Table Value Constructor
Transparent Application Continuity
Transportable Binary XML
Ubiquitous Search With DBMS_SEARCH Packages
Unicode IVS (Ideographic Variation Sequence) Support

Compression:

Improve Performance and Disk Utilization for Hybrid Columnar Compression
Index-Organized Tables (IOTs) Advanced Low Compression

Data Guard:

Per-PDB Data Guard Integration Enhancements

Event Processing:

Advanced Queuing and Transactional Event Queues Enhancements
OKafka (Oracle’s Kafka implementation)
Prometheus/Grafana Observability for Oracle Database

In-Memory:

Automatic In-Memory enhancements for improving column store performance

Java:

JDBC Enhancements to Transparent Application Continuity
JDBC Support for Native BOOLEAN Datatype
JDBC Support for OAuth2.0 for DB Authentication and Azure AD Integration
JDBC Support for Radius Enhancements (Challenge Response Mode a.k.a. Two Factor Authentication)
JDBC Support for Self-Driven Diagnosability
JDBC-Thin support for longer passwords
UCP Asynchronous Extension

JSON:

JSON-Relational Duality View
JSON SCHEMA

RAC:

Local Rolling Patching
Oracle RAC on Kubernetes
Sequence Optimizations in Oracle RAC
Simplified Database Deployment
Single-Server Rolling Patching
Smart Connection Rebalance

Security:

Ability to Audit Object Actions at the Column Level for Tables and Views
Enhancements to RADIUS Configuration
Increased Oracle Database Password Length: 1024 Byte Password
Schema Privileges to Simplify Access Control
TLS 1.3

Sharding:

JDBC Support for Split Partition Set and Directory based Sharding
New Directory-Based Sharding Method
RAFT Replication
UCP Support for XA Transactions with Oracle Database Sharding

Spatial and Graph:

Native Representation of Graphs in Oracle Database
Spatial: 3D Models and Analytics
Spatial: Spatial Studio UI Support for Point Cloud Features
Support for the ISO/IEC SQL Property Graph Queries (SQL/PGQ) Standard
Use JSON Collections as a Graph Data Source

And here are few links that give you additional information on what will be coming with the new release:

Oracle Database 23c: New feature highlights by Lucas Jellema

Oracle 23c: New features by René Nyffenegger

23c Twitter Summary by Philipp Salvisberg

P.S. Just found out that road cyclists are moving away from 23C and that there are products called Merkur 23C Safety Razor and Glock Model 23C.