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.

6

8 Answers

Found here, no DBA : Checking oracle sid and database name

select * from global_name;

2

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.

2

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;

4

Thanks to this thread ()

select sys_context('userenv','service_name') from dual;

It can be executed with a regular user account, no need for sysdba rights

2

Check the service name of a database by

sql> show parameter service;

1

Connect to the database with the "system" user, and execute the following command:

show parameter service_name 

TO FIND ORACLE_SID USE $. oraenv

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.

0

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy