DBA Hub

📋Steps in this guide1/6

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 in Oracle Database 12c (12.1.0.2 and 12.2).

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Setup

We need to create 3 PDBs to test the clause. The setup code below does the following. - Creates a pluggable database called . - Creates a with a local user called that owns a populated table called . - 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 clause or the parameter. The next part of the setup does the following. - Creates a common user called that owns an empty table called in the root container. - Creates a populated version of the table owned by the user in each PDB. - Grants select privilege on the local user's table to the common user.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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;

-- 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
2

CONTAINERS Clause with Common Users

The clause can only be used from a common user in the root container. With no additional changes we can query the tables present in the common user in all the containers. The most basic use of the CONTAINERS clause is shown below. Notice the 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. We can also perform DML using the clause. In the examples below, we must specify the column for the DML action to be performed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
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>

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>

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>
3

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 tables created earlier. We must also create a table in the root container with the same name as the views. With the blank table and views in place we can now use the clause indirectly against the local user objects.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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;

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

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. This also allows us to perform DML against the local user tables. 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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>

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>

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>
5

CONTAINERS Hint (12.2)

Oracle database 12.2 introduced the hint, allowing you an element of control over the recursive SQL statements executed as a result of using the 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>>". As an example, we will run a query against the view and check the elapsed time. We repeat the query, but this time add a hint to the recursive queries run in each PDB, which should make the elapsed time slower on this small VM. Notice the significantly longer elapsed time as a result of the parallel operations in the recursive SQL..

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
/*+ CONTAINERS(DEFAULT_PDB_HINT='<<PUT-HINT-HERE>>') */

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>

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>
6

Clean Up

You can clean up all the pluggable databases and the common user created for these examples using the following script. For more information see: - Multitenant : All Articles - PDB CONTAINERS Clause - CONTAINERS Hint Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- !!! 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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!