0

How do I convert a TNS entry that has a SID to instantclient format?

I'm able to connect with a TNS string but not with the equivalent (I thought!) instantclient string.

$ ./sqlplus 'myname/mypass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.example.com)(PORT=1521))(CONNECT_DATA=(SID=mysid)))'
Connected to: ...

$ ./sqlplus myname/mypass@//myhost.example.com:1521/mysid

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Improve this question
3
  • You can't specify the SID, but have to specifiy a servicename when using easy connect. lsnrctl services on the DB server should tell you, which serivces are available for your database.
    – jmk
    Commented Nov 4, 2015 at 9:52
  • Many thanks! I can't run lsnrctl, as I'm just on a client machine. I'll try getting the servicename from the USERENV.
    – Mark Harrison
    Commented Nov 4, 2015 at 18:05
  • I'm curious if that will work as you don't connect to a service name. But there's another thread that gives a solution. Either try on sql*plus show parameter service_name or try a select value from v$parameter where name like '%service_name%'; with another sql client.
    – jmk
    Commented Nov 4, 2015 at 20:46

1 Answer 1

Reset to default
1

Use your working connection that connects using the SID to find which services are in the database. If you are connected run:

select name, blocked from v$active_services;

and use one of the names you get to connect to.

sqlplus myname/mypass@//myhost.example.com:1521/{a name from query}
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.