By Franck Pachot

.
As in previous posts (1 and 2) I’m still playing with multitenant dictionary. You may wonder why I’m spending time to do unsupported things that we will never be able to use in real life.

Of course, playing with internals is fun 😉

But it’s not only that. We will implement CDB databases (multi-tenant, and single-tenant as I think non-CDB will be deprecated in future versions). We will operate pluggable databases (clone, upgrade, downgrade,…), encounter issues, etc. Before doing that on critical environments, I need to be confident about the multitenant implementation. I’ts new, it’s undocumented, and – at the first sight – it looks like a ‘hack’ in the way the dictionary is working for years.

The first time I’ve seen the noncdb_to_pdb.sql I was scared. The first object link tables I’ve checked was not working as expected. And the first recovery scenario I tried had issues. And this is why I started investigating.

Currently, I feel more confident about it. Those metadata links and object links works well and seems to be robust. And the implementation is not a weird black box anymore, but just something to learn and experiment.

Create shared function

I’ll create two functions with sharing=metadata, DEMO_FUNCTION0 which calls DEMO_FUNCTION1.
In order to do that I set “_oracle_script” to true and run the same create statements in root and in the pdb.

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;
  2  /
Function created.

SQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;
  2  /
Function created.

SQL> grant execute on DEMO_FUNCTION0 to public;
Grant succeeded.

now in the pdb:

SQL> alter session set container=pdb1;
Session altered.

SQL> create function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo'; end;
  2  /
Function created.

SQL> create function DEMO_FUNCTION0 sharing=metadata return varchar2 as begin return DEMO_FUNCTION1; end;
  2  /
Function created.

SQL> grant execute on DEMO_FUNCTION0 to public;
Grant succeeded.

Check multitenant dictionary

Then I can check the dictionary tables (not the views, but the underlying tables).

First in root:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE
----- -------------- ------ ------- ---- --------------------------------
92370 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
92369 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

I see the two functions in OBJ$ (the table behind DBA_OBJECTS) which are flagged as metadata links and in 12c all objects have a signature.
The major storage of the function metadata is the pl/sql code in SOURCE$:

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

 OBJ# LINE SOURCE
----- ---- --------------------------------------------------
92370    1 function DEMO_FUNCTION0                  return va
           rchar2 as begin return DEMO_FUNCTION1; end;

92369    1 function DEMO_FUNCTION1                  return va
                      rchar2 as begin return 'demo'; end;

And because I’ve granted execution to public I have an entry in OBJAUTH$:

SQL> select obj#,grantor#,grantee# from objauth$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

      OBJ#   GRANTOR#   GRANTEE#
---------- ---------- ----------
     92370          0          1

Finally, I have a dependency between those functions as DEMO_FUNCTION0 is calling DEMO_FUNCTION1:

SQL> select d_obj#,p_obj# from dependency$ where p_obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

    D_OBJ#     P_OBJ#
---------- ----------
     92370      92369

I’ll now look at the same information in the pdb. I don’t expect to have all of those information being redundantly stored because this is the main goal of multitenant: avoid to store the metadata link objects in all containers.

SQL> alter session set container=pdb1;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS   FLAGS SHAR SIGNATURE
----- -------------- ------ ------- ---- --------------------------------
91829 DEMO_FUNCTION0      1 5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1 5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

It’s the same information as in the root because the create statements are exactly the same.

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

no rows selected

This is the important point. The most important part of metadata that we don’t want to duplicate among all containers is the pl/sql code (wrapped or not) of all those dbms_ packages. When you have a metadata link function, then the code is not stored in the pdb SOURCE$. When a session has to access to it, it sees that it is a metadata link (from OBJ$) and then switch temporarily to the root in order to get it and populate the pdbdictionary cache.

SQL> select obj#,grantor#,grantee# from objauth$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

      OBJ#   GRANTOR#   GRANTEE#
---------- ---------- ----------
     91829          0          1

The objects privileges are stored at pdblevel because they can be different than in root (commonality of users and privileges).

SQL> select d_obj#,p_obj# from dependency$ where p_obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

    D_OBJ#     P_OBJ#
---------- ----------
     91829      91828

The dependency is stored at pdblevel for two reasons:

  • the objects id are different.
  • dependency can be different among PDBs

Remember that you can plug a PDB from another CDB and from another version of Oracle. Then the dependency used to invalidate to force recompilation makes sense.

Run the function from both containers

SQL> alter session set container=cdb$root;
Session altered.

SQL> select DEMO_FUNCTION0 from dual;

DEMO_FUNCTION0
--------------------------------------------
demo

SQL> alter session set container=pdb1;
Session altered.

SQL> select DEMO_FUNCTION0 from dual;

DEMO_FUNCTION0
--------------------------------------------
demo

We have same result.

And we can check that each container has filled its own buffer cache:

SQL> select con_id,name,type,sharable_mem from gv$db_object_cache where name like 'DEMO_FUNCTION_';

    CON_ID NAME                TYPE       SHARABLE_MEM
---------- ------------------- ---------- ------------
         3 DEMO_FUNCTION0      FUNCTION          24552
         3 DEMO_FUNCTION1      FUNCTION           8168
         1 DEMO_FUNCTION0      FUNCTION          24552
         1 DEMO_FUNCTION1      FUNCTION           8168

PDB in a different version than CDB

Now let’s imagine that we plug a PDB which has different version. We can also ‘patch’ our pdb by changing the function – however I don’t think it is supported to patch a PDB to a version different than the CDB.

SQL> create or replace function DEMO_FUNCTION1 sharing=metadata return varchar2 as begin return 'demo from pdb'; end;
  2  /
Function created.

I’ve changed the function in the pdb which is now different than the root one.
Let’s check what we have now on both containers:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

      OBJ# NAME                   STATUS      FLAGS SHAR SIGNATURE
---------- ------------------ ---------- ---------- ---- --------------------------------
     92370 DEMO_FUNCTION0              1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
     92369 DEMO_FUNCTION1              1    5308416 MDL  0BCDC8C960AD833F95F0595EEBB70745

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

 OBJ# LINE SOURCE
----- ---- --------------------------------------------------
92370    1 function DEMO_FUNCTION0                  return va
           rchar2 as begin return DEMO_FUNCTION1; end;

92369    1 function DEMO_FUNCTION1                  return va
                      rchar2 as begin return 'demo'; end;

Nothing changed in the root

SQL> alter session set container=pdb1;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE
----- -------------- ------ ---------- ---- --------------------------------
91829 DEMO_FUNCTION0      5    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1   22020096 NONE FC39D079D5CD8B8743EDE6F7FCBFE33B
The signature is different and the sharing flag is not set to metadata link anymore. Note that the status of the calling function is now INVALID thanks to the dependency tracking at container level.
SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO_FUNCTION_');

      OBJ#       LINE SOURCE
---------- ---------- --------------------------------------------------
     91828          1 function DEMO_FUNCTION1                  return va
                      rchar2 as begin return 'demo from pdb'; end;

we have now the pl/sql code in the pdbas it is not a metadata link anymore.

Let’s run it:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select DEMO_FUNCTION0 from dual;

DEMO_FUNCTION0
--------------------------------------------
demo

SQL> alter session set container=pdb1;
Session altered.

SQL> select DEMO_FUNCTION0 from dual;

DEMO_FUNCTION0
--------------------------------------------
demo from pdb

This proves that the function is not shared anymore. Anything has been managed properly (dependency tracking, status invalidation, dictionary cache invalidation, etc.
As it has been executed, the invalid function has been recompiled automatically and is now valid:

SQL> alter session set container=pdb1;
Session altered.

SQL> select obj#,name,status,flags,decode(bitand(flags,196608),65536,'MDL',131072,'OBL','NONE') sharing,signature from obj$ where name like 'DEMO_FUNCTION_';

 OBJ# NAME           STATUS      FLAGS SHAR SIGNATURE
----- -------------- ------ ---------- ---- --------------------------------
91829 DEMO_FUNCTION0      1    5308416 MDL  ECE1890DECB2F100A2F755CC0B76166D
91828 DEMO_FUNCTION1      1   22020096 NONE FC39D079D5CD8B8743EDE6F7FCBFE33B

Conclusion

The main points here are:

  • metadata link stored procedure code is stored only in root
  • dependency and grants are stored in each container to be managed locally
  • ‘sharing=metadata’ and same signature are two mandatory conditions to have a metadata link

This implementation seems to be very robust: sharing is done only when the DDL is the same. Invalidation is properly managed. We have seen how it behaves when a function is changed in a PDB. Next blog post will show how it behaves when we change the function in the root.