CODEWITHSUNDEEP
oracle-databasesqlplus
Mykhaylo Adamovych
Mykhaylo Adamovych

Reputation: 20976

Why do I get ORA-12514 with easy connect but not with TNS?

I can connect successfully to my database with a TNS-style connect descriptor:

connect <user>/<pass>@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=<host>)(Port=<port>))(CONNECT_DATA=(SID=<sid>)));

But when I try to connect like this:

connect <user>/<pass>@<host>:<port>/<sid>;

I get ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

Why does the fist command work but the second does not?

Upvotes: 0

Views: 845

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

SID and service name are not the same thing. In your working version you're explicitly saying you're using the SID, in the CONNECT_DATA part.

When you use the easy connect syntax:

CONNECT username@[//]host[:port][/[service_name][:server]][/instance_name]]
Enter password: password

... you are not supplying the SID; you're actually doing:

connect <user>/<pass>@<host>:<port>/<service_name>

The service name and SID might be the same, but since you're getting that error, in your case they don't seem to be. If you have access to the server you might be able to run lsnrctl services to see which service names are recognised. Or if you can connect as a user with sufficient privileges you can query:

select value from v$parameter where name = 'service_names';

There could be more than one service name reported by either method, and you can probably use any. You may have one like <SID>.<domain> which could be an obvious choice. If in doubt though ask you DBA which you should be using.

Upvotes: 4

Related Questions