Checking oracle sid and database name

Question

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?

Answer 1

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;

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

Type on sqlplus command prompt

SQL> select * from global_name;

then u will be see result on command prompt

SQL ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM

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

Answer 6

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>;