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.