• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7195
  • Last Modified:

killing one PL/SQL session from another

I have a number of PL/SQL procedures which I execute from a web browser via the Oracle Web Server.  Some of the procedures can take a long time to execute and I may change my mind about running them, so I want to kill the long running session or sessions.  The only way I have of identifying the session uniquely is to look at the v_$session view and find the active pl/sql initiated by the webserver that looks like mine.  I can then use ALTER SYSTEM KILL SESSION with the SID/Serialno from v_$session.  This is annoying -- I'd like to be able to click a customised "stop" button on my browser (which is waiting for a response, showing me an hourglass) to stop the PL/SQL session on the database, not just a client side stop that will drop the connection, displaying "navigation cancelled" on my browser, with the PL/SQL session continuing to run, which is what I get by default.

What I think I need is a piece of PL/SQL, initiated via the webserver by a button click on the browser, which kills the long-running web server initiated session, but I need to be absolutely sure that the session is the right one.

DBMS_SESSION gives me a UNIQUE_SESSION_ID which I could pass down a pipe when every long running session is initiated.  The killing session could receive that id and act on it.  Unfortunately DBMS_SESSION does not give me a kill method, nor does the UNIQUE_SESSION_ID appear to be the serial number/sid combination I require if I'm going to use DBMS_SQL to ALTER SYSTEM KILL SESSION (although it does look pretty similar, seemingly made up of XXXXYYYYZZZZ where XXXX is the SID in hex, and YYYY is the hex of the serial number reversed bytewise).

Does anybody know either:
1. a better way of doing this (am I missing something obvious) or
2. the exact make up of the unique_session_id in DBMS_SQL so that I can always reliably extract the serial number and sid from it.

It seems a waste of resources when PL/SQL sessions are left running on the database regardless of whether or not the http connection that initiated them has closed, though I can see why you might sometimes want to run a batch process this way.
1 Solution
Mark GeerlingsDatabase AdministratorCommented:
I've had a similar problem with long-running PL\SQL procedures (batch processes) that I have wanted to be able to see progress of and/or cancel.  These did not involve a Web connection, but I think it is basically a PL\SQL issue, not a Web issue.

My solution to this includes a "process_tracking" table and a package of procedures and functions that I call from all long-running processes.  Basically, I have the PL\SQL process insert a new row into my process_tracking table when it starts, then update that row periodically (after every 10, or 100, or 1000 records - whatever makes the most sense for that application) to show me how many records have been processed and/or the record key of the latest one.

One other field in my process_tracking table is a cancel_flag.  When the procedure updates the row with the latest values, it also checks the cancel_flag, and if I have set that to "Y" from another session, the PL\SQL process aborts.

If you think this approach would help you, leave a comment here, and I'll post the table definition, package definition and a sample PL\SQL procedure using them.
cramer091498Author Commented:
I have since found a way to get the sessionid -- userenv('SESSIONID') provides the audsid value from v_$session.  Apparently this is an audit trail value for the session and is unique, so this can be used to derive the sid and serial number required to kill the session.  But I'd still like to hear if anyone knows of a better way.
cramer091498Author Commented:
markgeer -- thanks for the response.  I saw your method in an e-exchange search and considered using it.  The problem is that this will require adding code throughout all procedures to check the cancel status in the table.  I have a lot of these procedures.  Every cursor loop in every procedure that could possibly take some time to complete would need to have a check.  If I can put a single procedure call in at the start of every session (every frame accessed via the webserver) which sends out its ID then that's a lot less code and will allow me to kill the process with more or less immediate effect.  Having found how identify the session id (see my previous comment) I'd prefer to go with this method, unless there's something easier that (perhaps) the web server provides.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

have you checked if you can kill your own session?

My suggestion is the following :

1. Use DBMS_SESSION in the long running pl/sql to generate a UNIQUE_SESSION_ID.
2. Use DBMS_APPLICATION_INFO.SET_MODULE(UNIQUE_SESSION_ID) with this ID as parameter. It will put this value in the MODULE field of the v_$session.
3. Send the UNIQUE_SESSION_ID down the pipe.
4. The killing process should receive the unique ID and then issue something like the following :
INTO   sid,serial
WHERE  MODULE='<unique-sess-id-received>';

BTW, you can use DBMS_APPLICATION_INFO.SET_MODULE or DBMS_APPLICATION_INFO.SET_ACTION to set any other data that may help you in identifying your session or any other needs.

Hope it helps,

P.S. - You shouldn't rely on the result of DBMS_SESSION.UNIQUE_SESSION_ID for resolving the SID and SERIAL#, since Oracle is not obligated to keep it in the same format.

cramer091498Author Commented:

I believe I can do this.  The webserver initiates the process and would initiate the killing process also.  I know I can kill the process from enterprise manager logged in as the user.  I see no reason why I shouldn't be able to from SQL.  


I like this solution.  I wasn't aware you could set the module field in v_$session.  It is essentially the same as using userenv('SESSIONID') -- see my previous comment -- as the audsid it returns is evidently unique for the session and also appears in v_$session.  I'll award you the points tomorrow unless someone comes up with a non-sessionid/pipe method (which doesn't involve writing my own PL/SQL cartridge!).

Your PS also makes sense -- this is really what prompted me to ask the question.

Thanks,  Cramer.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now