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

User · Answer

Type on sqlplus command prompt   SQL gt  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

User · Answer

If  like me  your goal is get the database host and SID to generate a Oracle JDBC url  as   jdbc oracle thin   lt server host gt  1521  lt instance name gt    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

User · Answer

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  lt username here gt

User · Answer

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 gt  select sys context  userenv   db name   from dual   SYS CONTEXT  USERENV   DB NAME   -------------------------------------------------------------------------------- orcl  SQL gt  select ora database name from dual   ORA DATABASE NAME -------------------------------------------------------------------------------- ORCL XYZ COM  SQL gt  select   from global name   GLOBAL NAME -------------------------------------------------------------------------------- ORCL XYZ COM

User · Answer

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

User · Answer

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

[oracle] Checking oracle sid and database name

Examples related to oracle

Examples related to oracle10g