How to find Oracle Service Name

Question

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.

@GauravSoni AFAIK `show parameter` is a SQL/Plus command, and I'm not sure whether SQL Developer supports a SQL/Plus prompt. If it doesn't, the OP could use `select * from v$parameter where name like '%service_name%'` instead. — Frank Schmitt, Mar 14 '14 at 08:46
"Show parameter service_name;" returns error "Show parameters query failed". — Anders Jakobsen, Mar 14 '14 at 08:48
"select value from v$parameter where name like '%service_name%';" returns ORA-00942: table or view does not exist — Anders Jakobsen, Mar 14 '14 at 08:48
@AndersJakobsen:you dint have access to the data dictionary views ,ask your DBA to give access or send you the service name .And may be sql developer support the sqlplus commands ,as toad also support that F5 is the command in toad to run such commands. — Gaurav Soni, Mar 14 '14 at 08:52
to select from V$PARAMETER (synonym) you need grant on V_$PARAMETER (view) — Marek-A-, Jun 28 '17 at 11:52

score 85 · Answer 1 · edited May 23 '17 at 12:03

85

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

select * from global_name;

edited May 23 '17 at 12:03

Community

1
1

answered Aug 23 '16 at 14:05

Goufalite

2,253
3
17
29

Many thanks, this one worked for me from Oracle SQL Developer. – Larry Smith Oct 20 '16 at 22:17
4

[GLOBAL_NAME](https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5102.htm#REFRN29019) gets you the global database name, which is not the same as the Service Name. (Although in some instances they could happen to be have the same value.) – sql_knievel Jul 13 '20 at 15:24

score 51 · Answer 2 · answered May 14 '15 at 18:42

51

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.

answered May 14 '15 at 18:42

user674669

10,681
15
72
105

2

for anyone who need some help please note that the url needs the sid and not the service name. The full service name didn't work for me: `jdbc:oracle:thin:@localhost:1521:orcl.athens.intrasoft-intl.private` while this worked: `jdbc:oracle:thin:@localhost:1521:orcl` – George Pligoropoulos Oct 06 '15 at 15:27
5

@GeorgePligor: Please note the difference: `jdbc:oracle:thin:@HOST:PORT:SID` but `jdbc:oracle:thin:@HOST:PORT/SERVICE` Replace the uppercase words by the correct values. Please note the difference between the two strings: in the sid string the name of the sid ist separated by a : from the preceding string, in the service string the name is separated by a / from the preceding string – miracle173 May 10 '17 at 16:07

Marmite Bomber · Accepted Answer · 2018-11-20T06:25:51.033

28

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;

edited Nov 20 '18 at 06:25

answered Jul 05 '18 at 14:27

Marmite Bomber

19,886
4
26
53

2

This just says `SYS$USERS` for me when I'm on the machine using SQL*Plus directly. – jpmc26 Nov 19 '18 at 17:26
@jpmc26 [SYS$USERS](http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref468) is the default service for user sessions that are not associated with services – Marmite Bomber Nov 19 '18 at 18:26
I gathered, but the fact it doesn't work when running on the same machine makes this answer much less useful than others. – jpmc26 Nov 19 '18 at 18:30
1

@jpmc26 extended the answer thaks to your suggestion. It is important to distinct the *connected* service and the *available* services. – Marmite Bomber Nov 19 '18 at 18:45

score 16 · Answer 4 · answered Mar 05 '15 at 16:17

16

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

answered Mar 05 '15 at 16:17

Carl Bosch

1,281
16
14

13

All it returns is SYS$USERS. I did eventually get in contact with the DBA, so this is no longer an issue – Anders Jakobsen Mar 09 '15 at 14:08
1

This actually did return the current Service_name as defined in the TNSNames for me. Have an upvote. – Marco Oct 01 '15 at 09:07

score 13 · Answer 5 · answered Nov 23 '15 at 10:58

13

Check the service name of a database by

sql> show parameter service;

answered Nov 23 '15 at 10:58

Dhyan Mohandas

1,126
11
12

4

`SHOW PARAMETERS;` for everything. – akki Jul 26 '16 at 12:23

score 4 · Answer 6 · answered Apr 10 '16 at 17:25

4

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

show parameter service_name

answered Apr 10 '16 at 17:25

Moiz Sajid

644
1
10
20

score -1 · Answer 7 · answered Oct 15 '19 at 06:02

-1

TO FIND ORACLE_SID USE $. oraenv

answered Oct 15 '19 at 06:02

Rahul Balu

1

score -5 · Answer 8 · answered Dec 02 '16 at 08:17

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.

How to find Oracle Service Name

8 Answers8

Linked