Skip to main content

Multitenant CDB views trick

In this post, let's look at the new dictionary views added by multitenant: the CDB views and how they actually work.
With the advent of Oracle 12c, and the multitenant database, one more layer of naming has been added. It is no longer enough to specify just the schema and table as a unique identifier, and that new required layer is, of course, the PDB.

Oracle has decided to keep things much the same for a user connecting to a PDB; although a DBA can see some differences (e.g. redo), a user (or an application) can be virtually oblivious to this new multi-tenancy functionality.

One of the consequences is that Oracle decided to have all DBA (plus ALL and USER) views show only the current PDB. So to show data from all PDBs a new family of views has been introduced; the CDB views. Generally speaking then, for any DBA view showing all objects in a PDB, there is an associated CDB view that displays this information globally, across all PDBs.

CDB$VIEW

In 12.1.0.1, Oracle introduced a function called CDB$VIEW and the query for CDB_OBJECTS looks like this:

SELECT ..., CON_ID FROM CDB$VIEW("SYS"."DBA_OBJECTS")

This special function works similar to GV$ views in that it runs a parallel query to query all the PDBs, and then union all the results together. It also adds CON_ID to show which PDB each row comes from.
It actually queries all the containers, including CDB$ROOT and PDB$SEED:
select con_id, count(*) from cdb_data_files group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         2          2
         1          4
         4          3
         3          5

The function is not documented, and although you can try using it, you will run into strange behavior. If you use a non-global user, you can end up killing the server process:

select * from cdb$view("SCOTT"."EMP")
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xA] [PC:0xB83E308, kglhdgn()+120] [flags: 0x0, count: 1
kglhdgn()+120        signal   __sighandler()       7F5B34766C40 ?
kglrdti()+238        call     kglhdgn()            7F5B34766C40 ?
qcdlgbo()+5243       call     kglrdti()            7F5B34766C40 ?

And if you use global user (as you should), you run into another problem: the table must exist in all containers, including PDB$SEED.
So if it is not in CDB$ROOT, the query fails immediately:
select * from cdb$view("SYSTEM"."TEST1")
 *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

But if you create this in root, you are still missing the PDB$SEED:
select * from cdb$view("SYSTEM"."TEST1")
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P001
ORA-00942: table or view does not exist

And to modify PDB$SEED, you have to open it read-write... which is scary and not something I would recommend!:
alter session set "_oracle_script"=TRUE;

alter pluggable database pdb$seed close immediate;
alter pluggable database pdb$seed open read write;
...
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open read only;
alter session set "_oracle_script"=FALSE;

Fortunately, Oracle is clever enough to run the PX slaves only for those that are really needed - so specifying CON_ID, you can get around the PDB$SEED issue.

select * from cdb$view("SYSTEM"."TEST1") where con_id in (3,4);
D CON_ID
- ----------
X          3
X          4

However, you still need the CDB$ROOT copy though, so that Oracle knows the table definition. And, of course, the table definition must be the same in all PDBs, otherwise you get errors about missing columns - or something more obtuse.
select * from cdb$view("SYSTEM"."TEST1") where con_id in (4)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-00904: "DUMMY": invalid identifier

CONTAINERS

12.1.0.2 changed this functionality, so that it now uses a CONTAINERS() clause:
SELECT ..., CON_ID FROM CONTAINERS("SYS"."DBA_OBJECTS")

The is very similar to the CDB$VIEW functionality, but the biggest 'improvement' is that it is now a documented and supported option, which you can safely use on your own tables. But note that there are still some quirks regarding the ownership, meaning it must be owned by the same common (=CDB level) user.

Also one noteworthy point is that the clause now excludes PDB$SEED by default. While this is usually a good thing (you don't want to bother with tables nobody can actually use), it can sting you when querying CDB_DATA_FILES. For example, it no longer shows all the files of a database, although there is a parameter for this; set exclude_seed_cdb_view: i.e:
select * from containers("SYSTEM"."TEST1")
SQL> select con_id, count(*) from cdb_data_files group by con_id;

select con_id, count(*) from cdb_data_files group by con_id;

    CON_ID   COUNT(*)
---------- ----------
         1          4
         3          3
         4          3

As for other changes covered by what we have discussed above, not much is different. Using a non-global user is still prohibited, but at least there is a proper error message to report this now:

select * from containers("SCOTT"."EMP")
                                 *
ERROR at line 1:
ORA-00942: table or view does not exist

And because it does not query PDB$SEED, one does not need to use the CON_ID predicate to exclude the PDB$SEED from the query.

Comments

Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors.

I tried to reproduce the error on my test system, using different versions (EE, SE, 11.1.0.6, 11.1.0.7), but to no avail. Fortunately, I finally got to the failing system:

SQL> recover standby database;
ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1
ORA-00289: suggestion :
/u01/flash_recovery_area/T1/archivelog/2009_11_27/o1_mf_1_208_%u_.arc
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-00280: change 9614132 for thread 1 is in sequence #208


Interestingly, nothing interesting is written to alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and slow.direct SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

Reading execution plan from SGA and PGA - teaser

Some of you have seen my presentation about hidden parts of Oracle execution plans and how to access the plan in the memory directly and parse it. I presented it at OakTable World 2017  and it will be also presented in Wellington and Acukland this November.  You can download the presentation at http://vitspinka.com/files/ReadingPlanFromSGA-OTWatOOW-2017.pdf.


I realize that many people did not have the chance to attend... and that the slides need quite a lot of explaining, it's hard to understand this internals without more explanation.


Thus you can look forward to seeing a handful of blog posts, inspired by this presentation, and explain some of the aspects of this whole topic.

We will start with some basic tools; accessing SGA and PGA, which you may find useful for many other tasks, too. Then we will look at some details of the execution plan. This is not - and neither the presentation is - an exhaustive guide to the execution plan internals. It would be a multi-year project to t…