Thursday, January 12, 2012

Killing a running query in Oracle

If you are working with Oracle using an IDE like SQL Developer or TOAD, sometimes it could happen that some of your past queries are still running and therefore blocking your current work.

In case, you can (using an admin account) access the Oracle system tables using something like this simple query to get your current running job list:

SELECT    p.spid   , q.sql_fulltext   , s.* FROM   v$session s INNER JOIN   v$session_wait wON    s.sid=w.sidLEFT OUTER JOIN   v$process pON    s.paddr=p.addrWHERE   s.schemaname='SCHEMANAME'AND   LOWER(s.osuser)='USERNAME';

Each query runs as at OS level a separate thread/process. Most of the Oracle installations runs over an Unix machine: in case, you can simple kill the corresponding process with the command:

# kill -9 PID_NUMBER

No comments:

Post a Comment