Container Maps

From Robs_Wiki
Revision as of 06:23, 6 October 2020 by Qadmin wiki (talk | contribs) (Create the application root)
Jump to: navigation, search

Introduction

  • In Oracle Database version 12.1.0.2 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 12.2.0.1, “Container Maps” were introduced in Oracle 12.2.0.1.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.