This discussion is archived
3 Replies Latest reply: May 27, 2013 11:51 AM by 1011183 RSS

Oracle SQL*Plus Connection Error

1011183 Newbie
Currently Being Moderated
I tried to run SQL Plus under Oracle - OracleHome 10G -> Application Development in a local machine with Oracle 11g XE. This client tool was installed by someone else.

Logon information:
User Name: system
Host String: xe

Here is the error message:

ORA-12154: TNS:cound not resolve the connect identifier specified

I have no problem connecting to Oracle XE using Run SQL Command Line or SQL Developer.

Here is the content of tnsnames.ora

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Test1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

Here is the content of sqlnet.ora

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Here is the content of listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = Test1)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)
  • 1. Re: Oracle SQL*Plus Connection Error
    Paul M. Oracle ACE
    Currently Being Moderated
    This client tool was installed by someone else.
    So it's a separate product, in a different Oracle Home.
    ORA-12154: TNS:cound not resolve the connect identifier specified
    $ oerr ora 12154
    12154, 00000, "TNS:could not resolve the connect identifier specified"
    // *Cause:  A connection to a database or other service was requested using
    // a connect identifier, and the connect identifier specified could not
    // be resolved into a connect descriptor using one of the naming methods
    // configured. For example, if the type of connect identifier used was a
    // net service name then the net service name could not be found in a
    // naming method repository, or the repository could not be
    // located or reached.
    // *Action:
    //   - If you are using local naming (TNSNAMES.ORA file):
    //      - Make sure that "TNSNAMES" is listed as one of the values of the
    //        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
    //        (SQLNET.ORA)
    //      - Verify that a TNSNAMES.ORA file exists and is in the proper
    //        directory and is accessible.
    //      - Check that the net service name used as the connect identifier
    //        exists in the TNSNAMES.ORA file.
    //      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
    //        file.  Look for unmatched parentheses or stray characters. Errors
    //        in a TNSNAMES.ORA file may make it unusable.
    //   - If you are using directory naming:
    //      - Verify that "LDAP" is listed as one of the values of the
    //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
    //        (SQLNET.ORA).
    //      - Verify that the LDAP directory server is up and that it is
    //        accessible.
    //      - Verify that the net service name or database name used as the
    //        connect identifier is configured in the directory.
    //      - Verify that the default context being used is correct by
    //        specifying a fully qualified net service name or a full LDAP DN
    //        as the connect identifier
    //   - If you are using easy connect naming:
    //      - Verify that "EZCONNECT" is listed as one of the values of the
    //        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
    //        (SQLNET.ORA).
    //      - Make sure the host, port and service name specified
    //        are correct.
    //      - Try enclosing the connect identifier in quote marks.
    //
    //   See the Oracle Net Services Administrators Guide or the Oracle
    //   operating system specific guide for more information on naming.
    $
    Looks like you don't have tnsnames.ora within that Oracle Home. If so, you can copy there a working one, or you can set TNS_ADMIN environment variable to point to a working one.

    But you may not need tnsnames.ora : see Using the Easy Connect Naming Method
  • 2. Re: Oracle SQL*Plus Connection Error
    1011183 Newbie
    Currently Being Moderated
    Thanks. I changed host string to Test1/XE. It worked.
  • 3. Re: Oracle SQL*Plus Connection Error
    orafad Oracle ACE
    Currently Being Moderated
    1008180 wrote:
    I tried to run SQL Plus under Oracle - OracleHome 10G
    That Oracle Home has its own network\admin directory and tnsnames.ora (separate from the XE db one). A tnsnames.ora file may not have been created (yet) in that home, depending on how the stuff was installed/configured.

    Btw, why do you want to run sql plus from the "remote" home? The sql plus of XE ie. "sql command line" (take a look at the target application of the shortcut, from Properties) would seem like the first choice. Or, maybe second after SQL Developer (if using a current version anyway).

    Logon information:
    User Name: system
    Host String: xe
    ORA-12154: TNS:cound not resolve the connect identifier specified
    As noted above, use Easy connect i.e "tnsnames-less" naming method, or you'll need to configure the 10g home with an tnsnames entry and connect identifer "xe".

    Note that this error occurs client-side, before any connection attempt.

    I have no problem connecting to Oracle XE using Run SQL Command Line or SQL Developer.
    Different "homes", different config.

    Here is the content of tnsnames.ora
    Search windows drives for tnsnames.ora files - there may be one, two or more.

    Edited by: orafad on May 27, 2013 9:13 PM

    Edited by: orafad on May 27, 2013 9:14 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points