PL/SQL (MOSC)

MOSC Banner

Newbie: alter system kill session and save their SQL:s?

edited Jun 14, 2015 10:00AM in PL/SQL (MOSC) 1 commentAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center