How to Get The Current Oracle Database Name?

How to Get The Current Oracle Database Name?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In Oracle, you can check the name of the current database using various methods. Here are multiple examples with their expected outputs and explanations:

Example 1: Using the GLOBAL_NAME View

SELECT * FROM GLOBAL_NAME;

Expected Output:

GLOBAL_NAME
-----------------
your_database_name

Explanation:

  • The GLOBAL_NAME view contains the global database name which is the full name of the database, typically consisting of the database name and the domain.

Example 2: Using the V$DATABASE View

SELECT NAME FROM V$DATABASE;

Expected Output:

NAME
-----------------
your_database_name

Explanation:

  • The V$DATABASE is a dynamic performance view that shows information about the database.
  • The NAME column contains the name of the database.

Example 3: Using the DBA_USERS View

SELECT DISTINCT ORACLE_USERNAME||'@'||DB_LINK AS DB_NAME FROM DBA_USERS;

Expected Output:

DB_NAME
-----------------
username@your_database_name

Explanation:

  • The DBA_USERS view lists information about all users in the database.
  • This query concatenates the ORACLE_USERNAME and DB_LINK to provide a pseudo database name; however, this method might not be reliable for an accurate database name and should be used with caution.

Example 4: Using the ORA_DATABASE_NAME Parameter

SELECT ORA_DATABASE_NAME FROM DUAL;

Expected Output:

ORA_DATABASE_NAME
-----------------
your_database_name

Explanation:

  • ORA_DATABASE_NAME is an environmental parameter that holds the name of the database.
  • DUAL is a dummy table provided by Oracle.

Example 5: Using SYS_CONTEXT

SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;

Expected Output:

SYS_CONTEXT('USERENV','DB_NAME')
---------------------------------
your_database_name

Explanation:

  • SYS_CONTEXT with the 'USERENV' namespace and 'DB_NAME' parameter returns the name of the database.
  • DUAL is a dummy table used in Oracle for selecting a single row.

Remember that the actual output will show the name of your database instead of your_database_name. Additionally, access to some of these views (V$DATABASE, DBA_USERS) may require elevated privileges, and you may need to log in as a user with the necessary rights or a DBA account.