Querying all oracle 12c pluggable databases within a container database at the same time?

While trying to work out how some of the new PDB/CDB functionality is implemented (specifically looking at how users are being stored) i discovered a useful inbuilt function that allows you to query the contents of the same table in multiple different pluggable databases at the same time.

Oracle specifically users this function itself in the view CDB_USERS (that's how i discovered it).

The function is called CDB$VIEW and is used as follows:

SQL> select * from CDB$VIEW("SYS"."DUAL");

D     CON_ID
- ----------
X          3
X          4
X          1
X          2

Here you can see the contents of the dual table from all 4 of my 'databases' (the container + 3 plugged in databases). The query seems to be run against each container and the results unioned together (with the container id appended)

This behaves very much like the gv$ views for RAC giving you an overall view of everything.


Post a Comment