Infrastructure at your Service

Franck Pachot

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.

24 Comments

  • Fagner Santos says:

    Hi Franck!
    Thanks to share your knoledge! you post is perfect and explained exactly what I was looking for and didint find at oracle documents.

    Regards,

    Fagner

  • Ivica ARsov says:

    Hi Franck,
    What I found strange is that according definition AUDIT_ACTIONS is object-linked, but still the data not shared to all pdbs from the root.

    CDB$ROOT:
    SQL> insert into audit_actions values (250,’a’);
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> select count(*) from audit_actions;

    COUNT(*)
    ———-
    213

    SQL>

    PDB1:
    SQL> alter session set container=pdb1;
    Session altered.
    SQL> select count(*) from audit_actions;

    COUNT(*)
    ———-
    212

    SQL>

    Also the ROWIDs are different.

    Regards,
    Ivica

  • Hi Ivica,
    Your comment is very interesting. AUDIT_ACTIONS was the first example I checked when investigating CDB dictionary and has mislead my comprehension (see http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=69321#202961).
    I think that we need a view above the table in order to share it (insert into PDB view will insert into CDB$ROOT table).
    I’ll check that and blog about it soon. cataudit.sql do not follow that rule but I think it’s a small bug.
    Best Regards,
    Franck.

  • Ivica Arsov says:

    Hi Franck,

    Yes, it sharing works only for views, and since the AUDIT_ACTIONS is defined as table (without view) the sql goes directly into the table in the container you’re connected to.

    SQL> show con_id;

    CON_ID
    ——————————
    1
    SQL> show con_name;

    CON_NAME
    ——————————
    CDB$ROOT
    SQL>
    SQL>
    SQL> create table mytable sharing=object as select 111 col2 from dual;
    Table created.
    SQL> create view mytable_v sharing=object as select * from mytable;

    View created.

    SQL> alter session set container=pdb1;

    Session altered.

    SQL> create table mytable sharing=object as select 222 col2 from dual;

    Table created.

    SQL> create view mytable_v sharing=object as select * from mytable;

    View created.

    SQL> select * from mytable_v; — data retrieved from root container

    COL2
    ———-
    111

    SQL> select * from mytable; — data retreived from pdb1 container

    COL2
    ———-
    222

    SQL>

    Interesting I needed table definitio in pdb container in order to create the view.
    After the view is created I can drop the table definition and the view’ll still work.

    SQL> show con_name;

    CON_NAME
    ——————————
    PDB1
    SQL>
    SQL> drop table mytable;

    Table dropped.

    SQL> select * from mytable_v;

    COL2
    ———-
    111

    SQL>

    Regards,
    Ivica

  • Hi Ivica,
    I didn’t expect that we can drop the underlying table and still use the view. The view should be invalidated. Are you still able to query the view after a flush shared_pool? Sometimes, objects remain in dictionary cache without being properly invalidated.
    I’ve posted my investigation about AUDIT_ACTIONS in a new post: https://www.dbi-services.com/index.php/blog/entry/oracle-multitenant-dictionary-object-links
    Best regards,
    Franck.

  • Ivica Arsov says:

    Hi Franck,

    Your question lead me to test what will happen if I try to recompile the view.
    What I found interesting (seems like a bug) is that I received a message “compilation errors” after recompilation, but the view was still working.

    SQL>
    SQL> alter view mytable_v compile;

    Warning: View altered with compilation errors.

    SQL> select * from mytable_v;

    COL2
    ———-
    111

    SQL>

    After flushing the shared pool I hit an error.

    SQL> alter system flush shared_pool;

    System altered.

    SQL> select * from mytable_v;
    select * from mytable_v
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04063: view “SYS.MYTABLE_V” has errors

    Regards,
    Ivica

  • Maciej Tokar says:

    Hi Franck,
    I have some problems while trying to reproduce your examples. DEMO_MDL_FUNCTION function gets created without the sharing set to METADATA LINK, despite the sharing=metadata was used. This is SI 12.1 PSU Apr 2016.

    SQL> show pdbs

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ---------- ------------------------------ ---------- ----------
    3 PDB READ WRITE NO

    SQL> create function DEMO_MDL_FUNCTION sharing=metadata
    return varchar2 as dummy varchar2(100);
    begin
    select max(dummy) into dummy from DEMO_REG_TABLE;
    return dummy;
    end;
    /

    Function created.

    SQL> column owner format a20
    SQL> column object_name format a30

    SQL> select owner,object_name,sharing from cdb_objects where object_name='DEMO_MDL_FUNCTION';

    OWNER OBJECT_NAME SHARING
    -------------------- ------------------------------ -------------
    SYS DEMO_MDL_FUNCTION NONE

    How it looks like from CDB's perspective:

    SQL> column owner format a20
    SQL> column object_name format a30
    SQL> select owner,object_name,sharing,con_id from cdb_objects where object_name='DEMO_MDL_FUNCTION';

    OWNER OBJECT_NAME SHARING CON_ID
    -------------------- ------------------------------ ------------- ----------
    SYS DEMO_MDL_FUNCTION METADATA LINK 1
    SYS DEMO_MDL_FUNCTION NONE 3

    Ideas?

  • Hi Maciej,
    I tested it and confirms that it works in 12.1.0.1 even when the table is sharing=NONE but requires sharing=METADATA in 12.1.0.2. Probably a check that all dependencies must have same structure. Not a bad idea.
    In the book we’re not talking about internals.
    There are some internals here: http://www.soug.ch/fileadmin/user_upload/SIGs/SOUG_DAY2_160623/MultitenantBeyondDocumentation_-_12cR1_version_dbi_Pachot.pdf
    Regards,
    Franck.

  • David says:

    Hi Franck,
    I have just tested some of your examples.

    I found that I cant use the sharing=metadata option for the creation of functions in Oracle 12.1.0.2.

    I get the error message ORA-65021: illegal use of SHARING clause.

    Am I missing something or has this changed since your blog?

    David

  • Hi David,

    65021, 00000, "illegal use of SHARING clause"
    // *Cause: A SHARING clause was encountered in unexpected context.
    // *Action: Do not use SHARING clause outside of Oracle-supplied scripts.

    Did you set ‚Äú_ORACLE_SCRIPT‚ÄĚ=true for your session before it?

  • David says:

    Hi Franck,
    yes that was the issue!

    I have tried it and it works ok.

    One question I have related to this is that we want to create a number of functions that we want to work in all PDBs and CDB$ROOT.

    Can we use this approach to do it?

    Or should we create them with sharing=none?

    Regards

    David

  • Zydrunas says:

    Dear Franck,

    Your post was very useful for me in replicating gv$session in PDB, as inside pluggable DB sessions constantly migrate from PDB to root container and so they are not visible from inside PDB and this prevents applications from being able to properly monitor their progress via the view. Unfortunately I noticed one issue with this object level sharing. It seems that on PDB level data refreshes every half a minute, while on CDB it does immediately.

    Any ideas about such behavior?

    Here’s a test case..
    Create views on both CDB$ROOT and PDB

    create or replace view SCHEMA.TEST_SESSION sharing=object as select * from SYS.GV_$SESSION;

    run selects on each

    select sysdate,count(*) from ECBSYS.TEST_SESSION;

    You will notice that on CDB this view refreshes constantly, while on PDB it only refreshes twice per minute at 00 and 30 seconds.

    • Hi Zydrunas,

      That’s very interesting. Thanks.
      Here is how I reproduced it:


      14:59:36 SQL> connect / as sysdba
      Connected.
      14:59:36 SQL> alter session set container=CDB$ROOT;
      Session altered.
       
      14:59:36 SQL> alter session set "_oracle_script"=true;
      Session altered.
       
      14:59:36 SQL> create view DEMOV sharing=object as select * from gv$session;
      View created.
       
      14:59:36 SQL> alter session set container=PDB1;
      Session altered.
       
      14:59:36 SQL> create view DEMOV sharing=object as select * from gv$session;
      View created.
       
      14:59:36 SQL> alter session set "_oracle_script"=false;
      Session altered.
       
      14:59:36 SQL>
      14:59:36 SQL> set serveroutput on
      14:59:36 SQL> declare
      14:59:36 2 x varchar2(100);
      14:59:36 3 begin
      14:59:36 4 for i in 1..120 loop
      14:59:36 5 dbms_lock.sleep(1);
      14:59:36 6 select to_char(current_timestamp)||' --> '||max(last_call_et) into x from DEMOV;
      14:59:36 7 dbms_output.put_line(x);
      14:59:36 8 end loop;
      14:59:36 9 end;
      14:59:36 10 /
      05-AUG-16 02.59.37.687834 PM +00:00 --> 600411
      05-AUG-16 02.59.38.695735 PM +00:00 --> 600411
      05-AUG-16 02.59.39.695722 PM +00:00 --> 600411
      05-AUG-16 02.59.40.695820 PM +00:00 --> 600411
      05-AUG-16 02.59.41.695732 PM +00:00 --> 600411
      05-AUG-16 02.59.42.695725 PM +00:00 --> 600411
      05-AUG-16 02.59.43.695728 PM +00:00 --> 600411
      05-AUG-16 02.59.44.696733 PM +00:00 --> 600411
      05-AUG-16 02.59.45.696697 PM +00:00 --> 600411
      05-AUG-16 02.59.46.696687 PM +00:00 --> 600411
      05-AUG-16 02.59.47.696641 PM +00:00 --> 600411
      05-AUG-16 02.59.48.696721 PM +00:00 --> 600411
      05-AUG-16 02.59.49.696689 PM +00:00 --> 600411
      05-AUG-16 02.59.50.696741 PM +00:00 --> 600411
      05-AUG-16 02.59.51.696679 PM +00:00 --> 600411
      05-AUG-16 02.59.52.696703 PM +00:00 --> 600411
      05-AUG-16 02.59.53.696690 PM +00:00 --> 600411
      05-AUG-16 02.59.54.696697 PM +00:00 --> 600411
      05-AUG-16 02.59.55.696695 PM +00:00 --> 600411
      05-AUG-16 02.59.56.696702 PM +00:00 --> 600411
      05-AUG-16 02.59.57.696694 PM +00:00 --> 600411
      05-AUG-16 02.59.58.696723 PM +00:00 --> 600411
      05-AUG-16 02.59.59.696703 PM +00:00 --> 600411
      05-AUG-16 03.00.00.696748 PM +00:00 --> 600411
      05-AUG-16 03.00.01.696734 PM +00:00 --> 600411
      05-AUG-16 03.00.02.696658 PM +00:00 --> 600411
      05-AUG-16 03.00.03.697672 PM +00:00 --> 600411
      05-AUG-16 03.00.04.697720 PM +00:00 --> 600411
      05-AUG-16 03.00.05.697699 PM +00:00 --> 600411
      05-AUG-16 03.00.06.697723 PM +00:00 --> 600411
      05-AUG-16 03.00.07.697672 PM +00:00 --> 600411
      05-AUG-16 03.00.08.697679 PM +00:00 --> 600442
      05-AUG-16 03.00.09.697803 PM +00:00 --> 600442
      05-AUG-16 03.00.10.697727 PM +00:00 --> 600442
      05-AUG-16 03.00.11.697733 PM +00:00 --> 600442
      05-AUG-16 03.00.12.697750 PM +00:00 --> 600442
      05-AUG-16 03.00.13.697857 PM +00:00 --> 600442
      05-AUG-16 03.00.14.697836 PM +00:00 --> 600442
      05-AUG-16 03.00.15.697716 PM +00:00 --> 600442
      05-AUG-16 03.00.16.697758 PM +00:00 --> 600442
      05-AUG-16 03.00.17.697732 PM +00:00 --> 600442
      05-AUG-16 03.00.18.697777 PM +00:00 --> 600442
      05-AUG-16 03.00.19.697795 PM +00:00 --> 600442
      05-AUG-16 03.00.20.697836 PM +00:00 --> 600442
      05-AUG-16 03.00.21.697823 PM +00:00 --> 600442
      05-AUG-16 03.00.22.698842 PM +00:00 --> 600442
      05-AUG-16 03.00.23.698805 PM +00:00 --> 600442
      05-AUG-16 03.00.24.698768 PM +00:00 --> 600442
      05-AUG-16 03.00.25.698816 PM +00:00 --> 600442
      05-AUG-16 03.00.26.698845 PM +00:00 --> 600442
      05-AUG-16 03.00.27.699815 PM +00:00 --> 600442
      05-AUG-16 03.00.28.699735 PM +00:00 --> 600442
      05-AUG-16 03.00.29.699735 PM +00:00 --> 600442
      05-AUG-16 03.00.30.699758 PM +00:00 --> 600442
      05-AUG-16 03.00.31.699760 PM +00:00 --> 600442
      05-AUG-16 03.00.32.699791 PM +00:00 --> 600442
      05-AUG-16 03.00.33.699742 PM +00:00 --> 600442
      05-AUG-16 03.00.34.699745 PM +00:00 --> 600442
      05-AUG-16 03.00.35.699749 PM +00:00 --> 600442
      05-AUG-16 03.00.36.699726 PM +00:00 --> 600442
      05-AUG-16 03.00.37.699682 PM +00:00 --> 600442
      05-AUG-16 03.00.38.699755 PM +00:00 --> 600472
      05-AUG-16 03.00.39.699686 PM +00:00 --> 600472
      05-AUG-16 03.00.40.699855 PM +00:00 --> 600472
      05-AUG-16 03.00.41.700684 PM +00:00 --> 600472
      05-AUG-16 03.00.42.700721 PM +00:00 --> 600472
      05-AUG-16 03.00.43.700728 PM +00:00 --> 600472
      05-AUG-16 03.00.44.700736 PM +00:00 --> 600472
      05-AUG-16 03.00.45.701710 PM +00:00 --> 600472
      05-AUG-16 03.00.46.701714 PM +00:00 --> 600472
      05-AUG-16 03.00.47.701718 PM +00:00 --> 600472
      05-AUG-16 03.00.48.701707 PM +00:00 --> 600472
      05-AUG-16 03.00.49.701782 PM +00:00 --> 600472
      05-AUG-16 03.00.50.701755 PM +00:00 --> 600472
      05-AUG-16 03.00.51.701700 PM +00:00 --> 600472
      05-AUG-16 03.00.52.701732 PM +00:00 --> 600472
      05-AUG-16 03.00.53.701696 PM +00:00 --> 600472
      05-AUG-16 03.00.54.701726 PM +00:00 --> 600472
      05-AUG-16 03.00.55.701685 PM +00:00 --> 600472
      05-AUG-16 03.00.56.701753 PM +00:00 --> 600472
      05-AUG-16 03.00.57.701740 PM +00:00 --> 600472
      05-AUG-16 03.00.58.701730 PM +00:00 --> 600472
      05-AUG-16 03.00.59.701699 PM +00:00 --> 600472
      05-AUG-16 03.01.00.702759 PM +00:00 --> 600472
      05-AUG-16 03.01.01.702730 PM +00:00 --> 600472
      05-AUG-16 03.01.02.702775 PM +00:00 --> 600472
      05-AUG-16 03.01.03.702687 PM +00:00 --> 600472
      05-AUG-16 03.01.04.703725 PM +00:00 --> 600472
      05-AUG-16 03.01.05.703714 PM +00:00 --> 600472
      05-AUG-16 03.01.06.703757 PM +00:00 --> 600472
      05-AUG-16 03.01.07.703728 PM +00:00 --> 600472
      05-AUG-16 03.01.08.703839 PM +00:00 --> 600472
      05-AUG-16 03.01.09.703819 PM +00:00 --> 600503
      05-AUG-16 03.01.10.704710 PM +00:00 --> 600503
      05-AUG-16 03.01.11.704797 PM +00:00 --> 600503
      05-AUG-16 03.01.12.704740 PM +00:00 --> 600503
      05-AUG-16 03.01.13.704758 PM +00:00 --> 600503
      05-AUG-16 03.01.14.704805 PM +00:00 --> 600503
      05-AUG-16 03.01.15.704719 PM +00:00 --> 600503
      05-AUG-16 03.01.16.704813 PM +00:00 --> 600503
      05-AUG-16 03.01.17.705944 PM +00:00 --> 600503
      05-AUG-16 03.01.18.706731 PM +00:00 --> 600503
      05-AUG-16 03.01.19.706840 PM +00:00 --> 600503
      05-AUG-16 03.01.20.707726 PM +00:00 --> 600503
      05-AUG-16 03.01.21.707812 PM +00:00 --> 600503
      05-AUG-16 03.01.22.708778 PM +00:00 --> 600503
      05-AUG-16 03.01.23.708697 PM +00:00 --> 600503
      05-AUG-16 03.01.24.708726 PM +00:00 --> 600503
      05-AUG-16 03.01.25.708789 PM +00:00 --> 600503
      05-AUG-16 03.01.26.708830 PM +00:00 --> 600503
      05-AUG-16 03.01.27.708795 PM +00:00 --> 600503
      05-AUG-16 03.01.28.709881 PM +00:00 --> 600503
      05-AUG-16 03.01.29.710826 PM +00:00 --> 600503
      05-AUG-16 03.01.30.711801 PM +00:00 --> 600503
      05-AUG-16 03.01.31.711736 PM +00:00 --> 600503
      05-AUG-16 03.01.32.711807 PM +00:00 --> 600503
      05-AUG-16 03.01.33.711830 PM +00:00 --> 600503
      05-AUG-16 03.01.34.711748 PM +00:00 --> 600503
      05-AUG-16 03.01.35.711716 PM +00:00 --> 600503
      05-AUG-16 03.01.36.711725 PM +00:00 --> 600503
       
      PL/SQL procedure successfully completed.

      Note that this does not happen with a view on a regular table. I’ve no explanation for that, but as it is internal and undocumented… anything can happen.

      Regards,
      Franck.

  • […] a bit googling I found an excellent blog: http://blog.dbi-services.com/oracle-12c-cdb-metadata-a-object-links-internals/ that gave me an idea how to fix the issue (as usual ALTER SESSION SET ‚Äú_oracle_script‚ÄĚ=true […]

  • Charles Smiles says:

    Frank,

    Thanks for your detailed explanation of the metadata links and object links. I can’t say the same thing for the two clowns that “wrote” the “OCP 12c Upgrade 1Z0-060 Exam Guide” by Packt publishing that chose to plagiarize word for word notes you provided in this post without any citation. It’s quite remarkable and ballsy of them to think no one would find this. Go to page 49 three paragraphs down and you will find that you and the author just happened to be thinking the same thing..lol

    “…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.”
    For them it was two years after your blog post.

    I’ve actually thrown their book in the trash. Like many Oracle books in the market it’s nothing more than poorly paraphrased content from Oracle’s documentation that you end up marking up so much and have been forced to go online for deeper exposure only to run into an article like yours . The Oracle industry truly needs to self-regulate publication of commercial reading material. Just too much crap out there.
    At any rate, bringing this to your attention. Might want to get with the publisher. Who knows what else they have copied from you without citation.

    https://www.amazon.com/OCP-Upgrade-1Z0-060-Exam-guide/dp/1787126609/ref=sr_1_3?ie=UTF8&qid=1543235834&sr=8-3&keywords=1z0-060

  • appusamy says:

    Hi Franck

    How does sharing working in the package level . ?

    I have created the package on cdb root but the pdb does not have the visibility on the package.

    CREATE OR REPLACE PACKAGE testpackage AUTHID CURRENT_USER IS
    TYPE FilterTableType IS TABLE OF varchar2(32767) INDEX BY varchar2(30);
    FUNCTION getDataHistorical(
    dbid IN NUMBER := NULL
    , filter_list IN VARCHAR2 := NULL)
    RETURN XMLTYPE;
    END testpackage;
    /
    CDB root level
    select owner,object_name,sharing from cdb_objects where object_name=’testpackage’;
    OWNER OBJECT_TYPE SHARING
    DBSNMP PACKAGE BODY NONE
    DBSNMP PACKAGE NONE

    PDB level

    select owner,object_name,sharing from db_objects where object_name=’testpacakge’;

    no rows selected.

    Q1. Do i need to explicitly specify the sharing calause in the create stmt ? If yes mean syntax please
    Q2. Any other way to sharing the clause ? like alter session/system ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod