- In Oracle Database version 188.8.131.52 Oracle introduced the “CONTAINERS” clause, a very useful clause that can be used to execute queries across several Pluggable Databases. You can filter which PDB you want to get the data from by the CON_ID column. The downside of using the “CONTAINERS” clause is that you have to hard code the value of the CON_ID column. If the CON_ID changes because of a PDB unplug and a PDB plugin, you would be getting data from a wrong PDB; or if you remove the PDB, your queries will simply fail.
- In 184.108.40.206, “Container Maps” were introduced in Oracle 220.127.116.11.0. In container maps the PDB name is used instead of the CON_ID when accessing the partitioned data. Unfortunately, at present, “Container Maps” are not available to use with normal Pluggable Databases. “Container Maps” can be used only with Application Containers (Application Root + Application PDBs).
Demo with Container Maps, including setup
Create the application root
We need an application root and application PDBs to setup container maps:
SQL> alter session set container=cdb$root; Session altered. SQL> create pluggable database pdb10 as application container admin user pdbadmin identified by pdbadmin; Pluggable database created. SQL> alter pluggable database pdb10 open; Pluggable database altered. SQL> alter pluggable database pdb10 save state; Pluggable database altered.
Create the Application PDBs
-- first connect to the application root SQL> alter session set container=pdb10; Session altered. SQL> create pluggable database north admin user app1admin identified by app1admin; Pluggable database created. SQL> create pluggable database central admin user app1admin identified by app1admin; Pluggable database created. SQL> create pluggable database south admin user app1admin identified by app1admin; Pluggable database created. SQL> alter pluggable database north, central, south open; Pluggable database altered. SQL> alter pluggable database north, central, south save state; -- View the results: SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- ---------- PDB10 READ WRITE NORTH READ WRITE CENTRAL READ WRITE SOUTH READ WRITE -- Remark: because we issue this query in the application container, we only see the application pdb’s. -- If we issue the same query in the root container we see all the pdb’s.
Create the Container Map
A container map is a simple table that has the information on which “partitions” (Application PDBs) are used and which column is used to address the data; in this case the column “country”. The type of partitioning used here is “BY LIST”. Note that the name of the “partitions” matches exactly with the name of the “Application PDBs”.
-- Connect to the application root and create the table: SQL> alter session set container=pdb10; SQL> CREATE TABLE c##robla.containermap ( country VARCHAR2(30) NOT NULL) PARTITION BY LIST (country) ( PARTITION north VALUES ('CANADA','USA'), PARTITION central VALUES ('GUATEMALA','NICARAGUA'), PARTITION south VALUES ('ARGENTINA','BRAZIL')); Table created. -- Now set the application container to use this containermap: SQL> ALTER PLUGGABLE DATABASE SET CONTAINER_MAP='C##ROBLA.CONTAINERMAP'; Pluggable database altered.