How to find Oracle Service Name
OracleOracle Problem Overview
I have an Oracle database on my network which I am able to connect to with Oracle SQL Developer, using hostname, port, username, password and the SID.
I need to connect another application (Quantum Gis), however it uses the Service Name instead of the SID.
Is there any way for me to determine the SERVICE_NAME of the database when I am connected in SQL Developer via the SID?
I do not have access to the server and have no local tnsnames.ora or similar.
Oracle Solutions
Solution 1 - Oracle
Found here, no DBA : https://stackoverflow.com/questions/6288122/checking-oracle-sid-and-database-name
select * from global_name;
Solution 2 - Oracle
Connect to the server as "system" using SID. Execute this query:
select value from v$parameter where name like '%service_name%';
It worked for me.
Solution 3 - Oracle
Overview of the services used by all sessions provides the distionary view v$session
(or gv$session
for RAC databases) in the column SERVICE_NAME
.
To limit the information to the connected session use the SID from the view V$MYSTAT
:
select SERVICE_NAME from gv$session where sid in (
select sid from V$MYSTAT)
If the name is SYS$USERS
the session is connected to a default service, i.e. in the connection string no explicit service_name was specified.
To see what services are available in the database use following queries:
select name from V$SERVICES;
select name from V$ACTIVE_SERVICES;
Solution 4 - Oracle
Thanks to this thread (https://community.oracle.com/thread/473276)
select sys_context('userenv','service_name') from dual;
It can be executed with a regular user account, no need for sysdba rights
Solution 5 - Oracle
Check the service name of a database by
sql> show parameter service;
Solution 6 - Oracle
Connect to the database with the "system" user, and execute the following command:
show parameter service_name
Solution 7 - Oracle
TO FIND ORACLE_SID USE $. oraenv
Solution 8 - Oracle
With SQL Developer you should also find it without writing any query. Right click on your Connection/Propriety.
You should see the name on the left under something like "connection details" and should look like "Connectionname@servicename", or on the right, under the connection's details.