Works with: Solaris, Linux, AIX


Let us start with something easy and frequently used on sites with badly written PL/SQL code.

Let's suppose that you have an Oracle process that has gone astray, or even worse, is generating a lock on your database, preventing other users from making updates on a table.

The first step would be to find the Oracle session that's causing the lock (not the ones that are suffering from the lock, which you would find easily by quering the V$SESSION table).

It's as easy as:

SELECT * FROM dba_blockers;

You might have to wait for a while before getting the magic serial# and sid (not to be mistaken with ORACLE_SID of course) needed to run your ALTER SYSTEM KILL SESSION 'sid , serial#' command.

Note: on Oracle 8i and earlier, you would first have to run the catblock.sql script in $ORACLE_HOME/rdbms/admin/ directory in order to create the DBA_BLOCKERS table.

However, sometimes killing the session simply won't work. In that case, you could have to kill the session, or an Oracle proces gone astray through UNIX system utilities.

For that purpose, on UNIX systems, Oracle has the V$PROCESS table, which can be joined with the V$SESSION table to find the UNIX process ID matching your Oracle session ID (the sid field of DBA_BLOCKERS).

The following query will yield you the needed system process ID (spid), among other useful information about your rogue process or session:

SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;

All you need to do now is to run a kill -9 spid  on your UNIX machine upon the process number given by the spid column of the above query.

Note: On a windows box, you would use the orakilll.exe utility located in $ORACLE_HOME/bin. Like this:
orakill $ORACLE_SID spid

Happy computing.

Drop me a comment if this post has been useful to you.
Alternatively, you could also visit a few links to keep me in business ;-)


Nixman