Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-12514 error when connecting through SQL*Plus

I get the error "ORA-12514: TNS listener could not resolve service_name in connect descriptor" when I try to connect from the command line with SQL*Plus. When I try to connect to the database with other tools (e.g., Toad ), everything works just fine. It's only with SQL*Plus that I have this problem.

I get the error "ORA-12514: TNS listener could not resolve service_name in connect descriptor" when I try to connect from the command line with SQL*Plus. When I try to connect to the database with other tools (e.g., Toad ), everything works just fine. It's only with SQL*Plus that I have this problem.
The ORA-12514 error is telling you that there is a mismatch between your TNS alias's SERVICE_NAME and the database server's SERVICE_NAME as defined for your Oracle Listener. From the command line where you would launch SQL*Plus, issue the following command:
 tnsping tns_alias
where "tns_alias" is the TNS alias you are using in your "sqlplus user/pass@tns_alias" construct. In the output, you can see the SERVICE_NAME, as seen below:
 localhost% tnsping orcl TNS Ping Utility for Solaris: Version 9.2.0.7.0 - Production on 23-JAN-2006 20:02:18 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: /var/opt/oracle/sqlnet.ora Used TN SNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = db1.acme.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (10 msec)
Now you'll know the service name defined for your client. On your database server, determine the services the Listener is listening for:
 localhost% lsnrctl status LSNRC TL for Solaris: Version 9.2.0.7.0 - Production on 23-JAN-2006 20:04:02 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=IPC)) STATUS of the LISTENER ------------------------ Alias LISTENER Version & nbsp; TNSLSNR for Solaris: Version 9.2.0.7.0 - Production Start Date 17-JAN-2006 11:25:27 Uptime 6 days 8 hr. 38 min. 34 sec Trace Level off Security OFF SNMP &nb sp; OFF Listener Parameter File /var/opt/oracle/listener.ora Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
The service names have to match; otherwise you'll get the ORA-12514 error.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close