Monday, July 6, 2015

CDB_TABLESPACES not Showing Tablespaces of Pluggable Database

Starting 12c, along with DBA_*, ALL_* and USER_* views, there is another time of data dictionary views available which are CDB_* views. For example, along with DBA_TABLESPACES view there is also CDB_TABLESPACES view. These CDB_* views in a container database show all information of all containers (container + all pluggable databases)
in a container database where CON_ID column in these views display container id, to which each row of information belongs to. 

If we don’t see information of any of our pluggable database in CDB_* views, it would mean that pluggable database is not OPEN (or OPEN in restricted mode). Following is an example where we see that tablespaces of a pluggable database PDB1 are not listed in CDB_TABLESPACES view.
SQL> select con_id,tablespace_name from cdb_tablespaces order by 1;

    CON_ID TABLESPACE_NAME
---------- ------------------------------
         1 SYSTEM
         1 TEST
         1 SYSAUX
         1 TEMP
         1 UNDOTBS1
         1 USERS


Likewise, if we query CDB_DATAFILES or any other CDB_* view, we will not be able to see any information of our PDB1 database.
Please note that CDB_* views are also available in each pluggable database, but it contains information only about that particular pluggable database – only CDB_* views in container database contain information of all pluggable databases in the container.

In the following we can see how opening a pluggable database would make all its information available in CDB_* views
Information not available if pluggable database is open in restricted mode
SQL> alter pluggable database pdb1 open restricted;

Pluggable database altered.

SQL> select con_id,tablespace_name from cdb_tablespaces order by 1;

    CON_ID TABLESPACE_NAME
---------- ------------------------------
         1 SYSTEM
         1 TEST
         1 SYSAUX
         1 TEMP
         1 UNDOTBS1
         1 USERS

6 rows selected.

Opening pluggable database and querying CDB_* view
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> select name,open_mode,restricted from v$containers;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
CDB$ROOT                       READ WRITE NO
PDB$SEED                       READ ONLY  NO
PDB1                           READ WRITE YES

SQL> select con_id,tablespace_name from cdb_tablespaces order by 1;

    CON_ID TABLESPACE_NAME
---------- ------------------------------
         1 SYSTEM
         1 SYSAUX
         1 TEST
         1 TEMP
         1 USERS
         1 UNDOTBS1
         3 SYSTEM
         3 SYSAUX
         3 TEMP
         3 USERS

10 rows selected.



No comments:

Popular Posts - All Times