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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.