5 Replies Latest reply on Aug 24, 2018 12:55 AM by Gaz in Oz

    How to kill a session from OCCI ?

    3573813

      We're having trouble trying to kill a session using OCCI (when try to call "alter system kill session", OCCI returns something like "session doesn't exist"). What is the proper way to finish a 3rd party session like running "alter system kill session" from sqlplus ? I'm sure the user has the proper rights to do it, because it works over sqlplus...

        • 1. Re: How to kill a session from OCCI ?
          Maxim Kartashev-Oracle

          Hi,

           

          this forum is about Oracle's compiler and tools, there are probably no OCCI experts here. I think you'll have better luck finding an answer to your question in the special forum for OCI: Oracle Call Interface (OCI)

          • 2. Re: How to kill a session from OCCI ?
            3573813

            Thanks, I'll move the thread to that forum.

             

            Regards,

            • 3. Re: How to kill a session from OCCI ?
              Gaz in Oz

              It is unclear from your post as to what error you are getting by what sql call you are doing, as you anecdotally supplied what is going on. Use copy/paste for code and error.

               

              The syntax for "kill session" is:

                 ALTER SYSTEM KILL SESSION '<sid>, <serial#> [, <inst_id> ]';

              https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#SQLRF00902

              where sid and serial# can be gotten from v$session, inst_id (as well as sid and serial#) is available from gv$session (for RAC).

              • 4. Re: How to kill a session from OCCI ?
                3573813

                OK, let me explain with a few more details:

                 

                We are facing a problem where our application is executing a select query statement using OCCI that is taking too long on Oracle Server. Sometimes, the application never returns, so we created a separated thread to implement some kind of "timeout control". If the statement doesn't return after a specific time in seconds, the application kills (or at least try to...) the session and destroy the thread.

                 

                We've tried the following approaches:

                 

                1 - Execute a statement "ALTER SYSTEM KILL SESSION '<sid>, <serial#>' IMMEDIATE". However, Oracle Server returns an error ORA-00030. The very same statement executed on SQL*plus by the same user, works as expected.

                2 - When the query is running and taking too long to process, we tried to invoke the method disconnect() on OCCI. However we noticed that the disconnection call was locked and did not finished.

                3 - Our third approach was to try to reuse the same session that was created to execute other queries. We noticed that it did not work as Oracle Server returned error ORA-01013 .

                 

                Considering we're using OCCI, I understand it's not possible to use ociBreak() - there's no such method on OCCI, only OCI.

                 


                So, the question is, why calling ALTER SYSTEM KILL SESSION via OCCI doesn't work ? If there's a specific way to do it, what is it ?

                • 5. Re: How to kill a session from OCCI ?
                  Gaz in Oz

                  . Database version to 4 digits?

                     select * from v$version;

                  . OCI and OCCI versions?

                  . Are you connecting to RAC or single instance database configuration?

                  . Code you are executing to kill the session?

                      A simple working example would be very helpful, to me and to you.

                  . How are you identifying the sid, serial# of the session you think you want to kill?

                   

                  OK, let me explain with a few more details:

                   

                  We are facing a problem where our application is executing a select query statement using OCCI that is taking too long on Oracle Server. Sometimes, the application never returns, so we created a separated thread to implement some kind of "timeout control". If the statement doesn't return after a specific time in seconds, the application kills (or at least try to...) the session and destroy the thread.

                  The statement "doesn't return" sounds like tuning may be what you should be doing. Or, limit the amount of resources that particular statement can consume.

                   

                  We've tried the following approaches:

                   

                  1 - Execute a statement "ALTER SYSTEM KILL SESSION '<sid>, <serial#>' IMMEDIATE". However, Oracle Server returns an error ORA-00030. The very same statement executed on SQL*plus by the same user, works as expected.

                  That suggests you are doing something wrong in your code. ORA-00030 states:

                  oerr ORA-00030

                  00030, 00000, "User session ID does not exist."

                  // *Cause:  The user session ID no longer exists, probably because the

                  //          session was logged out.

                  // *Action: Use a valid session ID.

                  ...so what sql statement are you actually executing and with what values? (Obviously not a valid sid, serial# combination for that instance).

                  Query (g?)v$session to see the state/status/sql of the session you think you want to kill, to establish what it is doing exactly.

                   

                  2 - When the query is running and taking too long to process, we tried to invoke the method disconnect() on OCCI. However we noticed that the disconnection call was locked and did not finished.

                  "we noticed disconnection call was locked", how?

                   

                  3 - Our third approach was to try to reuse the same session that was created to execute other queries. We noticed that it did not work as Oracle Server returned error ORA-01013 .

                  If you have set a "timeout" on the Oracle connection, then that error is expected if the timeout kicks in.

                  As you provided zero code to SHOW what you are doing, it is difficult to be more specific.

                  Here's a bunch of ways (including some you have already tried) to kill a session:

                  https://oracle-base.com/articles/misc/killing-oracle-sessions

                   

                  Considering we're using OCCI, I understand it's not possible to use ociBreak() - there's no such method on OCCI, only OCI.

                  You can make OCI calls while using OCCI, depending on your program structure.

                   

                  So, the question is, why calling ALTER SYSTEM KILL SESSION via OCCI doesn't work ? If there's a specific way to do it, what is it ?

                  Post a simple example runnable (compilable) code so people can help further.