Demystifying Data Sharing in Oracle 19c Multitenant

Oracle introduced multitenant architecture in 12c. Purpose of multitenant is consolidation. Instead of having multiple databases on a server we can now create one consolidated database called the CDB. This can contain multiple pluggable databases (PDBs). To an application a PDB would appear as if it is a separate database. But behind the scenes we now have one instance, one set of background processes, one shared pool and so on. This architecture is based on data sharing between CDBs and PDBs. Oracle manages to keep the dictionary information in the CDB while the application data segments remain private to the PDB.

In this article we will understand how data sharing works across PDBs and CDBs.

Oracle allows three different techniques of sharing 

1. Metadata Link

2. Data Link

3. Extended Data Link

These sharing methods is what Oracle uses internally to achieve multitenant architecture. From 12.2 Oracle allows users to create their own application root containers and then create PDBs inside this application container. This root application container is actually 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 this container. I'm running Oracle database 19c on a Windows Platform. I have a root container already installed called NC001.

Log in as SYSDBA on NC001

SQL> create pluggable database app_container as application container admin user admin identified by oracle file_name_convert=('D:\app\beriv\oradata\NC001','D:\app\beriv\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\beriv\oradata','D:\app\beriv\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 has a PDB called APP_PDB1.

Now let us log in into this APP_CONTAINER and create shared objects.We have created three tables here.

ML_T   : A metadata linked table

EL_T    : An extended data linked table

DL_T   : A data link table

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 in the application container but the actual data segment is private to each of the containers. Sync command that I ran above is just a one time copy. So that Oracle can create the actual data segment in the PDBs. Now this table ML_T exists in APP_CONTAINER and also in APP_PDB1. They share the dictionary but the data is private to each of these 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  you can see, ML_T data segment is private. APP_PDB1 can only see its own data (2) . While APP_CONTAINER can see its own version of 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.
 Now the PDB can access its own data and also 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 you can see, this returns 1,2. It shows data from both the app_container and app_pdb1.
APP_PDB1 can modify its own rows 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 this 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

==============

Here I have created a data link view DL_V. And this view selects from table ML_T which is a metadata linked table we created above.
I created another view EL_V which is an extended data link view. And this view also selects 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

Here I'm logged in as APP_PDB1. We know that selecting from table 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 Oracle 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 you can 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

We see the similar behaviour here. We know that selecting from the table ML_T gives us the output of '2'. But selecting from this view EL_V we get 1,2. Because the view is an extended data link, Oracle pulls the data both from the application root container(app_container) and the current PDB(app_pdb1). 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

In the next article we will see how Oracle uses this data sharing framework to consolidate the AWR data in CDB-PDB.