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:

Multitenant : All Articles

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

Back to the Top.