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.
This was last published in January 2006

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close