DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: How to set query timeout in DB?

  1. #11
    Join Date
    Feb 2002
    Posts
    48
    Thank you so much for all your inputs.

    Unfortunately, we will be running on 8i for a while before upgrading to 9i to take advantage of the MAX_ESTIMATED_EXEC_TIME.

    Tamilselvan,
    If you have an idea how to do it, I will be glad to see your procedures. Thank you in advance!

    My boss said you guys are wonderful and that we should cancel the Oracle Support and pay you guys instead!!

    Just wonder... are you guys (advisors & moderators) doing this full-time? I mean giving technical advice. I found you all have great Oracle knowledge and give excellent advice. Your help is greatly appreciated.

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Here is the procedure:
    create or replace procedure kill_session_proc as
    -- --------------------------------------------
    -- This procedure works only 8i or later
    -- Login as system or sys and execute it
    -- -------------------------------------------
    cursor c1 is
    select sid, serial#
    from v$session_longops
    where elapsed_seconds > 120 and
    time_remaining > 0 ;
    begin
    for c1_rec in c1 loop
    execute immediate 'alter system kill session '||''''
    ||to_char(c1_rec.sid)||','||to_char(c1_rec.serial#)||'''' ;
    end loop;
    end;
    /

    Set up a dbms_job with appropriate time interval to call the procedure.

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