Data Dictionary Differences inside a Pluggable Database in Oracle

Back to: Oracle DBA Tutorials

Data Dictionary Differences inside a Pluggable Database in Oracle

In this article, I am going to discuss Data Dictionary Differences inside a Pluggable Database in Oracle. Please read our previous article where we discussed Dynamic Performance Views in Oracle.

Data Dictionary Differences Inside a Pluggable Database

We are going to understand the data dictionary. We will see the differences between cdb_ and the dba_ data dictionaries and how these data dictionaries behave in the pluggable database. Open the Linux machine and log in as an oracle user. Once the database is started. Connect to the database.

Currently, we are on the root database. So, now we will execute a select statement query to extract the count of tables using the cdb_ data dictionary.

Query: select count(1) from cdb_tables;

We can see the count is 2179 tables. Previously when we executed the same query, we got a greater number of tables but now we have 2179 tables. This is because the pluggable database is off. Let’s run a query to check the status of the pluggable databases.

Query: select name,open_mode from v$pdbs;

We can see the pluggable database JAYAPDB is mounted. Let’s run the command to open the pluggable database.

Command: alter pluggable database JAYAPDB open;

Now the pluggable database is open let’s run the same count query again to check the count of tables.

Query: select count(1) from cdb_tables;

Now we can see 4363 tables across all the containers. Let us run the same query by moving from the container database to the pluggable database.

Command: alter session set container=JAYAPDB.

Now we are in the pluggable database. Let’s check again.

If we run the same query, we used to check the count of tables in the pluggable it does not show the count as 4363. It only retrieves the count of the pluggable database JAYAPDB. Let us run the query and check again.

Query: select count(1) from cdb_tables;

We can see the count as 2184. This indicates that if we are going to the cdb_tables data dictionary then we need to connect to the root database and if we connect to the pluggable database and run the cdb_tables data dictionary it only retrieves the tables present in the database that has logged in. Now, let us try to connect back to the container database and run the query again.

Query: Alter session set container =CDB$ROOT;

Note: We understood that if we need to use the cdb_ data dictionary just works in the root database and does not work in the other pluggable database.

In the next article, I am going to discuss Query Data Files and Temp Files in Oracle Database. Here, in this article, I try to explain Data Dictionary Differences inside a Pluggable Database in Oracle and I hope you enjoy this Data Dictionary Differences inside a Pluggable Database in the Oracle article.