V$DATABASE/ V$INSTANCE in Oracle

Back to: Oracle DBA Tutorials

V$DATABASE/ V$INSTANCE in Oracle Database with Examples

In this article, I am going to discuss V$DATABASE/ V$INSTANCE in Oracle Database with Examples. Please read our previous article where we discussed Query Data Files and Temp Files in Oracle.

V$DATABASE/ V$INSTANCE in Oracle

In this article, we will try to learn about querying the database name and the database version. We have already learned about databases starting from 12c,18c,19c, and 21c are multitenant architecture databases.

We have a container database name CDB. For example, the container database is like a bucket. The bucket takes as many databases as it fits. Every Container database has the following containers:

1. Exactly one CDB root container called CDB$ROOT
2. Seed PDB called PDB$SEED
3. One or more user-created PDBs

So, in the bucket container database, we have one seed database and other container databases like PDB1 and PDB2. The container_id for the bucket is 0. The container id for the CDB$ROOT database is 1. The container id for PDB$SEED is 2 and for PDB1 is 3 and for PDB2 is 4.

Examples to understand V$DATABASE/ V$INSTANCE in Oracle:

Please start your Linux virtual machine and start the database and open the pluggable database for better practice. Open SQL Developer and connect to the database as sysdba. Once you are connected to the database using the command “show con_name” to check the database name.

So, we are in the CDB$ROOT database. Now let us try to check the information in the database. There is a query to check the information about the database using the data dictionary V$database.

Query: select name,cdb,con_id,open_mode from v$database;

From the data dictionary v$database, we can view all the information regarding the database. From the results, we can see the name of the database is JAYADB and the CDB is YES means it is a container database. The con_id is taken as 0 and OPEN_MODE is READ WRITE. There is another way to check the database name.

Command: show parameter db_name

In the output, you can see the db_name is mentioned as JAYADB. We have created a 19c database so, we knew that is 19c. What if some new user tries to log in to the database and needs to check the version of the database? There is a query to check the version of the database.

Query: select banner from v$version;

We can see the version mentioned as Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production. We have seen the version of the database name. Let’s try to check the instance name using another query. These queries will help during the DBA work. We already discussed that instance_name is equal to database_name if we are using a single instance and a single database.

Query: select * from v$instance;

There is a lot of information in this query. It displays the oracle version of the database, hostname, startup time, status of the database, and much more information. You can go through this information once you execute this query. There is another command to check the instance name of the database in the SQL developer is “show parameter instance_name”

This also displays the same instance_name but with less information not including any other information. In the next article you will learn about how to query the container databases using the data dictionary V$database and V$instance.

In the next article, I am going to discuss V$Containers/cdb_pdbs in Oracle Database. Here, in this article, I try to explain V$DATABASE/ V$INSTANCE in Oracle Database and I hope you enjoy this V$DATABASE/ V$INSTANCE in the Oracle article.