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?

use SELECT INSTANCE_NAME FROM V$INSTANCE; – Ahmed Emad Dec 23 '20 at 08:37 — Ahmed Emad, Dec 23 '20 at 08:37

score 173 · Accepted Answer · edited Feb 20 '20 at 06:39

173

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;

edited Feb 20 '20 at 06:39

Stefan van den Akker

6,661
7
48
63

answered Jun 09 '11 at 04:46

V4Vendetta

37,194
9
78
82

1

Thanks for quick response.Are instance and oracle SID same thing? – Adnan Jun 09 '11 at 04:49
2

@Adnan They need not be the same since there coould be multiple instances of the DB running on a single machine they are identified by SID – V4Vendetta Jun 09 '11 at 04:51
@adnan Did you get the values you needed ? – V4Vendetta Jun 09 '11 at 05:08
No. I want to get database name instead of current user and oracle sid instead of instance. – Adnan Jun 09 '11 at 05:20
14

`select sys_context('userenv','db_name') from dual;` for database name and sid i have already added in the answer. hope this gives you what you want – V4Vendetta Jun 09 '11 at 05:30
Thanks for your time. i have got database name but when i use select sys_context('userenv','SId') from dual i'm getting empty column,does it mean sid not set? If thats true then how can we set SID? – Adnan Jun 09 '11 at 05:48
2

Please check this [link](http://asktom.oracle.com/pls/asktom/f?p=100:11:1589451392620155::::P11_QUESTION_ID:318216852435) – V4Vendetta Jun 09 '11 at 06:04
@V4Vendetta When installing Oracle, I had to see my environment using `.bash_profile` which also contains the env var `ORACLE_SID`. If we configure it via env variables, doesn't it mean it will remain the same for every database that is created? – AjB Apr 07 '17 at 04:55
select sys_context('userenv','instance_name') from dual; worked for me while doing jpa definition to eclipse, thank you. – yılmaz Aug 10 '18 at 01:37
sys_context('USERENV', 'SID') is the session id which changes for every new connection, not what the OP was asking for. In this case SID seems to have an overloaded meaning, but what the OP needs is: SID = SELECT sys_context('userenv','instance_name') FROM dual; SERVICE_NAME = select value from v$parameter where name like '%service_name%'; – crowne Oct 21 '21 at 14:44

score 82 · Answer 2 · answered Dec 11 '13 at 17:01

82

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

answered Dec 11 '13 at 17:01

Sergio Marcelo C Figueiredo

1,857
17
24

Perfect. This is exactly what I wanted to know, but didn't know how to phrase. – Addison Jul 20 '16 at 07:13
Me too. Thanks Sergio! – BenW Apr 05 '17 at 22:31

score 53 · Answer 3 · answered Jun 14 '11 at 02:28

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

No special permissions are needed for the queries above. Verified by creating a new user with CONNECT privilege only. — bdeem, Aug 03 '15 at 15:18

score 27 · Answer 4 · answered Jun 09 '11 at 07:12

27

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.

answered Jun 09 '11 at 07:12

APC

144,005
19
170
281

Whats about oracle SID? Is there any method for checking it from scott account? – Adnan Jun 09 '11 at 08:59
1

That's the service name, not the SID. – jpmc26 Jul 16 '18 at 23:24

score 6 · Answer 5 · edited Jun 09 '15 at 03:12

6

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.

edited Jun 09 '15 at 03:12

Dhanuka

2,826
5
27
38

answered Jun 09 '15 at 02:13

anant kumar

59
1
1

Thanks, you made my day. – Muhammad Ashikuzzaman Nov 04 '15 at 10:25

score 3 · Answer 6 · answered Jun 09 '11 at 10:08

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

score 3 · Answer 7 · answered Oct 21 '21 at 14:56

SID appears to be an overloaded term in the Oracle environment. There's lots of answers on topic which say:
SID = SELECT sys_context('USERENV', 'SID') FROM DUAL;

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;

score 0 · Answer 8 · answered Sep 06 '21 at 13:10

0

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

answered Sep 06 '21 at 13:10

Fayazuddin

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). – Procrastinator Sep 07 '21 at 06:24

Checking oracle sid and database name

8 Answers8

Linked