Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Transaction still running in a loop after kill immediate

helpdbas (IS/IT--Management) (OP)
20 Oct 09 22:19
My transaction is still running using a loop and the entire transaction will not kill.  How do you kill the entire transaction if there is a loop?
Turkbear (TechnicalUser)
21 Oct 09 1:24
Hi,
What platform and OS?

 

profile

To Paraphrase:"The Help you get is proportional to the Help you give.."

SantaMufasa (TechnicalUser)
21 Oct 09 14:05

Quote (HelpDBAs):

How do you kill the if there is a loop? entire transaction if there is a loop?
You query the data dictionary view, "gv$session", to identify the "SID" (Session IDentifier) and the "SERIAL#" of the session you wish to kill (that is running the loop):

CODE

set linesize 200
set echo off
set feedback on
col oruser format a20 heading "Oracle Username"
col osuser format a15 heading "O/S Username"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a20
col computer heading "Computer/Terminal" format a22
col program format a50 heading "Program"
col process format 999999 heading "OS|Process|ID"
select     username oruser
   ,osuser osuser
   ,machine computer
   ,''||s.sid||','||s.serial#||'' ss
   ,status
   ,process
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
   ,program
from       gv$session s
order by time
/
ttitle off

Oracle Username      O/S Username    Computer/Terminal      SID/Ser#     STATUS   ID           Logon Date/Time      Program
-------------------- --------------- ---------------------- ------------ -------- ------------ -------------------- -----------------
                     SYSTEM          WM-DAVEHUNT            '170,1'      ACTIVE   2984         2009/10/13 11:04:16  ORACLE.EXE (PMON)
                     SYSTEM          WM-DAVEHUNT            '169,1'      ACTIVE   3240         2009/10/13 11:04:17  ORACLE.EXE (PSP0)
                     SYSTEM          WM-DAVEHUNT            '168,1'      ACTIVE   3260         2009/10/13 11:04:17  ORACLE.EXE (MMAN)
                     SYSTEM          WM-DAVEHUNT            '162,1'      ACTIVE   2664         2009/10/13 11:04:19  ORACLE.EXE (CJQ0)
                     SYSTEM          WM-DAVEHUNT            '167,1'      ACTIVE   2196         2009/10/13 11:04:19  ORACLE.EXE (DBW0)
                     SYSTEM          WM-DAVEHUNT            '166,1'      ACTIVE   2208         2009/10/13 11:04:19  ORACLE.EXE (LGWR)
                     SYSTEM          WM-DAVEHUNT            '165,1'      ACTIVE   1948         2009/10/13 11:04:19  ORACLE.EXE (CKPT)
                     SYSTEM          WM-DAVEHUNT            '164,1'      ACTIVE   2656         2009/10/13 11:04:19  ORACLE.EXE (SMON)
                     SYSTEM          WM-DAVEHUNT            '163,1'      ACTIVE   2304         2009/10/13 11:04:19  ORACLE.EXE (RECO)
                     SYSTEM          WM-DAVEHUNT            '160,1'      ACTIVE   3516         2009/10/13 11:04:19  ORACLE.EXE (MMNL)
                     SYSTEM          WM-DAVEHUNT            '161,1'      ACTIVE   1932         2009/10/13 11:04:19  ORACLE.EXE (MMON)
                     SYSTEM          WM-DAVEHUNT            '154,1'      ACTIVE   4292         2009/10/13 11:05:05  ORACLE.EXE (QMNC)
                     SYSTEM          WM-DAVEHUNT            '151,1'      ACTIVE   4620         2009/10/13 11:05:25  ORACLE.EXE (q000)
                     SYSTEM          WM-DAVEHUNT            '143,7614'   ACTIVE   5036         2009/10/21 08:49:54  ORACLE.EXE (q001)
TEST                 XXX\DaveHunt    XXX\WM-DAVEHUNT        '147,1276'   ACTIVE   1844:5796    2009/10/21 11:33:24  sqlplusw.exe
TEST                 XXX\DaveHunt    XXX\WM-DAVEHUNT        '152,2847'   ACTIVE   216:5048     2009/10/21 11:41:10  sqlplusw.exe
In the case, above, I want to kill '152,2847'. Next I issue the following command:

CODE

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

Working example:
SQL> alter system kill session '152,2847';

System altered.
Notice the single quotes are necessary to surround the two values.

When I then re-run the query against gv$session, I see these results:

CODE

Oracle Username      O/S Username    Computer/Terminal      SID/Ser#     STATUS   ID           Logon Date/Time      Program
-------------------- --------------- ---------------------- ------------ -------- ------------ -------------------- -----------------
...
TEST                 XXX\DaveHunt    XXX\WM-DAVEHUNT        '147,1276'   ACTIVE   1844:5796    2009/10/21 11:33:24  sqlplusw.exe
TEST                 XXX\DaveHunt    XXX\WM-DAVEHUNT        '152,2847'   KILLED   216:5048     2009/10/21 11:41:10  sqlplusw.exe
Let us know if you have additional questions.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

SantaMufasa (TechnicalUser)
21 Oct 09 14:59
My apologies, HelpDBAs...My "Old Timers" disease prevented my putting this thread in the context of your earlier thread186-1572803: kill sessions using loop from two weeks ago. Sorry for that oversight.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close