Categories: SQL Tutorials

Oracle Database Name | How to Find oracle database name?

In my previous articles I have clearly given you idea about different systems tables in oracle with its use.In this article I would like to give you information about Oracle Database Name and Finding the Database Name in detail. The main purpose of this article is to find out your oracle database name with easy steps. These steps are really very important in day to day database activities. I would like to explain multiple ways for finding out the database name for which you are connected.

Type 1 : Finding Oracle Database name using System View named V$Database

The first method to find out oracle database name is using system views. There are so many types of system views in Oracle. The view named V$database is used to find out the database details from the oracle. If you are using Oracle SQL developer or Toad to connect with your oracle database you need to use the select query to find out the name of the connected database.

There are so many columns in V$Database view. Kindly use following steps to find the database name using V$Database view.

Prerequisite information :

I would like to mention prerequisite to use this query. User needs to have the permission to access V$database view. Mainly the user with DBA privileges will access this view.

If you don’t have access to the view kindly run following query :

Grant select on V$Views;

Step 1 : Describe V$database view to see the columns

Query : Desc V$database

Step 2 : You need to find out the column name for database. There is column named ‘Name’ in V$database view. So you can use following query to find out the database name which is connected.

Select name from V$Database;

Output :

Complexsql

The Complexsql is the database name on which the user is connected.

Type 2 : With Using Global_name table

The second method for finding out database name is using global_name system table. The global_name table contains the default value of database name and database domain together. The Global_name table is publicly accessible to any database user logged in to the database.

The following query is used to find out Oracle Database name using Global_name.

Query:

Select * from Global_name;

Output:

Complexsql

The database name is Complexsql to which user is connected.

Type 3 : Using the dbms utility

The third type to get database name is using the dbms utility.The oracle system function get_parameter_value from dbms_utility package will give you the database name. To do this user need to create one PLSQL procedure.

PLSQL Procedure :

Declare

V_no number(30);

V_database_name varchar2(30);

Begin

:V_no:=dbms_utility.get_parameter_value(‘db_name’,:V_no,:V_database_name );

End;

If you can Print the V_database_name it will give you the database name.

Print V_database_name;

Output :

Complexsql

Type 4 : Using dual table of Oracle

User can find out the database name using dual table.

Query:

select ora_database_name from dual;

Output:

Complexsql

The complexsql is the database name on which user is been connected.

These are multiple ways to find out the database name in Oracle. I hope you like this article. If you like this article or if you have any suggestions with this article kindly comments in comments section.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.

Recent Posts

What is Root Cause Analysis (RCA) With real examples

In my previous article I have given details about application support engineer day to day…

1 hour ago

Application Support Engineer Day to day responsibilities

In my previous articles I have given the roles and responsibilities of L1,L2 and L3…

2 days ago

What is mean by SLA ( Service Level Agreement) with Examples?

In my previous articles i have given the hierarchy of production support in real company…

5 days ago

What is Production support Hierarchy in organization?

In this article i would like to provide information about production support organization structure or…

5 days ago

What are roles and responsibilities for L3 Support Engineer?

In my previous article I have given roles for L1 and L2 support engineer with…

5 days ago

What are roles and responsibilities of L2 Engineer?

I have started this new series of how to become application support engineer. This article…

5 days ago