Oracle Data Dictionary

Oracle Data Dictionary

In this article, I am going to discuss Oracle Data Dictionary. Please read our previous article where we discussed Saving and Running Scripts in Oracle Database.

Oracle Data Dictionary

Data Dictionary is one of the most essential parts of the database.

Data Dictionary: It is metadata about the database. It shows us tables, views objects, and indexes. The data dictionary is structured in tables and views. This is maintained by the oracle database server. This is owned by the SYS user. We should not modify the data dictionary tables and views. This is a read-only set of tables that provides information about the database.

Structure of Oracle Data Dictionary:

Structure of Oracle Data Dictionary

CDB_: This is a data dictionary that shows all the objects in the container database which is CDB$ROOT and across all in the pluggable database. So, you can see all the objects in the container database as well as the pluggable databases. This CDB_ data dictionary is supported in the oracle releases 12c,18c,19c, 21c, and future releases.

DBA_: This Data dictionary shows all the objects in a database neither container nor pluggable database. Only DBA has privileges to view this data dictionary.

Both the CDB_ and DBA_ data dictionaries are restricted to only DBA privileges. There is a special privilege to check these data dictionaries. The privileges are SYSDBA privilege, select any dictionary privilege, and SELECT_CATALOG_ROLE.

ALL_ and USER_: The ALL_ and USER_ data dictionary is owned by the current user. Suppose if a user hr logged in to the database the user can see all the objects under his usernames like tables, views, objects, and indexes. If we create any new user, he directly has access to all_ and user_ data dictionary tables.

DBA_ Data Dictionary Exercise:

Log in to the Linux machine and start the database. Once the database is started open the SQL developer and login as “sys as sysdba” user in the SQL developer. Make sure the database is up and running while performing operations in SQL developer.

Note: You can open SQL developer on both local computers or else in the server as well. We are opening the SQL developer in the server machine. Login to SQL developer as SYS user

DBA_ Data Dictionary Exercise

Once you connect to the database open the worksheet where you can run the queries separately. Below is the worksheet where you can run and edit the queries.

Oracle Data Dictionary

So, everything is set. So, let’s try to run with the basic command “show con_name”.

Oracle Data Dictionary

We can see we are on the CDB$ROOT database. This indicates that currently, we have logged in to the container database. Let’s run a query that shows the list of pluggable databases present inside the database.

Query: select con_id,name,open_mode,creation_time from v$pdbs;

The data dictionary view V$pdbs shows all the pluggable databases present inside the container database. From this data dictionary were are selecting con_id, name, open_mode, and Creation_time columns from the data dictionary v$pdbs;

Oracle Data Dictionary

From the output, we can see that there are two pluggable databases. The CON_ID for the PDB$SEED is 2 and CON_ID for JAYAPDB is 3. The status of the pluggable database is JAYAPDB. We need to open this pluggable database in order to perform any operations and view the objects in the pluggable database.

Command: alter pluggable database JAYAPDB open;

Oracle Data Dictionary

So, the pluggable database JAYAPDB is open. Now, the pluggable database is also open, and let us run a query that shows the list of all the tables.

Query: select owner,table_name,con_id,tablespace_name from cdb_tables order by 1,2;

This query will show all the tables present in the pluggable database and the container database. Because the data dictionary cdb_tables contains all the tables in the pluggable database and the container database.

Oracle Data Dictionary

Right-click on any table you can see a popup select count rows in the option.

Oracle Data Dictionary

This option will show the total number of rows in the output.

Oracle Data Dictionary

So, it is clear that the total tables present in both the container and the pluggable databases are 4363 tables. If you see the output of the list of tables there are two rows for one table with different con_id(container_id).

Oracle Data Dictionary

Here the table WLM_CLASSIFIER_PLAN table has two rows one has con_id as 3 and the other con_id as 1. This indicates the output has tables of both the container database and the pluggable database. Now let’s try to close the pluggable database and try to check the count of the tables again using the same above query.

Command: alter pluggable database JAYAPDB close;

Oracle Data Dictionary

Note: Please ignore the error I have given the wrong database name.

Now the database is closed let’s try to run the same query again and check the count of the tables.

Query: select owner,table_name,con_id,tablespace_name from cdb_tables order by 1,2;

Oracle Data Dictionary

From the output, we see the con_id for the tables is 1. It indicates that the results are tables of the container database. In order to see the objects of the pluggable database while running any query with data dictionary cdb_ the pluggable database needs to be in the open state. Let’s open the database again.

Oracle Data Dictionary

Now the database is open, check the same query again to verify the count of table names.

Query: select owner,table_name,con_id,tablespace_name from cdb_tables order by 1,2;

Oracle Data Dictionary

After checking with the pluggable database the data dictionary cdb_ will count the tables present inside a pluggable database and a container database as well. In order to prove this, let us run another query to check the tables present in the container database and the pluggable database.

Query: select con_id, count(table_name) from cdb_tables group by con_id;

Oracle Data Dictionary

From the output you can the table count for con_id=1 is 2179 and the table count for con_id= 3 is 2184. Both examples define the data dictionary cdb_. There may be many other data dictionaries under cdb_ like cdb_tables, cdb_views, cdb_indexes, and cdb_users.

DBA_ Data Dictionary Exercise:

Let us now try to learn about the dba_ data dictionary. This data dictionary only displays the content of the database that we have logged in to. This data dictionary requires sysdba privilege. In order to prove this, let us take a sample query.

Query: select count(table_name) from dba_tables;

DBA_ Data Dictionary Exercise

This query shows the count of all the tables present inside the container database because we are currently in the container database. If we move to the pluggable database, it shows the count of the pluggable database. We can see 2179 tables in the CDB$ROOT container database. Now let’s move to the pluggable database and try running the same query to check the number of tables present inside the pluggable database.

DBA_ Data Dictionary Exercise

We have logged in to the pluggable database.

Query: select count(table_name) from dba_tables;

DBA_ Data Dictionary Exercise

We can see the count of tables is 2184 in the pluggable database JAYAPDB.

Note: Whenever we use the dba_ data dictionary it only shows all the objects that are present in the database that we have logged in.

Now let’s go back to the container database and check if the table count between the pluggable database and the container database is equal or not.

DBA_ Data Dictionary Exercise

We are using the command “show con_name” to verify whether we have logged in to the same container database or else we are left in a pluggable database.

DBA_ Data Dictionary Exercise

We have logged in to the container database. Let us run the same query to check the count of tables present inside the container database.

Query: select count(table_name) from dba_tables;

DBA_ Data Dictionary Exercise

There is a difference between both the pluggable database and the container database tables.

Note: The other two data dictionaries all_ & user_ are the data dictionaries when the user is logged in. A PL/SQL developer would be aware of these data dictionaries.

In the next article, I am going to discuss Common Users vs Local Users in Oracle Database. Here, in this article, I try to explain Oracle Data Dictionary and I hope you enjoy this Oracle Data Dictionary article.

Leave a Reply

Your email address will not be published. Required fields are marked *