6 Replies Latest reply on Dec 20, 2019 3:54 PM by thatJeffSmith-Oracle

    SERVER=DEDICATED is ignored

    henryfg42

      Why does sqldeveloper ignore the SERVER=DEDICATED clause in my tnsnames.ora ?

       

      This works for all other tools, using the client software:

      PRP.WORLD=

        (DESCRIPTION=

          (ADDRESS=

            (COMMUNITY=TCP.world)

            (PROTOCOL=TCP)

            (HOST=xxx.xxx.xxx)

            (PORT=yyyy)

          )

          (CONNECT_DATA=

            (SERVER=DEDICATED)

            (GLOBAL_NAME=PRP)

            (SERVICE_NAME=PRP)

            (SID=PRP)

          )

        )

      All other tools (sqlplus, toad) connect using a DEDICATED server process. It is only sqldeveloper that used a SHARED server process.

      (And SHARED serves are enabled due to use of XMLDB/APEX/EM)

        • 1. Re: SERVER=DEDICATED is ignored
          thatJeffSmith-Oracle

          are you using the TNSNames file you THINK you are?

           

          try

           

          show tns (run with F5 in a sqlworksheet)

          • 2. Re: SERVER=DEDICATED is ignored
            EdStevens

            So show us the connection definition within SQL Dev.

            • 3. Re: SERVER=DEDICATED is ignored
              henryfg42

              The result of: show tns

              TNS Lookup locations

              --------------------

              1.  Third Party Path

                  P:\CorpSOE\TSS\oracle\network\admin

              2.  USER Home dir

                  C:\Users\hgie0

              3.  TNS_ADMIN

                  C:\oracle\network\admin

               

               

              Location used:

              -------------

              P:\CorpSOE\TSS\oracle\network\admin

               

              FYI:

              TNS_ADMIN is set to C:\oracle\network\admin

              There is no tnsnames.ora in   C:\Users\hgie0

              I have an identical copy of the tnsnames.ora  in C:\oracle\network\admin on the P: drive

              A long time ago (1 year+) I had TNS_ADMIN defined as: P:\CorpSOE\TSS\oracle\network\admin

               

              H:\>echo %TNS_ADMIN%

              C:\oracle\network\admin

              The above snippet from tnsnames.ora (the one that includes SERVER=DEDICATED) is in both known copies of tnsnames.ora

              • 4. Re: SERVER=DEDICATED is ignored
                henryfg42

                Even though TNS_ADMIN is set to C:\oracle\network\admin

                sqldeveloper used the Third Party Path: P:\CorpSOE\TSS\oracle\network\admin

                We had a file in that directory called tnsnames.ora.old

                That file did not have the "SERVER=" clause.

                 

                When we removed that file, new connections used the DEDICATED server.

                 

                1. I would still like sqldeveloper to follow my TNS_ADMIN directive (otherwise, as a DBA, I cannot test new versions of tnsnames.ora on my C: drive before I deploy to the P: drive..

                2. sqldeveloper 'defaults' to SERVER=SHARED

                 

                This 'solves' the mystery..

                 

                Cheers

                Harry

                • 5. Re: SERVER=DEDICATED is ignored
                  EdStevens

                  henryfg42 wrote:

                   

                  Even though TNS_ADMIN is set to C:\oracle\network\admin

                  sqldeveloper used the Third Party Path: P:\CorpSOE\TSS\oracle\network\admin

                  We had a file in that directory called tnsnames.ora.old

                  That file did not have the "SERVER=" clause.

                   

                  When we removed that file, new connections used the DEDICATED server.

                   

                  1. I would still like sqldeveloper to follow my TNS_ADMIN directive (otherwise, as a DBA, I cannot test new versions of tnsnames.ora on my C: drive before I deploy to the P: drive..

                  2. sqldeveloper 'defaults' to SERVER=SHARED

                   

                  This 'solves' the mystery..

                   

                  Cheers

                  Harry

                  Perhaps Jeff will correct me, but I seriously doubt that SQL Dev itself 'defaults' to anything if it is relying on a tnsnames.ora file (connection type = tns). More likely that default lies at the tns level.  It would be easy enough for you to prove/disprove, simply by using sqlplus instead of SQL Dev.

                  • 6. Re: SERVER=DEDICATED is ignored
                    thatJeffSmith-Oracle

                    we do what's defined in the connection properties, and if you don't mention anything, we'll do the default for the jdbc driver (thin) or sqlnet (thick)