Oracle SQL to Check the Database / Instance Name to Which the Current Session Is Connected using a Non DBA user

There are couple of options to find out the database name for the current session. If you are a DBA a privileged user/session you can query any data dictionary views. But it is not the same case for non-dba user session.

Here are the options.

Non DBA user session

SQL> SELECT SYS_CONTEXT ('USERENV', 'DB_NAME') dbname FROM DUAL;

DBNAME

------------------------------------

ORCL

SQL> SELECT SYS_CONTEXT ('USERENV', 'INSTANCE_NAME') instance from dual;

 

INSTANCE

------------------------------------

ORCL

DBA user session

 

SQL> select name from v$database;

NAME

---------

ORCL

SQL> select * from global_name; 

GLOBAL_NAME

----------------------------------------

ORCL 

SQL> select instance_name from v$instance; 

INSTANCE_NAME

----------------

ORCL