Orphaned Oracle processes

For some unfathomable reason, the backup system here at work seems to stop running once in a while.
This causes the RMAN backup to stop (but not fail), which causes the underlying Oracle shadow processes to hang, because they assume they'll get to continue their work later.

The proceses takes up large amounts of memory and along with various resource locks, starts to cause a bother for the users. So we have to identify and kill those processes.

So I log on as the DB owner and run sqlplus, where I issue this command:

SELECT spid FROM v$process WHERE NOT EXISTS

( SELECT 1 FROM v$session WHERE paddr = addr);

This will display all spids in the v$process that does not have a parrent session.

Then I simply try the unix commands "kill -3" (quit), if this fails then "kill -1" (hangup) and if this fails "kill -9" (abort)


I did, however, at a later date find a nice note indicating that  as of Oracle 10.2, there is an alternative way to kill sessions that will also kill the OS process (from sqlplus):

ALTER SYSTEM DISCONNECT SESSION ’sid,serial#’ POST_TRANSACTION;

ALTER SYSTEM DISCONNECT SESSION ’sid,serial#’ IMMEDIATE;

 The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the
session, while the IMMEDIATE clause disconnects the session and ongoing transactions are
recovered immediately.


So I'll try the latter method next time this happens, as this would likely cause less collateral damage in the system....