In a previous post, we have seen the creation of Container database and pluggable 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
