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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google