HOW TO STOP PLSQL PROC RUNNING EVEN AFTER SESSION IS KILLED ????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

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

Hybrid View

  1. #1
    Join Date
    Oct 2001
    Location
    Hyderabad, India
    Posts
    15

    Question


    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

  2. #2
    Join Date
    Oct 2000
    Posts
    211
    Hi Milind,
    Did you try killing the process at the OS level?

    Manjunath

  3. #3
    Join Date
    Oct 2001
    Location
    Hyderabad, India
    Posts
    15
    How do I?

  4. #4
    Join Date
    Aug 2001
    Posts
    64
    "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]

  5. #5
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    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

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Making it invincible

    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

  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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]

  10. #10
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width