Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c Release 1 (12.1.0.2)

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.

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.

-- 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."

This does not appear to be the case, as shown in the following example.

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

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

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

CONN c##common_user/Common1@pdb3
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>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.