8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Querying Container Data Objects (CONTAINER_DATA)
When using the multitenant architecture the visibility of information across the containers is a little more complicated than that of a non-CDB instance. Some object are considered "container data objects", including the V$
, GV$
, CDB_
, and some Automatic Workload Repository DBA_HIST*
views. The visibility of the data for container data objects is controlled using the CONTAINER_DATA
attribute of a common user.
The CONTAINER_DATA
attribute of a common user is modified using the ALTER USER
command. The syntax is described below.
ALTER USER user-name { SET CONTAINER_DATA = { ALL | DEFAULT | ( container_name [, container_name ]... ) } | ADD CONTAINER_DATA = ( container_name [, container_name ]... ) | REMOVE CONTAINER_DATA = ( container_name [, container_name ]... ) } [ FOR [ schema. ] container_data_object ];
This article shows some simple examples of setting the CONTAINER_DATA
attribute.
Remember, this is just about visibility of objects. It doesn't actually alter any objects themselves.
- Identifying Container Data Objects
- Default Behaviour
- All Container Data Objects in All Containers
- All Container Data Objects in Specific Container
- Specific Container Data Objects
For more information see:
Identifying Container Data Objects
You can identify container data objects by querying the {CDB|DBA|ALL|USER}_VIEWS
and {CDB|DBA|ALL|USER}_TABLES
views, although there doesn't appear to any tables listed at this point.
CONN / AS SYSDBA SELECT view_name FROM cdb_views WHERE container_data = 'Y'; SELECT table_name FROM cdb_tables WHERE container_data = 'YES';
As mentioned previously, the container data objects include the V$
, GV$
, CDB_
, and some Automatic Workload Repository DBA_HIST*
views.
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.
CONN / AS SYSDBA CREATE USER c##my_user IDENTIFIED BY MyPassword1; GRANT CREATE SESSION, DBA TO c##my_user CONTAINER=ALL;
Let's check the contents of the V$DATAFILE
from new common user.
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>
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.
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>
All Container Data Objects in All Containers
We can alter the default behaviour by changing the CONTAINER_DATA
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.
CONN / AS SYSDBA ALTER USER c##my_user SET CONTAINER_DATA=ALL CONTAINER=CURRENT;
We can see the configuration change reflected in the CDB_CONTAINER_DATA
view.
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>
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.
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>
We can revert to the default functionality by setting the CONTAINER_DATA
attribute back to DEFAULT
value.
CONN / AS SYSDBA ALTER USER c##my_user SET CONTAINER_DATA=DEFAULT CONTAINER=CURRENT;
We can see the configuration change reflected in the CDB_CONTAINER_DATA
view.
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>
All Container Data Objects in Specific Container
In this example we limit the visibility to a specific pluggable database. The CONTAINER_DATA
attribute must always contain the CDB$ROOT
container.
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>
Connecting to the common user, we can test this by looking at the V$DATAFILE
view again. Notice we now see the files for the root container and PDB1, but not the seed database.
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>
We can add and remove specific container names using the ADD CONTAINER_DATA
and REMOVE CONTAINER_DATA
clauses respectively.
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>
Using SET CONTAINER_DATA
clause will overwrite any existing settings.
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>
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 V$DATAFILES
view for all containers, when logged into the root container.
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>
Setting the top-level default attribute doesn't blank these object-specific attributes.
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>
We return the visibility to default by setting the object back to default for the user.
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>
For more information see:
Hope this helps. Regards Tim...