Hello All,
Database : 10.2.0.4
OS Version : AIX 6.1
We had an issue for a query running from long time and taking CPU . Upon confirmation with application we
killed the query this morning, but it still shows in v$session status as KILLED , ye not cleared.
Checked the v$transactions as per Tom's suggestions ( https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4974573906087 )
but could not find the reason for the below output.
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
1
1
1
1
1
1
43 rows selected.
not Sure why its showing for 43 times . Can someone let me know how to clear off those KILLED sessions please.
SQL> select username,status,to_char(logon_time,'mm/dd/yy hh:mi:ssAM') logon_time,osuser,program from v$session where status='KILLED';
USERNAME STATUS LOGON_TIME OSUSER PROGRAM
-------------------- -------- ------------------- --------------- --------------------
MDMAR KILLED 12/04/16 04:08:54PM SGSHAWN plsqldev.exe
MDMAR KILLED 11/03/16 03:14:49PM hjeverde SQL Developer
MDMAR KILLED 12/04/16 05:11:08PM SGSHAWN plsqldev.exe
MDMAR KILLED 09/21/16 02:40:04PM SGSHAWN plsqldev.exe
MDMAR KILLED 12/04/16 04:26:34PM SGSHAWN plsqldev.exe
MDMAR KILLED 11/04/16 02:36:36PM hjeverde SQL Developer
MDMAR KILLED 06/16/16 11:21:11AM SGSHAWN plsqldev.exe
MDMAR KILLED 12/04/16 04:50:32PM SGSHAWN plsqldev.exe
MDMAR KILLED 12/09/16 10:39:51AM hjeverde SQL Developer
MDMAR KILLED 06/16/16 02:46:08PM SGSHAWN plsqldev.exe
MDMAR KILLED 12/04/16 04:23:00PM SGSHAWN plsqldev.exe
Thanks
Mohammed
Oracle DBA