SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

SQL-Developer error:ORA-12505. TNS:listner does not currently know of SID given in connect desc

Nader HAug 11 2014 — edited Aug 11 2014

Hello guys, i know this question has been posted many times in the past but my issue includes sql oracle developer only. i can use the database and all features through sql*plus even execute sql queries. but when it comes to sql developer it will always return this error. (error:ORA-12505.)

windows 8 -64 oracle 11g r2

--------------------------------------------------------------------------------------------------------------------------------------

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER1

Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production

Start Date 11-AUG-2014 13:08:08

Uptime 0 days 0 hr. 14 min. 55 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora

Listener Log File c:\app\oracle\diag\tnslsnr\Needo-pc\listener1\alert\log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...

Service "orcl" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

-----------------------------------------------------------------------------------------------------------------

LSNRCTL> services

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0

LOCAL SERVER

"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

CLRExtProc

(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_122C_C6D0FCF3.ORA))

"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready

CLRExtProc

(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\NTN_122C_C6D0FCF3.ORA))

Service "orcl" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:22 refused:0 state:ready

LOCAL SERVER

Service "orclXDB" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER <machine: NEEDO-PC, pid: 3172>

(ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=52654))

The command completed successfully

------------------------------------------------------------------------------------------

thanks!

This post has been answered by Sven W. on Aug 11 2014

Jump to Answer

thatJeffSmith-Oracle

You didn't share your connection properties...

Nader H

can you elaborate ??

thatJeffSmith-Oracle

We need you to elaborate.

How are you connecting from SQL*Plus?

How are you defining your connection in SQL Developer?

Nader H

its all local, installation was implemented on a laptop for training purposes.

sql plus is accessed through cmd, typed sqlplus then i enter username (hr) and password (****) with no complications. i can retrieve row and columns as well

as for oracle sql developer same username and password are used.

hostname: localhost

port 1521 (as set through net configuration)

sid: xe

Nader H

and i made sure all required services are running as well.

thank you!

Sven W.

Answer

Try it with sid ORCL instead of XE.

If that doesn't help, then choose SERVICE_NAME orcl instead of SID in sql developer.

Your listener seems to have an entry for that.

You can test it also in SQl*Plus. But what you did so far in SQl*plus was a "server connect" without providing the database.

Since in your case the client and the server is on the same machine, this works with sql*plus.

If you try a client coonnect by specifying the database too, then you would do the same as the Oracle SQL Developer is trying.

For testing purposes something like

sqlplus user/pwd@host:port/ORCL

also in the Sqlnet.ora file there needs to be an etry for local connect method EZCONNECT. I'm not sure if this is set during the default net installation.

You can try if there is a TNS-Alias name (configured in tnsnames.ora)

sqlplus user/pwd@ORCL

If that works with sql*plus then you still need to tell the sql developer where the tnsnames.ora file is located on your computer.

The setting is in menu: extras/tools/database/enhanced

The tnsnames.ora file is usually in some folder like this:

C:\Oracle\product\11.2.0\client_1\network\admin

Marked as Answer by Nader H · Sep 27 2020

1 person found this helpful

unknown-7404

as for oracle sql developer same username and password are used.

hostname: localhost

port 1521 (as set through net configuration)

sid: xe

Why are you using 'sid: xe'? If you check the listener status you posted at the beginning you will see that there is NO such 'xe' listed.

The listing includes this:

Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...

Use 'orcl' as the sid or service name and post the results.

1 person found this helpful

Nader H

really appreciate the effort and clarification!

Solved and thanks!

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 8 2014

Added on Aug 11 2014

#sql-developer

8 comments

3,914 views

SQL Developer

SQL-Developer error:ORA-12505. TNS:listner does not currently know of SID given in connect desc

Comments

Post Details