8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c (12.1.0.2 and 12.2)

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

Related articles.

Setup

We need to create 3 PDBs to test the CONTAINERS clause. The setup code below does the following.

These examples use Oracle Managed Files (OMF). If you are not using OMF you will need to handle the file conversions manually using the FILE_NAME_CONVERT clause or the PDB_FILE_NAME_CONVERT parameter.

CONN / AS SYSDBA

-- Create a pluggable database
CREATE PLUGGABLE DATABASE pdb1
  ADMIN USER pdb_admin IDENTIFIED BY Password1
  DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

ALTER PLUGGABLE DATABASE pdb1 OPEN;

ALTER SESSION SET CONTAINER = pdb1;

-- 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 <= 4;


CONN c##common_user/Common1@pdb3

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


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

CONN local_user/Local1@pdb2
GRANT SELECT, INSERT, UPDATE, DELETE ON local_user_tab TO c##common_user;

CONN local_user/Local1@pdb3
GRANT SELECT, INSERT, UPDATE, DELETE 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          7
         2          7
         3          7
         4          7
         1          6
         2          6
         1          8
         2          8
         3          8
         4          8
         5          8
         6          8


12 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 (7, 8)
ORDER BY con_id, id;

    CON_ID         ID
---------- ----------
         7          1
         7          2
         7          3
         7          4
         8          1
         8          2
         8          3
         8          4
         8          5
         8          6

10 rows selected.

SQL>

We can also perform DML using the CONTAINERS clause. In the examples below, we must specify the CON_ID column for the DML action to be performed.

INSERT INTO CONTAINERS(common_user_tab) (con_id, id) VALUES (6, 10);
INSERT INTO CONTAINERS(common_user_tab) (con_id, id) VALUES (7, 10);
INSERT INTO CONTAINERS(common_user_tab) (con_id, id) VALUES (8, 10);
COMMIT;

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

    CON_ID         ID
---------- ----------
         6          1
         6          2
         6         10
         7          1
         7          2
         7          3
         7          4
         7         10
         8          1
         8          2
         8          3
         8          4
         8          5
         8          6
         8         10

15 rows selected.

SQL>

UPDATE CONTAINERS(common_user_tab) SET id = 11 WHERE con_id = 6 AND id = 10;
UPDATE CONTAINERS(common_user_tab) SET id = 11 WHERE con_id = 7 AND id = 10;
UPDATE CONTAINERS(common_user_tab) SET id = 11 WHERE con_id = 8 AND id = 10;
COMMIT;

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

    CON_ID         ID
---------- ----------
         6          1
         6          2
         6         11
         7          1
         7          2
         7          3
         7          4
         7         11
         8          1
         8          2
         8          3
         8          4
         8          5
         8          6
         8         11

15 rows selected.

SQL>

DELETE FROM CONTAINERS(common_user_tab) WHERE con_id = 6 AND id = 11;
DELETE FROM CONTAINERS(common_user_tab) WHERE con_id = 7 AND id = 11;
DELETE FROM CONTAINERS(common_user_tab) WHERE con_id = 8 AND id = 11;
COMMIT;

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

    CON_ID         ID
---------- ----------
         6          1
         6          2
         7          1
         7          2
         7          3
         7          4
         8          1
         8          2
         8          3
         8          4
         8          5
         8          6

12 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
---------- ----------
         6          1
         6          2
         7          1
         7          2
         8          1
         8          2

6 rows selected.

SQL>

CONTAINERS Clause with Local Users and Synonyms

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
---------- ----------
         6          1
         6          2
         7          1
         7          2
         8          1
         8          2

6 rows selected.

SQL>

This also allows us to perform DML against the local user tables.

INSERT INTO CONTAINERS(local_user_tab_syn) (con_id, id) VALUES (6, 10);
INSERT INTO CONTAINERS(local_user_tab_syn) (con_id, id) VALUES (7, 10);
INSERT INTO CONTAINERS(local_user_tab_syn) (con_id, id) VALUES (8, 10);
COMMIT;

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

    CON_ID         ID
---------- ----------
         6          1
         6          2
         6         10
         7          1
         7          2
         7         10
         8          1
         8          2
         8         10

9 rows selected.

SQL>

UPDATE CONTAINERS(local_user_tab_syn) SET id = 11 WHERE con_id = 6 AND id = 10;
UPDATE CONTAINERS(local_user_tab_syn) SET id = 11 WHERE con_id = 7 AND id = 10;
UPDATE CONTAINERS(local_user_tab_syn) SET id = 11 WHERE con_id = 8 AND id = 10;
COMMIT;

    CON_ID         ID
---------- ----------
         6          1
         6          2
         6         11
         7          1
         7          2
         7         11
         8          1
         8          2
         8         11

9 rows selected.

SQL>

DELETE FROM CONTAINERS(local_user_tab_syn) WHERE con_id = 6 AND id = 11;
DELETE FROM CONTAINERS(local_user_tab_syn) WHERE con_id = 7 AND id = 11;
DELETE FROM CONTAINERS(local_user_tab_syn) WHERE con_id = 8 AND id = 11;
COMMIT;

    CON_ID         ID
---------- ----------
         6          1
         6          2
         7          1
         7          2
         8          1
         8          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;


-- Result from 12.1.0.2
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>


-- Result from 18c.
SELECT *
FROM   CONTAINERS(local_user_tab_syn);

        ID     CON_ID
---------- ----------
         1          7
         2          7
         1          6
         2          6
         1          8
         2          8

6 rows selected.

SQL>

This gives and error in 12.1.0.2, but works fine in Oracle 18c. I'm not sure what the wording in the documentation means, but it doesn't read well to me. You need to do some tests before you pick an approach.

CONTAINERS Hint (12.2)

Oracle database 12.2 introduced the CONTAINERS hint, allowing you an element of control over the recursive SQL statements executed as a result of using the CONTAINERS clause.

The hint is placed in the select list as usual, with the basic syntax as follows. Substitute the hint you want in place of "<<PUT-HINT-HERE>>".

/*+ CONTAINERS(DEFAULT_PDB_HINT='<<PUT-HINT-HERE>>') */

As an example, we will run a query against the ALL_OBJECTS view and check the elapsed time.

CONN / AS SYSDBA

SET TIMING ON

SELECT con_id, MAX(object_id)
FROM   CONTAINERS(all_objects)
GROUP BY con_id
ORDER BY 1;

    CON_ID MAX(OBJECT_ID)
---------- --------------
         1          75316
         3          73209
         4          73330
         5          73323

Elapsed: 00:00:00.31
SQL>

We repeat the query, but this time add a PARALLEL(2) hint to the recursive queries run in each PDB, which should make the elapsed time slower on this small VM.

SELECT /*+ CONTAINERS(DEFAULT_PDB_HINT='PARALLEL(2)') */
       con_id, MAX(object_id)
FROM   CONTAINERS(all_objects)
GROUP BY con_id
ORDER BY 1;

    CON_ID MAX(OBJECT_ID)
---------- --------------
         1          75316
         3          73209
         4          73340
         5          73323

Elapsed: 00:00:06.17
SQL>

Notice the significantly longer elapsed time as a result of the parallel operations in the recursive SQL..

Clean Up

You can clean up all the pluggable databases and the common user created for these examples using the following script.

-- !!! Double-check you really need to do all these steps !!!

CONN / AS SYSDBA

-- Clean up the user if you want to repeat any steps.
DROP USER c##common_user CASCADE;


ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb2 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb1 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE pdb2 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

DROP USER c##common_user CASCADE;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.