Wednesday, 11 January 2017

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c

The PDB CONTAINERS clause allows data to be queried across multiple PDBs.

Setup

We need to create 3 PDBs to test the CONTAINERS clause. The setup code below does the following.
  • Creates a pluggable database called PDB1.
  • Creates a PDB1 with a local user called LOCAL_USER that owns a populated table called LOCAL_USER_TAB.
  • Creates two clones of PDB1 called PDB2 and PDB3.
CONN / AS SYSDBA

-- Create a pluggable database
CREATE PLUGGABLE DATABASE pdb1
  ADMIN USER pdb_admin IDENTIFIED BY Password1;

ALTER PLUGGABLE DATABASE pdb1 OPEN;

ALTER SESSION SET CONTAINER = pdb1;
CREATE TABLESPACE users;
ALTER DATABASE DEFAULT TABLESPACE users;

-- Create a local user.
CREATE USER local_user IDENTIFIED BY Local1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO local_user;

CREATE TABLE local_user.local_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 2;

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
ALTER PLUGGABLE DATABASE pdb2 OPEN;
CREATE PLUGGABLE DATABASE pdb3 FROM pdb1;
ALTER PLUGGABLE DATABASE pdb3 OPEN;

The next part of the setup does the following.
  • Creates a common user called C##COMMON_USER that owns an empty table called COMMON_USER_TAB in the root container.
  • Creates a populated version of the COMMON_USER_TAB table owned by the C##COMMON_USER user in each PDB.
  • Grants select privilege on the local user's table to the common user.
-- Create a common user that owns an empty table.
CONN / AS SYSDBA
CREATE USER c##common_user IDENTIFIED BY Common1 QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM TO c##common_user CONTAINER=ALL;

-- Create a table in the common user for each container.
-- Don't populate the one in the root container.
CONN c##common_user/Common1
CREATE TABLE c##common_user.common_user_tab (id NUMBER);

CONN c##common_user/Common1@pdb1

CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 2;

CONN c##common_user/Common1@pdb2

CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 2;

CONN c##common_user/Common1@pdb3

CREATE TABLE c##common_user.common_user_tab AS
SELECT level AS ID
FROM   dual
CONNECT BY level <= 2;

-- Grant select on the local user's table to the common user.
CONN local_user/Local1@pdb1
GRANT SELECT ON local_user_tab TO c##common_user;

CONN local_user/Local1@pdb2
GRANT SELECT ON local_user_tab TO c##common_user;

CONN local_user/Local1@pdb3
GRANT SELECT ON local_user_tab TO c##common_user;

CONN / AS SYSDBA

CONTAINERS Clause with Common Users

The CONTAINERS clause can only be used from a common user in the root container. With no additional changes we can query the COMMON_USER_TAB tables present in the common user in all the containers. The most basic use of the CONTAINERS clause is shown below.

CONN c##common_user/Common1

SELECT *
FROM   CONTAINERS(common_user_tab);

     ID     CON_ID
---------- ----------
         1          4
         2          4
         1          5
         2          5
         1          3
         2          3

6 rows selected.

SQL>

Notice the CON_ID column has been added to the column list, to indicate which container the result came from. This allows us to query a subset of the containers.

SELECT con_id, id
FROM   CONTAINERS(common_user_tab)
WHERE  con_id IN (3, 4)
ORDER BY con_id, id;

CON_ID   ID
---------- ----------
         3          1
         3          2
         4          1
         4          2

4 rows selected.

SQL>

CONTAINERS Clause with Local Users

To query tables and views from local users, the documentation suggest you must create views on them from a common user. The following code creates views against the LOCAL_USER_TAB tables created earlier. We must also create a table in the root container with the same name as the views.

CONN c##common_user/Common1
CREATE TABLE c##common_user.local_user_tab_v (id NUMBER);

CONN c##common_user/Common1@pdb1
CREATE VIEW c##common_user.local_user_tab_v AS
SELECT * FROM local_user.local_user_tab;

CONN c##common_user/Common1@pdb2
CREATE VIEW c##common_user.local_user_tab_v AS
SELECT * FROM local_user.local_user_tab;

CONN c##common_user/Common1@pdb3
CREATE VIEW c##common_user.local_user_tab_v AS
SELECT * FROM local_user.local_user_tab;

With the blank table and views in place we can now use the CONTAINERS clause indirectly against the local user objects.

CONN c##common_user/Common1

SELECT con_id, id
FROM   CONTAINERS(local_user_tab_v)
ORDER BY con_id, id;

CON_ID    ID
---------- ----------
         3          1
         3          2
         4          1
         4          2
         5          1
         5          2

6 rows selected.

SQL>

The documentation suggests the use of synonyms in place of views will not work, since the synonyms must resolve to objects owned by the common user issuing the query.

"When a synonym is specified in the CONTAINERS clause, the synonym must resolve to a table or a view owned by the common user issuing the statement."

That's not quite true from my tests, but it doesn't stop you from using synonyms to local objects in the PDBs, provided the object in the root container is not a synonym. The following example uses a real object in the root container, and local objects via synonyms in the pluggable databases.

CONN c##common_user/Common1
CREATE TABLE c##common_user.local_user_tab_syn (id NUMBER);

CONN c##common_user/Common1@pdb1
DROP TABLE c##common_user.common_user_tab;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR local_user.local_user_tab;

CONN c##common_user/Common1@pdb2
DROP TABLE c##common_user.common_user_tab;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR local_user.local_user_tab;

CONN c##common_user/Common1@pdb3
DROP TABLE c##common_user.common_user_tab;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR local_user.local_user_tab;

CONN c##common_user/Common1

SELECT con_id, id
FROM   CONTAINERS(local_user_tab_syn)
ORDER BY con_id, id;

 CON_ID      ID
----------    ----------
         3          1
         3          2
         4          1
         4          2
         5          1
         5          2

6 rows selected.

SQL>

Let's see what happens if we drop the common user table and replace it with a synonym of the same name, pointing to a table of the same structure as the local tables, but owned by the common user.

CONN c##common_user/Common1

DROP TABLE c##common_user.local_user_tab_syn PURGE;

CREATE SYNONYM c##common_user.local_user_tab_syn FOR c##common_user.common_user_tab;

SELECT *
FROM   CONTAINERS(local_user_tab_syn);

SELECT *
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P004
ORA-00942: table or view does not exist

SQL>

If the synonyms consistently point to an object in the common user it still doesn't work.

CONN c##common_user/Common1@pdb1
DROP SYNONYM c##common_user.local_user_tab_syn;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR c##common_user.common_user_tab;
DESC local_user_tab_syn;

 Name      Null?    Type
 --------- -------- -----------------
 ID                      NUMBER

SQL>

CONN c##common_user/Common1@pdb2
DROP SYNONYM c##common_user.local_user_tab_syn;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR c##common_user.common_user_tab;
DESC local_user_tab_syn;

 Name     Null?    Type
 --------- -------- ----------------
 ID                      NUMBER

SQL>

CONN c##common_user/Common1@pdb3
DROP SYNONYM c##common_user.local_user_tab_syn;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR c##common_user.common_user_tab;
DESC local_user_tab_syn;

 Name     Null?    Type
 --------  -------- ----------------
 ID                      NUMBER

SQL>

CONN c##common_user/Common1
DROP SYNONYM c##common_user.local_user_tab_syn;
CREATE SYNONYM c##common_user.local_user_tab_syn FOR c##common_user.common_user_tab;
DESC local_user_tab_syn;

 Name     Null?    Type
 --------  --------  ----------------------
 ID                        NUMBER

SQL>

SELECT con_id, id
FROM   CONTAINERS(local_user_tab_syn)
ORDER BY con_id, id;

FROM   CONTAINERS(local_user_tab_syn)
                  *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL>