tnsping tns_aliaswhere "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 188.8.131.52.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 184.108.40.206.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 220.127.116.11.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 successfullyThe service names have to match; otherwise you'll get the ORA-12514 error.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading