DBA Hub

📋Steps in this guide1/5

Multitenant : Querying Container Data Objects (CONTAINER_DATA)

The visibility of the data for container data objects is controlled using the CONTAINER_DATA attribute of a common user.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Identifying Container Data Objects

You can identify container data objects by querying the and views, although there doesn't appear to any tables listed at this point. As mentioned previously, the container data objects include the , , , and some Automatic Workload Repository views.

Code/Command (click line numbers to comment):

1
2
3
4
5
CONN / AS SYSDBA

SELECT view_name FROM cdb_views WHERE container_data = 'Y';

SELECT table_name FROM cdb_tables WHERE container_data = 'YES';
2

Default Behaviour

Create a new common user and grant it the DBA role in all containers. We could grant privileges on an individual container data object if we wanted. Let's check the contents of the from new common user. We can see the output is limited to the datafiles associated with the current container. The root container. If we want to see the datafiles associated with the PDB1 instance, we have to connect to it.

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
CONN / AS SYSDBA

CREATE USER c##my_user IDENTIFIED BY MyPassword1;
GRANT CREATE SESSION, DBA TO c##my_user CONTAINER=ALL;

CONN c##my_user/MyPassword1@cdb1

SELECT name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/users01.dbf

SQL>

CONN c##my_user/MyPassword1@pdb1

SELECT name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/8664FAA157F47230E055000000000001/datafile/o1_mf_apex_gc329dg6_.dbf

SQL>
3

All Container Data Objects in All Containers

We can alter the default behaviour by changing the setting for the common user. In this first example we will allow the common user to see the container data objects for all PDBs, including those added in the future. We can see the configuration change reflected in the view. If we connect to the common user in the root container, we can now see the datafiles for all containers, including those of the seed. We can revert to the default functionality by setting the attribute back to value. We can see the configuration change reflected in the view.

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
CONN / AS SYSDBA

ALTER USER c##my_user SET CONTAINER_DATA=ALL CONTAINER=CURRENT;

SET LINESIZE 100
COLUMN username FORMAT A20
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A20
COLUMN container_name FORMAT A20

SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     Y

SQL>

CONN c##my_user/MyPassword1@cdb1

SELECT name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/pdbseed/system01.dbf
/u02/oradata/CDB1/pdbseed/sysaux01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdbseed/undotbs01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/8664FAA157F47230E055000000000001/datafile/o1_mf_apex_gc329dg6_.dbf

12 rows selected.

SQL>

CONN / AS SYSDBA

ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;

SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

no rows selected

SQL>
4

All Container Data Objects in Specific Container

In this example we limit the visibility to a specific pluggable database. The attribute must always contain the container. Connecting to the common user, we can test this by looking at the view again. Notice we now see the files for the root container and PDB1, but not the seed database. We can add and remove specific container names using the and clauses respectively. Using clause will overwrite any existing settings.

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
ALTER USER c##my_user SET CONTAINER_DATA=(CDB$ROOT,PDB1) CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N CDB$ROOT

SQL>

CONN c##my_user/MyPassword1@cdb1

SELECT name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u02/oradata/CDB1/system01.dbf
/u02/oradata/CDB1/sysaux01.dbf
/u02/oradata/CDB1/undotbs01.dbf
/u02/oradata/CDB1/users01.dbf
/u02/oradata/CDB1/pdb1/system01.dbf
/u02/oradata/CDB1/pdb1/sysaux01.dbf
/u02/oradata/CDB1/pdb1/undotbs01.dbf
/u02/oradata/CDB1/pdb1/users01.dbf
/u02/oradata/CDB1/8664FAA157F47230E055000000000001/datafile/o1_mf_apex_gc329dg6_.dbf

9 rows selected.

SQL>

ALTER USER c##my_user ADD CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N CDB$ROOT
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N PDB$SEED

SQL>


ALTER USER c##my_user REMOVE CONTAINER_DATA=(PDB$SEED) CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER                                                     N PDB1
C##MY_USER                                                     N CDB$ROOT

SQL>

ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

no rows selected

SQL>
5

Specific Container Data Objects

In the previous examples, we gave the common user access to all container data objects in some or all PDBs. If needed, we can limit it to individual objects. In the following example we allow the common user to see the contents of the view for all containers, when logged into the root container. Setting the top-level default attribute doesn't blank these object-specific attributes. We return the visibility to default by setting the object back to default for the user. For more information see: - Querying Container Data Objects - ALTER USER - Multitenant : All Articles 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
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
CONN / AS SYSDBA

ALTER USER c##my_user SET CONTAINER_DATA=(CDB$ROOT,PDB1) FOR sys.v_$datafile CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER           SYS                  V_$DATAFILE          N PDB1
C##MY_USER           SYS                  V_$DATAFILE          N CDB$ROOT

SQL>

ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

USERNAME             OWNER                OBJECT_NAME          A CONTAINER_NAME
-------------------- -------------------- -------------------- - --------------------
C##MY_USER           SYS                  V_$DATAFILE          N PDB1
C##MY_USER           SYS                  V_$DATAFILE          N CDB$ROOT

SQL>

ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT FOR sys.v_$datafile CONTAINER=CURRENT;


SELECT username,
       owner,
       object_name,
       all_containers,
       container_name
FROM   cdb_container_data
WHERE  username = 'C##MY_USER'
ORDER BY 1,2,3;

no rows selected

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!