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
l_prog varchar2(100) := 'RCPT_GEN';
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;
FOR x in c1 loop
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
when others then
errm := sqlerrm;
insert into error_loans values
(x.loan_no, errm, l_prog, sysdate);
now i started this script in one telnet session -->
and in another session say TOAD window i'm monitoring receipts_log -->
select * from receipts_log where program ='RCPT_GEN';
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.
"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.
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.
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.
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));
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.
2 err varchar2(2000);
3 i number;
5 for i in 1..100 loop
8 insert into log values (i,'OK');
9 exception when others then
11 insert into log values (-i,err);
14 end loop;
PL/SQL procedure successfully completed.
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.
SQL> alter system kill session '22,2545';
alter system kill session '22,2545'
ERROR at line 1:
ORA-00031: session marked for kill
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;
SQL> select * from log where a<0;
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';
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.