In a previous post, we have seen the creation of Container database and pluggable database

Manually Create and configure CDB database and PDB database

In this post, we will explore data dictionary views of CDB and PDB.

We all are aware of DBA_, ALL_, and USER_ views.

 

  • USER_ views show information about user-specific metadata.
  • ALL_ views show information about user’s metadata and other users’ metadata for which user is granted privileges.
  • DBA_ views are super-set of all these views. It shows metadata regarding each user of the database.

In 12C one more level added to this that is CDB_ views.

  • CDB_xxx views shows information regarding container wide metadata. For each DBA_ view, there will be the corresponding view of CDB_ and this view contains all columns of DBA_ view and one extra column of CON_ID which shows associated container that can be either root container or any pluggable database.

Let us see one example.

DBA_DIRECTORIES view which we use to check directory information. Let us describe that.

SQL> desc DBA_DIRECTORIES;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(128)
 DIRECTORY_NAME 			   NOT NULL VARCHAR2(128)
 DIRECTORY_PATH 				    VARCHAR2(4000)
 ORIGIN_CON_ID					    NUMBER

Now I am describing CDB_DIRECTORIES.

SQL> desc CDB_DIRECTORIES;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER					   NOT NULL VARCHAR2(128)
 DIRECTORY_NAME 			   NOT NULL VARCHAR2(128)
 DIRECTORY_PATH 				    VARCHAR2(4000)
 ORIGIN_CON_ID					    NUMBER
 CON_ID 					    NUMBER

We can see CDB_DIRECTORIES have the same structure as DBA_DIRECTORIES just one column is added CON_ID.

Let us explore more

Step 1: Query DBA_DIRECTORIES view from CDB$ROOT container

SQL> select * from dba_directories;

OWNER  DIRECTORY_ DIRECTORY_PATH      ORIGIN_CON_ID
----- ---------- -------------------- -------------
SYS	 ORACLE_HOME /				          1
	 E

SYS	 ORACLE_BAS /				          1
	 E

SYS	 OPATCH_LOG /u01/oracle/product/	   1
	 _DIR	    12.1.0/db_1/QOpatch

SYS	 OPATCH_SCR /u01/oracle/product/	  1
	 IPT_DIR    12.1.0/db_1/QOpatch

OWNER DIRECTORY_ DIRECTORY_PATH	      ORIGIN_CON_ID
----- ---------- -------------------- -------------

SYS	 OPATCH_INS /u01/oracle/product/	  1
	 T_DIR	     12.1.0/db_1/OPatch

SYS	 DATA_PUMP_ /u01/oracle/admin/my	  1
	 DIR	    cdb/dpdump/

SYS	 XSDDIR     /u01/oracle/product/	  1
		       12.1.0/db_1/rdbms/xm
		       l/schema


7 rows selected.

Step 2 : Query DBA_DIRECTORIES from pluggable database .

SQL> select * from dba_directories;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID
-------- ---------- -------------------- -------------
SYS	 DATA_PUMP_ /u01/oracle/admin/my	     1
	 DIR	    cdb/dpdump/

SYS	 OPATCH_INS /u01/oracle/product/	     1
	 T_DIR	    12.1.0/db_1/OPatch

SYS	 OPATCH_SCR /u01/oracle/product/	     1
	 IPT_DIR    12.1.0/db_1/QOpatch

SYS	 OPATCH_LOG /u01/oracle/product/	     1
	 _DIR	    12.1.0/db_1/QOpatch

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID
-------- ---------- -------------------- -------------

SYS	 ORACLE_BAS /				     1
	 E

SYS	 ORACLE_HOM /				     1
	 E

SYS	 DEM	    /u02/			     3
SYS	 XSDDIR     /u01/oracle/product/	     3
		    12.1.0/db_1/rdbms/xm
		    l/schema

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID
-------- ---------- -------------------- -------------


8 rows selected.

Step 3 : Query CDB_DIRECTORIES from CDB$ROOT container :

SQL> select * from CDB_DIRECTORIES;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 DATA_PUMP_ /u01/oracle/admin/my	     1		3
	 DIR	    cdb/dpdump/

SYS	 OPATCH_INS /u01/oracle/product/	     1		3
	 T_DIR	    12.1.0/db_1/OPatch

SYS	 OPATCH_SCR /u01/oracle/product/	     1		3
	 IPT_DIR    12.1.0/db_1/QOpatch

SYS	 OPATCH_LOG /u01/oracle/product/	     1		3
	 _DIR	    12.1.0/db_1/QOpatch

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------

SYS	 ORACLE_BAS /				     1		3
	 E

SYS	 ORACLE_HOM /				     1		3
	 E

SYS	 DEM	    /u02/			     3		3
SYS	 XSDDIR     /u01/oracle/product/	     3		3
		    12.1.0/db_1/rdbms/xm
		    l/schema

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------

SYS	 XSDDIR     /u01/oracle/product/	     1		1
		    12.1.0/db_1/rdbms/xm
		    l/schema

SYS	 DATA_PUMP_ /u01/oracle/admin/my	     1		1
	 DIR	    cdb/dpdump/

SYS	 OPATCH_INS /u01/oracle/product/	     1		1
	 T_DIR	    12.1.0/db_1/OPatch


OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 OPATCH_SCR /u01/oracle/product/	     1		1
	 IPT_DIR    12.1.0/db_1/QOpatch

SYS	 OPATCH_LOG /u01/oracle/product/	     1		1
	 _DIR	    12.1.0/db_1/QOpatch

SYS	 ORACLE_BAS /				     1		1
	 E

SYS	 ORACLE_HOM /				     1		1
	 E

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------


15 rows selected.

Step 4 : Query CDB_DIRECTORIES from pluggable database MYPDB1:

SQL> select * from CDB_DIRECTORIES;

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 DEM	    /u02/			     3		3
SYS	 XSDDIR     /u01/oracle/product/	     3		3
		    12.1.0/db_1/rdbms/xm
		    l/schema

SYS	 DATA_PUMP_ /u01/oracle/admin/my	     1		3
	 DIR	    cdb/dpdump/

SYS	 OPATCH_INS /u01/oracle/product/	     1		3
	 T_DIR	    12.1.0/db_1/OPatch


OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------
SYS	 OPATCH_SCR /u01/oracle/product/	     1		3
	 IPT_DIR    12.1.0/db_1/QOpatch

SYS	 OPATCH_LOG /u01/oracle/product/	     1		3
	 _DIR	    12.1.0/db_1/QOpatch

SYS	 ORACLE_BAS /				     1		3
	 E

SYS	 ORACLE_HOM /				     1		3
	 E

OWNER	 DIRECTORY_ DIRECTORY_PATH	 ORIGIN_CON_ID	   CON_ID
-------- ---------- -------------------- ------------- ----------


8 rows selected.

SQL>

We have queries DBA_DIRECTORIES and CDB_DIRECTORIES from root container and pluggable database container.

In step 1 we have queried DBA_DIRECTORIES from root container which shows directories associated with Root container.

In step 2 we have queries DBA_DIRECTORIES from mypdb1 which shows all directories associated with Pluggable container mypdb1.

In step 3 we have queries CDB_DIRECTORIES from root container which shows directories of root container as well as all directories of all pluggable database we can see associated CON_ID in CDB_DIRECTORIES.

In step 4 we have queried CDB_DIRECTORIES from mypdb1 which shows same output as DBA_DIRECTORIES just one column more which shows CON_ID of MYPDB1 that is 3.

Stay tuned for More articles on Oracle Multitenant

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.