3 Replies Latest reply: May 27, 2013 1:51 PM by 1011183 RSS

    Oracle SQL*Plus Connection Error

    1011183
      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.
          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
            Thanks. I changed host string to Test1/XE. It worked.
            • 3. Re: Oracle SQL*Plus Connection Error
              orafad
              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