Checking oracle sid and database name

If, like me, your goal is get the database host and SID to generate a Oracle JDBC url, as

jdbc:oracle:thin:@<server_host>:1521:<instance_name>

the following commands will help:

Oracle query command to check the SID (or instance name):

select sys_context('userenv','instance_name') from dual; 

Oracle query command to check database name (or server host):

select sys_context('userenv', 'server_host') from dual;

Att. Sergio Marcelo

Just for completeness, you can also use ORA_DATABASE_NAME.

It might be worth noting that not all of the methods give you the same output:

SQL> select sys_context('userenv','db_name') from dual;

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

SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.XYZ.COM

I presume SELECT user FROM dual; should give you the current user

and SELECT sys_context('userenv','instance_name') FROM dual; the name of the instance

I believe you can get SID as SELECT sys_context('USERENV', 'SID') FROM DUAL;