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.

Now I am describing CDB_DIRECTORIES.

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

Step 2 : Query DBA_DIRECTORIES from pluggable database .

Step 3 : Query CDB_DIRECTORIES from CDB$ROOT container :

Step 4 : Query CDB_DIRECTORIES from pluggable database MYPDB1:

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.