HOW TO STOP PLSQL PROC RUNNING EVEN AFTER SESSION IS KILLED ????

[skmilind]

Hi,

I have a plsql procedure which selects rows from one table and inserts into other few tables. As it has more than lacs rows I made it restartable, means if it gets any error which causes for stoping the procedure, I dont have to start it again from record one. It can go on from the record where it was stoped.

follwing is the script. --> genreceipt.sql

declare
l_prog varchar2(100) := 'RCPT_GEN';
errm varchar2(3000);
cursor c1 is
select distinct loan_no from loan_details
where loan_no > (select last_txnno from receipts_log where program = l_prog)
order by loan_no;
Begin
FOR x in c1 loop
begin
receipts_generate(x.loan_no); --This proc inserts rows into many tables
update receipts_log -- LOG
set last_txnno = x.loan_no,
count_rows = count_rows+1
where program = l_prog
commit;
exception
when others then
rollback;
errm := sqlerrm;
insert into error_loans values
(x.loan_no, errm, l_prog, sysdate);
commit;
end;
end loop;
end;
/

now i started this script in one telnet session -->
@genreceipts.sql
and in another session say TOAD window i'm monitoring receipts_log -->
select * from receipts_log where program ='RCPT_GEN';
o/p
program last_txnno count_rows
------- ---------- -----------
RCPT_GEN LAABD09824 50

here last_txnno is always updated with last loan no generated and count_rows having total loans generated.

Now Problem is .....

I want to stop that procedure manually so i pressed ctrl C it didnt worked. so I stoped that procedure by clicking disconnect which closed my telnet window. but it dint stop my procedure. in another session my count_rows and last_txnno in is still showing that my loans are getting generated. Then I killed my session, but no use. my counter stoped only when all loans got generated.

IS THERE ANY WAY TO STOP THAT SESSION. WHICH SHOWS AS IT IS KILLED IN MY SESSION LOG. I FEEL IT IS RUNNING IN BACKGROUND.

Thanks in Advance,

- Milind

[manjunathk]

Hi Milind,
Did you try killing the process at the OS level?

Manjunath

[skmilind]

How do I?

[tekion]

"kill pid" where pid is the process id
if kill doesn't work, then try " kill -8 " then "kill -4 ", the
signal (-8, -4) you send is safe enough, IMHO I used it all times.
The reason you are sending it the above signal is becasue you really
don't want o send a -9 to your process; it's not a nice thing to do to your process.


[Edited by tekion on 01-28-2002 at 01:15 PM]

[aarroyob]

In the v$session view you can obtain the spid for this session. At OS level you can do:

Unix: kill -9 spid
NT: Orakill sid spid

Regards

Angel

[anandkl]

Generally if you terminate the session abnormally the request sent by that session will not be aborted untill its closed in a clean manner.
If you want to terminate the session either kill the session.

cheese
anandkl

[Mr.Hanky]

I think alot of the time what you are really waiting for is for Oracle to ROLLBACK whatever transactions took place.
Keep the rollback in mind.

MH

[TomazZ]

Hi all,

Yes, I know that this post is half year old, but I had the same problem today. I just want to share my findings with you.

I also thought that I am waiting for rollback, but then noticed in log table, that the procedure is continuing doing its stuff.
Then I killed it from OS, but why was that necessary....

Well, after some testing I found out, that "exception when others" catches "ORA-00028: your session has been killed" exception like any other and the loop continues till the real end.

I don't think this is expected behaviour... at least, I haven't expected it
Looks like killing for oracle means just pushing an "you have been killed" exception to the victim's session. So we can make any code unkillable, if we want to.
Strange!

Comments?

Have fun,
Tomaz

[Orca777]

Hi,

Is the reason for all this that the PL/SQL-Modul is executed IN the database-memory-area, otherwise how could it possible that the pl/sql still runs even the User-process was killed ?

Orca

PS.: another Question : where are the SQL executet IN DB or in PGA/UGA?



[Edited by Orca777 on 07-29-2002 at 09:46 AM]

[TomazZ]

Maybe I wasn't clear enough. You can kill this session from OS. But you cannot kill it with "alter system kill session..."

I created log table:

SQL> create table log (a number, msg varchar2(2000));

Table created.

I have now two sqlplus sessions with timing set on.
In first I run loop with exception when others handle...
Few seconds after i issue "alter system kill session" in second sqlplus.

-----First session:
SQL> declare
2 err varchar2(2000);
3 i number;
4 begin
5 for i in 1..100 loop
6 begin
7 dbms_lock.sleep(1);
8 insert into log values (i,'OK');
9 exception when others then
10 err:=sqlerrm;
11 insert into log values (-i,err);
12 end;
13 commit;
14 end loop;
15 end;
16 /

PL/SQL procedure successfully completed.

Elapsed: 00:01:52.05
-----
Notice that I haven't got no "your session is killed". Then, when I try to issue next statement I get "session is killed". So the session was killed after successful completion of procedure or anonymous pl/sql block in this example.

-----Second session:
SQL> alter system kill session '22,2545';
alter system kill session '22,2545'
*
ERROR at line 1:
ORA-00031: session marked for kill

Elapsed: 00:01:02.00
-----
It waits for a minute and then fails to kill session, but only marks it for kill. (On succesful kill we get "System altered.")

----- If we examine log table:

SQL> select count(*) from log;

COUNT(*)
----------
100

SQL> select * from log where a<0;

A
----------
MSG
-----------------------------------------
-1
ORA-00028: your session has been killed

-----
We can see here that altough I issued "alter system kill session" in first second of executing pl/sql (see field A equals -1), it continues to run through all 100 loops of which there are 99 successfull:
-----
SQL> select count(*) from log where msg='OK';

COUNT(*)
----------
99
-----

I think what happens is that on kill oracle pushes ORA-00028 exception to session. Since my 'when others' catches and handles the exception, session's default exception handler is not able to respond to 'kill' exception. So the session stays 'marked for kill' but running. After completion of the procedure (when my handler is no longer valid), PMON tries again and succeed.

I tried to put "commit; raise;" in my when others exception handler. After this the kill succeeds: ORA-00028 is the last line in my log.

Regards,
Tomaz