Container Maps

From Robs_Wiki
Revision as of 06:31, 6 October 2020 by Qadmin wiki (talk | contribs) (Synchronize the PDB and add data to the table)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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.

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.

Create an application in the application root that uses the container map

SQL> ALTER PLUGGABLE DATABASE APPLICATION app_rob BEGIN INSTALL '1.0';

Pluggable database altered.

SQL> CREATE TABLE c##robla.revenue SHARING=METADATA (
country VARCHAR2(30),
revenue number);  

Table created.

-- Remark: the Sharing=METADATA is optional. This means that only the metadata is shared between the different application PDB’s. 
-- The actual data is stored in each PDB separately and not shared.

SQL> ALTER TABLE c##robla.revenue ENABLE CONTAINER_MAP;

Table altered.

SQL> ALTER TABLE c##robla.revenue ENABLE CONTAINERS_DEFAULT;

Table altered.

SQL> ALTER PLUGGABLE DATABASE APPLICATION app_rob END INSTALL '1.0';

Pluggable database altered.

-- We can double check whether the tables where the data will be stored are enabled to use Container Maps 
-- by querying the view DBA_TABLES and its new column “CONTAINER_MAP”

SQL> select containers_default, container_map, table_name from dba_tables where owner = 'C##ROBLA';

CON CON TABLE_NAME
--- --- --------------------
YES YES REVENUE
NO  NO  CONTAINERMAP

Synchronize the PDB and add data to the table

SQL> alter session set container=north;

SQL> ALTER PLUGGABLE DATABASE APPLICATION app_rob sync;

Pluggable database altered.

SQL> insert into c##robla.revenue values ('CANADA',1000);

SQL> insert into c##robla.revenue values ('USA',2000);

SQL> commit;

SQL> alter session set container=central;

SQL>  ALTER PLUGGABLE DATABASE APPLICATION app_rob sync;

Pluggable database altered.

SQL> insert into c##robla.revenue values ('GUATEMALA',3000);

SQL> insert into c##robla.revenue values ('NICARAGUA',4000);

SQL> commit;

SQL> alter session set container=south;

SQL> ALTER PLUGGABLE DATABASE APPLICATION app_rob sync;

Pluggable database altered.

SQL> insert into c##robla.revenue values ('ARGENTINA',5000);

SQL> insert into c##robla.revenue values ('BRAZIL',6000);

SQL> commit;

Execute a query, using the PDB's as partitions

Connect to the “Application Root” and from there execute two queries. You can see that the SELECT statements don’t have any filter with the column CON_ID nor the Application PDB name. We are just getting data from a simple table (C##ROBLA.REVENUE), but the SELECT statement understands that Container Map is enabled, it will ask in which “partition” (Application PDB) the value “GUATEMALA” is stored and then it will query the table “C##ROBLA.REVENUE” in that specific Application PDB.

-- connect to the root container
SQL> alter session set container=pdb10;

Session altered.
-- issue the query

SQL> select country, revenue from c##robla.revenue where country='GUATEMALA';

COUNTRY                           REVENUE
------------------------------ ----------
GUATEMALA                            3000