Checking oracle sid and database name


Oracle Problem Overview

I want to check SID and current database name.

I am using following query for checking oracle SID

select instance from v$thread;

but table or view does not exist error is coming.

I am using following query for checking current database name

select name from v$database;

but table or view does not exist error is coming.

Any idea for above two problems?

Oracle Solutions

Solution 1 - Oracle

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;

Solution 2 - Oracle

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


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

Solution 3 - Oracle

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;


SQL> select ora_database_name from dual;


SQL> select * from global_name;


Solution 4 - Oracle

The V$ views are mainly dynamic views of system metrics. They are used for performance tuning, session monitoring, etc. So access is limited to DBA users by default, which is why you're getting ORA-00942.

The easiest way of finding the database name is:

select * from global_name;

This view is granted to PUBLIC, so anybody can query it.

Solution 5 - Oracle

Type on sqlplus command prompt

SQL> select * from global_name;

then u will be see result on command prompt


Here first one "ORCL" is database name,may be your system "XE" and other what was given on oracle downloading time.

Solution 6 - Oracle

As has been mentioned above,

select global_name from global_name;

is the way to go.

You couldn't query v$database/v$instance/v$thread because your user does not have the required permissions. You can grant them (via a DBA account) with:

grant select on v$database to <username here>;

Solution 7 - Oracle

SID appears to be an overloaded term in the Oracle environment. There's lots of answers on topic which say:

However please note that this shows your current session_id, and changes for every new connection to the DB.

When referring to SID and current database name in the same question one can safely assume that the OP is trying to configure connections in tnsnames or elsewhere, and not trying to identify the session_id of a currently connected session.

Therefore in this context:
SID = SELECT sys_context('userenv','instance_name') FROM dual;

SERVICE_NAME = select sys_context('userenv','service_name') from dual;

Solution 8 - Oracle

SELECT sys_context('userenv','instance_name') FROM dual;


All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionAdnanView Question on Stackoverflow
Solution 1 - OracleV4VendettaView Answer on Stackoverflow
Solution 2 - OracleSergio Marcelo C FigueiredoView Answer on Stackoverflow
Solution 3 - OraclePatrick MarchandView Answer on Stackoverflow
Solution 4 - OracleAPCView Answer on Stackoverflow
Solution 5 - Oracleanant kumarView Answer on Stackoverflow
Solution 6 - OraclePhilView Answer on Stackoverflow
Solution 7 - OraclecrowneView Answer on Stackoverflow
Solution 8 - OracleFayazuddinView Answer on Stackoverflow