Thursday, March 4, 2010

Killing Database Sessions - tips from Tanel on killing at the OS level & using oradebug

Tanel Poder in one of his blog entries detailed his preferred approach for killing database sessions - in particular the annoying ones that wont die.

In short:
1) Verify whether you’re killing the right session first (Naturally)

2) ALTER SYSTEM KILL SESSION

3) If that doesn’t work immediately then check whether the target session has acknowledged the kill and is rolling back its (large) transaction.

4) If there’s no rollback happening and session just seems to be stuck, then its time to kill that session’s process from OS level.

5) If couple of minutes after killing the process from OS level that sessions and its locks & resources are still not released then attach to your own process with oradebug and run “ORADEBUG WAKEUP 2″ a couple of times (and checking if the session has gone + waiting few seconds between each invocation).

See Tanel's post for full details.

Alter system kill session and ORA-00031: session marked for kill

No comments:

Post a Comment