Newbie: alter system kill session and save their SQL:s?
Hi,
I have manage to check the database after a specific wait, if the wait exists, check if the session with the waits is blocking other sessions, if it does, I kill the session.
This is the part where I kill the blocking sessions:
loop
if (blockingsessions.s1 > sessionvalue) then
for rec3 in (SELECT 'alter system kill session '''|| sid || ',' || serial# || '''' stmt FROM v$session where sid=blockingsessions.s1)
loop
execute immediate rec3.stmt;
end loop;
var_event:='Killed sessions!';
insert into maintenance.logtable values (SYSDATE,var_event);
commit;
end if;
What I would like is help on how to capture the SQL that was causing the block.