Hello all. Great forum!
I have some sessions in v$session which have been killed, and I would like them to be removed. They exist from a couple crashed workstations and have nothing to rollback.
I understand will remain there until the database is bounced, but exist on a production server (windows 2000), so I'm going with orakill.
Usage: orakill sid thread
where sid = the Oracle instance to target
thread = the thread id of the thread to kill
I'm using this query to look for the thread:
Code:
SELECT s.sid, p.spid AS thread, s.program
FROM v$process p, v$session s
WHERE p.addr = s.paddr;
SID THREAD PROGRAM
------ ------------ ----------------
62 1236
54 540
64 1540
61 1640
36 680
19 1156
26 1516 sqlplusw.exe
66 1392 sqlplusw.exe
Here are the KILLED sessions I'd like to remove:
Code:
SELECT SID,PROGRAM,STATUS
FROM V$SESSION
WHERE USERNAME IS NOT NULL AND PROGRAM IS NOT NULL
ORDER BY STATUS DESC, PROGRAM;
SID PROGRAM STATUS
------ ---------------- --------
29 TOAD.exe KILLED
37 TOAD.exe KILLED
47 TOAD.exe KILLED
52 TOAD.exe KILLED
39 sqlplusw.exe KILLED
66 sqlplusw.exe INACTIVE
26 sqlplusw.exe ACTIVE
As you can see, the first query does not locate the SID's of the KILLED sessions, and therefore I cannot locate the thread needed to complete orakill.
I had considered using this tool "QuickSlice" to locate the correct windows thread, but they had done it based on CPU usage which wouldn't help my case.
This is something I've been trying to solve for quite some time as it has been causing lots of productivity loss.
Thank you all in advance.