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.