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): CODEset 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: CODEALTER 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: CODEOracle 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." |
|
Talk To Other Members
Read More Threads Like This One
