{"id":3723,"date":"2014-04-30T09:38:40","date_gmt":"2014-04-30T07:38:40","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/"},"modified":"2014-04-30T09:38:40","modified_gmt":"2014-04-30T07:38:40","slug":"oracle-12c-cdb-metadata-a-object-links-internals","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/","title":{"rendered":"Oracle 12c CDB – metadata & object links internals"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nWarning: 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.<\/p>\n

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.<\/p>\n

What was probably more challenging for Oracle 12c architects is the way to implement the sharing of the dictionary.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

So, geek stuff is coming now. Let’s try to create metadata and object links ourselves…<\/p>\n

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

Container data objects<\/h3>\n

First, let’s see how the root can view data from other containers.<\/p>\n

I am in the root container:<\/p>\n

SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n CDB$ROOT\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 1<\/code><\/pre>\n
and create a regular table:<\/p>\n
SQL>\u00a0create\u00a0table\u00a0DEMO_REG_TABLE\u00a0sharing=none\u00a0as\u00a0select\u00a0111\u00a0dummy\u00a0from\u00a0dual;\n Table\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_REG_TABLE;\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111<\/code><\/pre>\n
Then I do exactly the same (but different data) in a PDB<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n PDB1\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 3\nSQL>\u00a0create\u00a0table\u00a0DEMO_REG_TABLE\u00a0sharing=none\u00a0as\u00a0select\u00a0999\u00a0dummy\u00a0from\u00a0dual;\n Table\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_REG_TABLE;\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0999<\/code><\/pre>\n
Now back in the root container, I’ll use the CDB$VIEW table function in order to see data from all PDB:<\/p>\n
SQL>\u00a0select\u00a0*\u00a0from\u00a0\u00a0cdb$view(DEMO_REG_TABLE)\u00a0where\u00a0con_id\u00a0in\u00a0(1,3);\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\u00a0\u00a0\u00a0\u00a0\u00a0CON_ID\n ----------\u00a0----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0999\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/code><\/pre>\n
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.<\/p>\n
You want to know how it is implemented? It seems that it is doing a parallel query on each PDB.
\nHere is the proof. Previously I used ‘con_id in (1,3)’ because I’ve not created my table in all PDB.<\/p>\n
SQL>\u00a0select\u00a0*\u00a0from\u00a0\u00a0cdb$view(DEMO_REG_TABLE);\n select\u00a0*\u00a0from\u00a0\u00a0cdb$view(DEMO_REG_TABLE)\n *\n ERROR\u00a0at\u00a0line\u00a01:\n ORA-12801:\u00a0error\u00a0signaled\u00a0in\u00a0parallel\u00a0query\u00a0server\u00a0P002\n ORA-00942:\u00a0table\u00a0or\u00a0view\u00a0does\u00a0not\u00a0exist<\/code><\/pre>\n
When the table is not found in a PDB the error comes from a parallel process.<\/p>\n
Metadata links<\/h3>\n
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.<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n CDB$ROOT\nSQL>\u00a0show\u00a0con_id\nCON_ID\n ------------------------------\n 1\nSQL>\u00a0create\u00a0function\u00a0DEMO_MDL_FUNCTION\u00a0sharing=metadata\n \u00a0\u00a02\u00a0\u00a0return\u00a0varchar2\u00a0as\u00a0dummy\u00a0varchar2(100);\u00a0begin\u00a0select\u00a0max(dummy)\u00a0into\u00a0dummy\u00a0from\u00a0DEMO_REG_TABLE;\u00a0return\u00a0dummy;\u00a0end;\n \u00a0\u00a03\u00a0\u00a0\/\n Function\u00a0created.\nSQL>\u00a0select\u00a0DEMO_MDL_FUNCTION\u00a0from\u00a0dual;\n DEMO_MDL_FUNCTION\n ------------------------------\n 111<\/code><\/pre>\n
This is my function in CDB$ROOT, showing content from my regular table in CDB$ROOT.
\nNow doing exactly the same in a PDB:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n PDB1\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 3\nSQL>\u00a0create\u00a0function\u00a0DEMO_MDL_FUNCTION\u00a0sharing=metadata\n \u00a0\u00a02\u00a0\u00a0return\u00a0varchar2\u00a0as\u00a0dummy\u00a0varchar2(100);\u00a0begin\u00a0select\u00a0max(dummy)\u00a0into\u00a0dummy\u00a0from\u00a0DEMO_REG_TABLE;\u00a0return\u00a0dummy;\u00a0end;\n \u00a0\u00a03\u00a0\u00a0\/\n Function\u00a0created.\nSQL>\u00a0select\u00a0DEMO_MDL_FUNCTION\u00a0from\u00a0dual;\n DEMO_MDL_FUNCTION\n ------------------------------\n 999<\/code><\/pre>\n
Here it is. I have the same function in my PDB, showing content from the regular table in PDB.<\/p>\n
I can see the metadata for my function from the SYS.SOURCE$ dictionary table.
\nHere in CDB$ROOT:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0source$\u00a0where\u00a0obj#\u00a0in\u00a0(select\u00a0obj#\u00a0from\u00a0obj$\u00a0where\u00a0name\u00a0like\u00a0'DEMO%');\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0OBJ#\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LINE\u00a0SOURCE\n ----------\u00a0----------\u00a0------------------------------\n \u00a0\u00a0\u00a0\u00a0\u00a095789\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0function\u00a0DEMO_MDL_FUNCTION<\/code><\/pre>\n
but let’s see what I have in my PDB:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0source$\u00a0where\u00a0obj#\u00a0in\u00a0(select\u00a0obj#\u00a0from\u00a0obj$\u00a0where\u00a0name\u00a0like\u00a0'DEMO%');\nno\u00a0rows\u00a0selected<\/code><\/pre>\n
Nothing is stored in the PDB. Only the information (in OBJ$) that the object is a metadata link.<\/p>\n
But there is another magic if I query the DBA_SOURCE view:<\/p>\n
SQL>\u00a0select\u00a0*\u00a0from\u00a0dba_source\u00a0where\u00a0name\u00a0like\u00a0'DEMO%';\nOWNER\u00a0NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LINE\u00a0TEXT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ORIGIN_CON_ID\n -----\u00a0-----------------\u00a0---------\u00a0----\u00a0---------------------------\u00a0-------------\n SYS\u00a0\u00a0\u00a0DEMO_MDL_FUNCTION\u00a0FUNCTION\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0function\u00a0DEMO_MDL_FUNCTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/code><\/pre>\n
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’.<\/p>\n
Object links<\/h3>\n
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.<\/p>\n
First I create the table in both containers:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n CDB$ROOT\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 1\nSQL>\u00a0create\u00a0table\u00a0DEMO_MDL_TABLE\u00a0sharing=metadata\u00a0as\u00a0select\u00a0111\u00a0dummy\u00a0from\u00a0dual;\n Table\u00a0created.\nSQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n PDB1\nSQL>\u00a0show\u00a0con_id\nCON_ID\n ------------------------------\n 3\nSQL>\u00a0create\u00a0table\u00a0DEMO_MDL_TABLE\u00a0sharing=metadata\u00a0as\u00a0select\u00a0999\u00a0dummy\u00a0\u00a0from\u00a0dual;\n Table\u00a0created.<\/code><\/pre>\n
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:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0\u00a0cdb$view(DEMO_MDL_TABLE)\u00a0where\u00a0con_id\u00a0in\u00a0(1,3);\n\u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\u00a0\u00a0\u00a0\u00a0\u00a0CON_ID\n ----------\u00a0----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0999\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/code><\/pre>\n
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’.<\/p>\n
I’ll create a view on it, defining it as an object link so that the data is shared:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n CDB$ROOT\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 1\nSQL>\u00a0create\u00a0view\u00a0DEMO_OBL_VIEW\u00a0sharing=object\u00a0as\u00a0select\u00a0*\u00a0from\u00a0DEMO_MDL_TABLE;\n View\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_OBL_VIEW;\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111<\/code><\/pre>\n
The view in CDB$ROOT shows data from the table in CDB$ROOT. Now let’s do the same in a PDB:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n PDB1\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 3\nSQL>\u00a0create\u00a0view\u00a0DEMO_OBL_VIEW\u00a0sharing=object\u00a0as\u00a0select\u00a0*\u00a0from\u00a0DEMO_MDL_TABLE;\n View\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_OBL_VIEW;\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\n ----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111<\/code><\/pre>\n
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.<\/p>\n
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:<\/p>\n
SQL>\u00a0insert\u00a0into\u00a0DEMO_OBL_VIEW\u00a0select\u00a09999\u00a0dummy\u00a0from\u00a0dual;\n insert\u00a0into\u00a0DEMO_OBL_VIEW\u00a0select\u00a09999\u00a0dummy\u00a0from\u00a0dual\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0*\n ERROR\u00a0at\u00a0line\u00a01:\n ORA-02030:\u00a0can\u00a0only\u00a0select\u00a0from\u00a0fixed\u00a0tables\/views<\/code><\/pre>\n
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:<\/p>\n
---------------------------------------------\n |\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n ---------------------------------------------\n |\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0FIXED\u00a0TABLE\u00a0FULL|\u00a0X$OBLNK$aed0818c\u00a0|\n ---------------------------------------------<\/code><\/pre>\n
Common data views<\/h3>\n
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:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=cdb$root;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n CDB$ROOT\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 1\nSQL>\u00a0create\u00a0or\u00a0replace\u00a0view\u00a0DEMO_INT_VIEW\u00a0common_data\u00a0(dummy,sharing)\u00a0as\u00a0select\u00a0dummy,case\u00a0when\u00a0dummy='222'\u00a0then\u00a00\u00a0else\u00a01\u00a0end\u00a0from\u00a0DEMO_MDL_TABLE;\n View\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_INT_VIEW;\n\u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\u00a0\u00a0\u00a0\u00a0SHARING\n ----------\u00a0----------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0222\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00<\/code><\/pre>\n
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:<\/p>\n
SQL>\u00a0alter\u00a0session\u00a0set\u00a0container=pdb1;\n Session\u00a0altered.\nSQL>\u00a0show\u00a0con_name\n CON_NAME\n ------------------------------\n PDB1\nSQL>\u00a0show\u00a0con_id\n CON_ID\n ------------------------------\n 3\nSQL>\u00a0create\u00a0or\u00a0replace\u00a0view\u00a0DEMO_INT_VIEW\u00a0common_data\u00a0(dummy,sharing)\u00a0as\u00a0select\u00a0dummy,case\u00a0when\u00a0dummy='222'\u00a0then\u00a00\u00a0else\u00a01\u00a0end\u00a0from\u00a0DEMO_MDL_TABLE;\n View\u00a0created.\nSQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_INT_VIEW;\n \u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\u00a0\u00a0\u00a0\u00a0SHARING\u00a0ORIGIN_CON_ID\n ----------\u00a0----------\u00a0-------------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0999\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01<\/code><\/pre>\n
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.
\nOf course, having read what is above, you expect to see a parallel process and a fixed table:<\/p>\n
SQL>\u00a0set\u00a0autotrace\u00a0on\n SQL>\u00a0select\u00a0*\u00a0from\u00a0DEMO_INT_VIEW;\n\u00a0\u00a0\u00a0\u00a0\u00a0DUMMY\u00a0\u00a0\u00a0\u00a0SHARING\u00a0ORIGIN_CON_ID\n ----------\u00a0----------\u00a0-------------\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0111\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0999\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a03\nExecution\u00a0Plan\n ----------------------------------------------------------\n Plan\u00a0hash\u00a0value:\u00a03158883863\n--------------------------------------------------------------------------------------------\n |Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|Pstart|Pstop\u00a0|\u00a0\u00a0\u00a0TQ\u00a0\u00a0|IN-OUT|\u00a0PQ\u00a0Distrib\u00a0|\n --------------------------------------------------------------------------------------------\n |\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a01\u00a0|\u00a0\u00a0PX\u00a0COORDINATOR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0PX\u00a0SEND\u00a0QC\u00a0(RANDOM)\u00a0\u00a0\u00a0|\u00a0:TQ10000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Q1,00\u00a0|\u00a0P->S\u00a0|\u00a0QC\u00a0(RAND)\u00a0\u00a0|\n |\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0PX\u00a0PARTITION\u00a0LIST\u00a0ALL|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a02\u00a0|\u00a0Q1,00\u00a0|\u00a0PCWC\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n |\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0FIXED\u00a0TABLE\u00a0FULL\u00a0\u00a0\u00a0\u00a0|\u00a0X$COMVW$e40eb386|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Q1,00\u00a0|\u00a0PCWP\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\n --------------------------------------------------------------------------------------------<\/code><\/pre>\n
The fixed table returns each container data as a partition, each gathered by a parallel process and returned to the query.<\/p>\n
This is enough about multitenant dictionary internals investigation.
\nIf 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.
\nIf 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<\/a>.<\/p>\n
Update on Nov. 11th 2014<\/h3>\n
Container data objects have changed in in first patchset. See 12.1.0.2 CDB views are now using CONTAINERS()<\/a> for information about it.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[220,455,64,320,96,209,66,223],"type_dbi":[],"class_list":["post-3723","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cdb","tag-internals","tag-multitenant","tag-multitenant-database","tag-oracle","tag-oracle-12c","tag-pdb","tag-pluggable-databases"],"acf":[],"yoast_head":"\nOracle 12c CDB - metadata & object links internals - dbi Blog<\/title>\n<meta name=\"description\" content=\"Investigation on multitenant dictionary by creating object links and metadata links.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c CDB - metadata & object links internals\" \/>\n<meta property=\"og:description\" content=\"Investigation on multitenant dictionary by creating object links and metadata links.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-04-30T07:38:40+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c CDB – metadata & object links internals\",\"datePublished\":\"2014-04-30T07:38:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\"},\"wordCount\":1279,\"commentCount\":1,\"keywords\":[\"CDB\",\"internals\",\"multitenant\",\"Multitenant database\",\"Oracle\",\"Oracle 12c\",\"PDB\",\"Pluggable Databases\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\",\"name\":\"Oracle 12c CDB - metadata & object links internals - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-04-30T07:38:40+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Investigation on multitenant dictionary by creating object links and metadata links.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c CDB – metadata & object links internals\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12c CDB - metadata & object links internals - dbi Blog","description":"Investigation on multitenant dictionary by creating object links and metadata links.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c CDB - metadata & object links internals","og_description":"Investigation on multitenant dictionary by creating object links and metadata links.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/","og_site_name":"dbi Blog","article_published_time":"2014-04-30T07:38:40+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c CDB – metadata & object links internals","datePublished":"2014-04-30T07:38:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/"},"wordCount":1279,"commentCount":1,"keywords":["CDB","internals","multitenant","Multitenant database","Oracle","Oracle 12c","PDB","Pluggable Databases"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/","name":"Oracle 12c CDB - metadata & object links internals - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-04-30T07:38:40+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Investigation on multitenant dictionary by creating object links and metadata links.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12c-cdb-metadata-a-object-links-internals\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c CDB – metadata & object links internals"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3723"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3723\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3723"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}