How to Check Database Name in Oracle | 6 Tips

If you want to check database name in Oracle that you connected there are several ways to get it. I added 6 different methods to check it. Sometimes if you want to see details like USERNAME, SID, or DATABASE there are simple query can take about those details below I would like to show how to retrieve that information in

1. SQL Developer
   • sys_context
   • ora_database
   • global_name
   • V$database
2. SQL*Plus
3. Command Prompt

First, let’s see SQL Developer how to check database names in Oracle. There are 4 different ways to fetch database name

Using SQL Developer:

Method 1: Use sys_context parameter values to get the database name. similar to this query you can take information like server_host,instance_name, SID

--
select sys_context('userenv','db_name') from dual;
--

Method 2: use the ora_database_name parameter value

--
select ora_database_name from dual;
--

Method 3: global_name of system table mostly equal with database name

--
SELECT * FROM global_name;
--

Method 4: v$database can be used to get details about the database but you have to access to system view

--
select * from v$database;
--

Using SQL*Plus (Command Line):

1. Open a command prompt or terminal.
2. You have to Connect with the database using SQL*Plus:

--
sqlplus username/password@dbname
--

Then run the above query to check the connected database name in Oracle like SELECT * FROM global_name;

Using Command Prompt (Windows):

1. Open a command prompt.
2. Do the same as above to Connect to the database using SQL*Plus. Run the Oracle query like SELECT * FROM global_name; or one of the above 4 methods

For more information according to the Oracle docs the SID and database name for an Oracle database are ORCL, and the database file is located on the location ORACLE_BASE\oradata\orcl directory, also the initialization response file is available at ORACLE_BASE\admin\orcl\pfile directory.

Oracle has an easy way of checking connected database names. you can use any of the methods according to your database account privilege. If you can’t access some of the methods then check do you have the required privileges to access those properties.

Ref: https://www.oratable.com/find-your-database-name-in-oracle/