4 Replies Latest reply on Sep 29, 2010 8:13 PM by 560152

    How to configure OracleXEClient to connect to OracleXE?

    wyfwong
      Oracle Database 10g Release 2 (10.2.0.1) Express Edition for Microsoft Windows
      http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

      I download OracleXE.exe (157MB) and OracleXEClient.exe (29.5MB)
      I installed OracleXE and it works well.
      After I installed OracleXEClient, click on [Start], Programs, Oracle Client 10g Express Edition, Run SQL Command Line
      I wish to connect to sys by typing (my password for sys is ora10g_manager):
      connect sys/ora10g_manager@xe as sysdba
      and the error is as follows:
      ERROR:
      ORA-12154: TNS:could not resolve the connect identifier specified

      Since I have also installed Oracle Develioper Suite 10g R2 on a separate ORACLE_HOME. I add XE by setting the tnsmaes.ora in ORACLE_HOME\NETWORK\ADMIN\tnsnames.ora
      XE =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      )
      (CONNECT_DATA =
      (SERVICE_NAME = XE)
      )
      )

      I click on [Start], Programs, Oracle Client 10g Express Edition, Run SQL Command Line, enter
      connect sys/ora10g_manager@xe as sysdba
      and it connects suffessfully.

      My question is if a user installed Oracle XE and Oracle XE Client but no other Oracle products in other ORACLE_HOME, how can one configure Oracle XE Client to connect to Oracle XE Database?
        • 1. Re: How to configure OracleXEClient to connect to OracleXE?
          153119
          Your problem is you have two Oracle_homes, and the Developer Suite home is the first one.
          As soon as you start any Oracle tool, it will determine which Oracle_home to use. This determines which tnsnames.ora it uses unless you  set the TNS_ADMIN environment variable. This is documented, and posted here, and all over the Internet, over and over again to answer questions from people, who assume they are unique, and refuse to do any research prior to posting the question yet again.
          You are one of those persons.

          --
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: How to configure OracleXEClient to connect to OracleXE?
            wyfwong
            1. Create a new user Variable called “TNS_ADMIN”
            Assume ORACLE_HOME of XE is in C:\oracle\product\oraclexe
            Since when you install Oracle 10g Express Edition, a tnsnames.ora file has already been created for you in C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora
            •Either set TNS_ADMIN in DOS prompt or in a New User Variable
            You can set TNS_ADMIN to your local path of Oracle Express Database
            set TNS_ADMIN= C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora

            or you can copy tnsnames.ora from C:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora to C:\oracle\product\XEClient, and set TNS_ADMIN in the User Variable

            Click on [Start], Programs, Oracle Client 10g Express Edition, Run SQL Command Line
            SQL> connect sys/ora10g_manager@xe as sysdba

            Other ways to connect to Oracle Express in DOS prompt
            Method 1: Set TNS_ADMIN in DOS prompt
            In DOS prompt,
            C:\> set TNS_ADMIN=C:\oracle\product\XEClient
            C:\> C:\oracle\product\XEClient\bin\sqlplus.exe /nolog
            SQL> connect sys/ora10g_manager@XE as sysdba

            Method 2: No need to set TNS_ADMIN in DOS prompt but enter the full connection string in one line of DOS command
            Set NLS_LANG in DOS Prompt
            C:\>set NLS_LANG=English
            Enter the following DOS command all in ONE line
            C:\>sqlplus "sys/ora10g_manager@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE))) as sysdba"
            • 3. Re: How to configure OracleXEClient to connect to OracleXE?
              646209
              It does not work for me.

              Why it has to be so difficult? Why not it installs/configures automatically? The software objective is that: make the user life easyer, not more complicated...
              • 4. Re: How to configure OracleXEClient to connect to OracleXE?
                560152
                Because it is Oracle. If they make our life easier, we will have more time thinking in how to get rid of their products from our companies...

                Every other version of oracle client comes with a NETWORK/ADMIN directory shipped. Oracle Express Client (for linux at least) does not have it.

                And about the documentation, for sure it is on "the web", and we have to do our "research", but when it comes to Oracle documentation, we can have all sorts of information but to find what you will need is another thing... For instance, how an average person can find that an environmental variable could affect the instance lookup?

                And why, for God's sake, Oracle still uses TNSNAMES.ORA?