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?
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;
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 wantIf, 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
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.
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;
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.
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>;
SELECT sys_context('userenv','instance_name') FROM dual;
