Transaction still running in a loop after kill immediate

[helpdbas]

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]

Hi,
What platform and OS?





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

 

[SantaMufasa]

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):

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:

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:

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.

Mufasa
(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]

My apologies, HelpDBAs...My "Old Timers" disease prevented my putting this thread in the context of your earlier thread186-1572803 from two weeks ago. Sorry for that oversight.

Mufasa
(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.”