8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- CONTAINERS Clause with Common Users
- CONTAINERS Clause with Local Users
- CONTAINERS Clause with Local Users and Synonyms
- CONTAINERS Hint (12.2)
- Clean Up
Related articles.
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 calledLOCAL_USER
that owns a populated table calledLOCAL_USER_TAB
. - Creates two clones of PDB1 called PDB2 and PDB3.
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.
- Creates a common user called
C##COMMON_USER
that owns an empty table calledCOMMON_USER_TAB
in the root container. - Creates a populated version of the
COMMON_USER_TAB
table owned by theC##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 <= 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...