Oracle introduced multitenant architecture in 12c. The purpose of multitenant is consolidation. Instead of having multiple databases on a server you can now create one consolidated database called the CDB. The CDB can contain multiple pluggable databases (PDBs). To an application, a PDB would appear as if it is a separate database. But behind the scenes there is one instance, one set of background processes, one shared pool, and so on. This architecture is based on data sharing between CDBs and PDBs. With multitenant, Oracle designed the architecture so that the dictionary information exists in the CDB while the application data segments remain private to the PDB.
In this article we will explain how data sharing works across PDBs and CDBs. Oracle 19c Database allows three different techniques of data sharing:
- Metadata Link
- Data Link
- Extended Data Link
These sharing methods are what Oracle databases uses internally to achieve multitenant architecture. From 12.2 onwards, Oracle 19c Database allows users to create their own application root containers and then create PDBs inside the application containers. The root application container is a PDB to its main root container – CDB$ROOT.
Let’s get started with a test case. We will first create an application container and then a PDB inside the container. These tests are done on an Oracle Database 19c running on a Windows Platform. We have a root container already installed called ART01.
Log in as SYSDBA on ART01.
SQL> create pluggable database app_container as application container admin user admin identified by oracle file_name_convert=('D:\app\testart\oradata\ART01','D:\app\testart\oradata\app_container');
Pluggable database created.
SQL> alter session set container=app_container;
Session altered.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> create pluggable database APP_PDB1 admin user pdb1admin identified by oracle file_name_convert=('D:\app\testart\oradata','D:\app\testart\oradata\APP_PDB1');
Pluggable database created.
SQL> alter pluggable database APP_PDB1 open;
Pluggable database altered.
We now have an application container called APP_CONTAINER. And this container has a PDB called APP_PDB1.
Now let’s log into APP_CONTAINER and create shared objects. We will create three tables.
- ML_T: A metadata linked table
- EL_T: An extended data linked table
- DL_T: A data link table
Note that to be able to create shared objects, the application root container must be in install or upgrade mode.
SQL> alter session set container=APP_CONTAINER;
Session altered.
SQL> alter pluggable database application app_container begin install '1';
Pluggable database altered.
SQL> create table ML_T SHARING=METADATA (A NUMBER);
Table created.
SQL> create table EL_T SHARING=EXTENDED DATA(A NUMBER);
Table created.
SQL> create table DL_T SHARING=DATA (A NUMBER);
Table created.
SQL> alter pluggable database application app_container end install '1';
Pluggable database altered.
SQL> alter session set container=APP_PDB1;
Session altered.
SQL> alter pluggable database application app_container sync;
Pluggable database altered.
METADATA LINK TABLE
=====================
Table ML_T is a metadata linked table. The table definition or metadata is stored in the application container, but the actual data segment is private to each of the containers. The Sync command executed in the example above is a one-time copy, so the Oracle database can create the actual data segment in the PDBs. Now table ML_T exists in APP_CONTAINER and in APP_PDB1. They share the data dictionary, but the data is private to each of the containers.
SQL> alter session set container=APP_CONTAINER;
Session altered.
SQL> insert into ML_T values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ML_T;
A
----------
1
SQL> alter session set container=APP_PDB1;
Session altered.
SQL> insert into ML_T values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ML_T;
A
----------
2
As shown above, the ML_T data segment is private. APP_PDB1 can only see its data (2). While APP_CONTAINER can only see its version of the data (1).
EXTENDED DATA LINK TABLE
==========================
Table EL_T is an extended data link table. Metadata is shared and data exists in both the containers. The PDB can access its own data and the data from the application container.
SQL> alter session set container=APP_CONTAINER;
Session altered.
SQL> insert into EL_T values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from EL_T;
A
----------
1
SQL> alter session set container=APP_PDB1;
Session altered.
SQL> insert into EL_T values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from EL_T;
A
----------
2
1
As shown above, this last query returns 1,2. It shows data from both the APP_CONTAINER and APP_PDB1. In the APP_PDB1 pluggable database, rows on the EL_T table can be modified but not the rows coming from the application container.
DATA LINK TABLE
================
DL_T is a data linked table. Data segment exists only in the APP_CONTAINER. Data cannot be modified in the PDB. But the PDB can view the data that’s stored in the root container.
SQL> alter session set container=APP_CONTAINER;
Session altered.
SQL> insert into DL_T values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set container=APP_PDB1;
Session altered.
SQL> select * from DL_T;
A
----------
1
Any attempt to modify this data would result in an error.
SQL> update DL_T set A=99;
update DL_T set A=99
*
ERROR at line 1:
ORA-65097: DML into a data link table is outside an application action
So far, we have seen shared tables, similarly we can also create shared views. Let’s look at couple of examples.
SHARED VIEWS
==============
Below is the creation of a data link view called DL_V. This view selects data from table ML_T which is a metadata linked table we created previously.
There’s another view called EL_V which is an extended data link view. This view also selects data from the same metadata linked table, ML_T.
SQL> alter session set container=APP_CONTAINER;
SQL> alter pluggable database application app_container begin upgrade '1' to '2';
Pluggable database altered.
SQL> create view DL_V SHARING=DATA AS (SELECT * FROM ML_T);
View created.
SQL> create view EL_V SHARING=EXTENDED DATA AS (SELECT * FROM ML_T);
View created.
SQL> alter pluggable database application app_container end upgrade;
Pluggable database altered.
SQL> alter session set container=APP_PDB1;
Session altered.
SQL> alter pluggable database application app_container sync;
Pluggable database altered.
SQL> select * from DL_V;
A
----------
1
In the example above, we’re logged in to APP_PDB1. We know that selecting from ML_T gives us the output of ‘2’. But selecting from this view, DL_V, we get ‘1’.
This is because the view is itself defined as a data link. This instructs the Oracle database to pull the data from the application root container and not from the current PDB. If you want to pull the data from the current APP_PDB1 use the Oracle supplied function NO_OBJECT_LINK.
SQL> select * from NO_OBJECT_LINK(DL_V);
A
----------
2
SQL> select * from EL_V;
A
----------
2
1
You can see similar behavior here. Selecting from the table ML_T gives us the output of ‘2’. But selecting from this view, EL_V, we get 1,2, just because the view is an extended data link.
The Oracle database pulls data from both the application root container (APP_CONTAINER) and the current PDB (APP_DB1). Again, if you want to pull the data only from the current PDB, use the function NO_OBJECT_LINK.
SQL> select * from NO_OBJECT_LINK(DL_V);
A
----------
2
Using either the Metadata Link, Data Link, or Extended Data Link sharing methods can be very beneficial and is an easy path to unlock the full potential of the database infrastructure and make use of the multitenant consolidated database feature available with Oracle 19c Database. Data sharing has become a key capability and is integral to increasing your database management potential. It is one of the most insightful data driven features released with the 19c version.