Oracle 12c CDB – metadata & object links internals

By Franck Pachot

.
Warning: this is only geek stuff about internals on multitenant database dictionary, metadata, and object links. It has nothing to do with the operations that you can do on your database. Don’t try that in production or you can corrupt the whole dictionary.

In 12 multitenant database (aka CDB) we know that each pluggable database is isolated in order to act as a standalone database. But they share some common resources because that’s the main reason to consolidate into one CDB. No problem for cpu, memory, redo and undo resources. They are managed by the instance which is at CDB level only. And most of the time the container can be identified from the service, from the tablespace, etc. It’s not difficult either for data because each PDB has it’s own set of tablespaces and the pluggable feature is just an extension of the transportable tablespace.

What was probably more challenging for Oracle 12c architects is the way to implement the sharing of the dictionary.

First, each PDB has its own metadata describing its own data. But metadata for the dictionary itself must be shared. For example all the dbms_xxx packages are stored only in CDB$ROOT container and PDB have metadata links for them.

In addition to that, some dictionary data must be shared as well, such as some reference tables (AUDIT_ACTIONS) or common repositories (AWR data exposed as DBA_HIST_). They are stored only in CDB$ROOT container and each PDB defines a view with is just an object link for them.

Finally, the CDB$ROOT container must be able to query data from all PDB, for example using the new CDB_ views. They are exposed as container data objects, but they actually query data that is stored in each PDB.

But that sounds a bit magic isn’t it? The documentation doesn’t go very far on how it’s implemented internally. Fortunately, Oracle scripts in ?/rdbms/admin give a few clues. It shows how the SQL syntax is extended when running under “_ORACLE_SCRIPT”=true.

So, geek stuff is coming now. Let’s try to create metadata and object links ourselves…

The following is done after setting “_ORACLE_SCRIPT”=true in our session.
You will see these new syntax elements: cdb$view(), sharing=metadata, sharing=object, common_data

Container data objects

First, let’s see how the root can view data from other containers.

I am in the root container:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 CDB$ROOT
SQL> show con_id
 CON_ID
 ------------------------------
 1

and create a regular table:

SQL> create table DEMO_REG_TABLE sharing=none as select 111 dummy from dual;
 Table created.
SQL> select * from DEMO_REG_TABLE;
      DUMMY
 ----------
        111

Then I do exactly the same (but different data) in a PDB

SQL> alter session set container=pdb1;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 PDB1
SQL> show con_id
 CON_ID
 ------------------------------
 3
SQL> create table DEMO_REG_TABLE sharing=none as select 999 dummy from dual;
 Table created.
SQL> select * from DEMO_REG_TABLE;
      DUMMY
 ----------
        999

Now back in the root container, I’ll use the CDB$VIEW table function in order to see data from all PDB:

SQL> select * from  cdb$view(DEMO_REG_TABLE) where con_id in (1,3);
      DUMMY     CON_ID
 ---------- ----------
        999          3
        111          1

This is how container objects are defined. They use CDB$VIEW to run a query in each PDB, consolidate the result and add a CON_ID column to show where the data comes from.

You want to know how it is implemented? It seems that it is doing a parallel query on each PDB.
Here is the proof. Previously I used ‘con_id in (1,3)’ because I’ve not created my table in all PDB.

SQL> select * from  cdb$view(DEMO_REG_TABLE);
 select * from  cdb$view(DEMO_REG_TABLE)
 *
 ERROR at line 1:
 ORA-12801: error signaled in parallel query server P002
 ORA-00942: table or view does not exist

When the table is not found in a PDB the error comes from a parallel process.

Metadata links

Now I will create a function in both the CDB$ROOT container and in a PDB. But I don’t want to have the code stored twice. I’ll use SHARING=METADATA to define a metadata link.

SQL> alter session set container=cdb$root;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 CDB$ROOT
SQL> show con_id
CON_ID
 ------------------------------
 1
SQL> create function DEMO_MDL_FUNCTION sharing=metadata
   2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
   3  /
 Function created.
SQL> select DEMO_MDL_FUNCTION from dual;
 DEMO_MDL_FUNCTION
 ------------------------------
 111

This is my function in CDB$ROOT, showing content from my regular table in CDB$ROOT.
Now doing exactly the same in a PDB:

SQL> alter session set container=pdb1;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 PDB1
SQL> show con_id
 CON_ID
 ------------------------------
 3
SQL> create function DEMO_MDL_FUNCTION sharing=metadata
   2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
   3  /
 Function created.
SQL> select DEMO_MDL_FUNCTION from dual;
 DEMO_MDL_FUNCTION
 ------------------------------
 999

Here it is. I have the same function in my PDB, showing content from the regular table in PDB.

I can see the metadata for my function from the SYS.SOURCE$ dictionary table.
Here in CDB$ROOT:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');
      OBJ#       LINE SOURCE
 ---------- ---------- ------------------------------
      95789          1 function DEMO_MDL_FUNCTION

but let’s see what I have in my PDB:

SQL> alter session set container=pdb1;
 Session altered.
SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');
no rows selected

Nothing is stored in the PDB. Only the information (in OBJ$) that the object is a metadata link.

But there is another magic if I query the DBA_SOURCE view:

SQL> select * from dba_source where name like 'DEMO%';
OWNER NAME              TYPE      LINE TEXT                        ORIGIN_CON_ID
 ----- ----------------- --------- ---- --------------------------- -------------
 SYS   DEMO_MDL_FUNCTION FUNCTION     1 function DEMO_MDL_FUNCTION              1

DBA_SOURCE shows information from the CDB$ROOT, following the metadata links, and adds a ORIGIN_CON_ID column to see if the row is coming from the PDB dictionary or the CDB$ROOT dictionary. I’ll will detail that later by creating a ‘common data view’.

Object links

We have seen how the CDB$ROOT can store metadata for all the PDB. We will use it to create a table with metadata link. But in addition to that we will create an object link so that the table in CDB$ROOT will store the data for all PDB. I’ll use SHARING=METADATA to create the table and SHARING=OBJECT when creating the view.

First I create the table in both containers:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 CDB$ROOT
SQL> show con_id
 CON_ID
 ------------------------------
 1
SQL> create table DEMO_MDL_TABLE sharing=metadata as select 111 dummy from dual;
 Table created.
SQL> alter session set container=pdb1;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 PDB1
SQL> show con_id
CON_ID
 ------------------------------
 3
SQL> create table DEMO_MDL_TABLE sharing=metadata as select 999 dummy  from dual;
 Table created.

So the table is created in both containers. I’ve inserted different data in order to understand what happens. Let’s use db CDB$VIEW to show data from each container:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> select * from  cdb$view(DEMO_MDL_TABLE) where con_id in (1,3);
     DUMMY     CON_ID
 ---------- ----------
        999          3
        111          1

I have two tables with same structure (because it is a metadata link). The CDB$ROOT one contains ‘111’ and the PDB one contains ‘999’.

I’ll create a view on it, defining it as an object link so that the data is shared:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 CDB$ROOT
SQL> show con_id
 CON_ID
 ------------------------------
 1
SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
 View created.
SQL> select * from DEMO_OBL_VIEW;
      DUMMY
 ----------
        111

The view in CDB$ROOT shows data from the table in CDB$ROOT. Now let’s do the same in a PDB:

SQL> alter session set container=pdb1;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 PDB1
SQL> show con_id
 CON_ID
 ------------------------------
 3
SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
 View created.
SQL> select * from DEMO_OBL_VIEW;
      DUMMY
 ----------
        111

The view in the PDB shows data from the table in CDB$ROOT. The query followed the object link instead of accessing the current container table.

How data is stored in that container table? It is stored from the container. Think about AWR which run at CDB level and stores its data in WRM$ tables. Then each PDB can query them with the DBA_HIST_ views. But just in case you want to try, you can’t insert into an object link:

SQL> insert into DEMO_OBL_VIEW select 9999 dummy from dual;
 insert into DEMO_OBL_VIEW select 9999 dummy from dual
             *
 ERROR at line 1:
 ORA-02030: can only select from fixed tables/views

This is a clue about the implementation. Object links are accessed through fixed tables. And if you explain plan from the PDB you will see it:

---------------------------------------------
 | Id  | Operation        | Name             |
 ---------------------------------------------
 |   0 | SELECT STATEMENT |                  |
 |   1 |  FIXED TABLE FULL| X$OBLNK$aed0818c |
 ---------------------------------------------

Common data views

Finally, let’s see how a PDB can show data coming from the CDB$ROOT. Dictionary tables such as DBA_SOURCE must show common metadata as well as PDB metadata. It is defined as a ‘common data view’ and I’ll create one here using COMMON_DATA:

SQL> alter session set container=cdb$root;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 CDB$ROOT
SQL> show con_id
 CON_ID
 ------------------------------
 1
SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
 View created.
SQL> select * from DEMO_INT_VIEW;
     DUMMY    SHARING
 ---------- ----------
        111          1
        222          0

I’ve added a ‘SHARING’ column, that is required when using COMMON_DATA, in order to flag rows that are shared to other containers (1) and rows that are not (0). Here the line with ‘222’ is private to my container and the others (‘111’) can be seen by the PDB. And as usual, I’m doing exactly the same in the pdb:

SQL> alter session set container=pdb1;
 Session altered.
SQL> show con_name
 CON_NAME
 ------------------------------
 PDB1
SQL> show con_id
 CON_ID
 ------------------------------
 3
SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
 View created.
SQL> select * from DEMO_INT_VIEW;
      DUMMY    SHARING ORIGIN_CON_ID
 ---------- ---------- -------------
        999          1             3
        111          1             1

When in the PDB the COMMON_DATA view shows the shared rows from the CDB$ROOT table in addition to the the rows from PDB table.
Of course, having read what is above, you expect to see a parallel process and a fixed table:

SQL> set autotrace on
 SQL> select * from DEMO_INT_VIEW;
     DUMMY    SHARING ORIGIN_CON_ID
 ---------- ---------- -------------
        111          1             1
        999          1             3
Execution Plan
 ----------------------------------------------------------
 Plan hash value: 3158883863
--------------------------------------------------------------------------------------------
 |Id  | Operation               | Name            |Pstart|Pstop |   TQ  |IN-OUT| PQ Distrib |
 --------------------------------------------------------------------------------------------
 |  0 | SELECT STATEMENT        |                 |      |      |       |      |            |
 |  1 |  PX COORDINATOR         |                 |      |      |       |      |            |
 |  2 |   PX SEND QC (RANDOM)   | :TQ10000        |      |      | Q1,00 | P->S | QC (RAND)  |
 |  3 |    PX PARTITION LIST ALL|                 |    1 |    2 | Q1,00 | PCWC |            |
 |  4 |     FIXED TABLE FULL    | X$COMVW$e40eb386|      |      | Q1,00 | PCWP |            |
 --------------------------------------------------------------------------------------------

The fixed table returns each container data as a partition, each gathered by a parallel process and returned to the query.

This is enough about multitenant dictionary internals investigation.
If you want more, have a look at ?/rdbms/admin/noncdb_to_pdb.sql which exposes all the magic that is done to transform a standalone dictionary to a linked one.
If you want more conventional information about pluggable databases, and manipulate them as they are designed to, please come to our Oracle 12c New Features workshops.

Update on Nov. 11th 2014

Container data objects have changed in in first patchset. See 12.1.0.2 CDB views are now using CONTAINERS() for information about it.