Problem
You’re running a Oracle database backup job, and you think the process is hung. You want to kill the process.
Solution
The OS PID can be used to terminate a process with the kill utility. In this example, ps is used to show the PID of an RMAN backup job that seems to be hung and needs to be terminated:
$ ps -ef | egrep ’rman|UID’ | egrep -v egrep
Here is some sample output:
UID PID PPID C STIME TTY TIME CMD
oracle 6822 6234 0 11:40 pts/0 00:00:00 rman target /
The PID is 6822 in this example. To terminate that process, issue a kill command, as shown here:
$ kill -9 6822
The -9 option sends a kill signal to the process, which causes it to terminate. You don’t see any output from the kill command because it unceremoniously removes the specified process. Ensure that you don’t kill the wrong Oracle process. If you accidentally kill a required Oracle background process, your instance will abort.
Note To see a list of all available types of kill signals, use the kill -l command.
How It Works
To run the kill command, you either have to own the process or have root privileges. Sometimes it is necessary to use the kill command to terminate unresponsive Oracle database processes. For example, you might sometimes need to kill a long-running or hung Oracle process (e.g., RMAN, SQL*Plus, and so on). If you know the process name, you can use the ps command to identify the PID (as shown in the solution section of this recipe).
In some situations, you might not know the OS process name. In these scenarios, you can identify the PID by querying data dictionary views, as shown here:
ACCEPT active DEFAULT ’y’ PROMPT ’Active only processes y/n? [y is default]: ’
SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT ’dummy_value’ dummy_value,
’USERNAME : ’ || s.username || CHR(10) ||
’SCHEMA : ’ || s.schemaname || CHR(10) ||
’OSUSER : ’ || s.osuser || CHR(10) ||
’MODULE : ’ || s.program || CHR(10) ||
’ACTION : ’ || s.schemaname || CHR(10) ||
’CLIENT INFO : ’ || s.osuser || CHR(10) ||
’PROGRAM : ’ || s.program || CHR(10) ||
’SPID : ’ || p.spid || CHR(10) ||
’SID : ’ || s.sid || CHR(10) ||
’SERIAL# : ’ || s.serial# || CHR(10) ||
’KILL STRING : ’ || ’’’’ || s.sid || ’,’ || s.serial# || ’’’’ || CHR(10) ||
’MACHINE : ’ || s.machine || CHR(10) ||
’TYPE : ’ || s.type || CHR(10) ||
’TERMINAL : ’ || s.terminal || CHR(10) ||
’CPU : ’ || q.cpu_time/1000000 || CHR(10) ||
’ELAPSED_TIME: ’ || q.elapsed_time/1000000 || CHR(10) ||
’BUFFER_GETS : ’ || q.buffer_gets || CHR(10) ||
’SQL_ID : ’ || q.sql_id || CHR(10) ||
’CHILD_NUM : ’ || q.child_number || CHR(10) ||
’START_TIME : ’ || TO_CHAR(s.sql_exec_start,’dd-mon-yy hh24:mi’) || CHR(10) ||
’STATUS : ’ || s.status || CHR(10) ||
’SQL_TEXT : ’ || q.sql_fulltext
FROM v$session s
JOIN v$process p ON (s.paddr = p.addr)
LEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)
WHERE s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,’x’) NOT LIKE ’%dummy_value%’ -- eliminates this query from output
AND s.status != DECODE(’&&active’,’n’,’xyz’,’N’,’xyz’,’INACTIVE’)
ORDER BY q.cpu_time;
You’ll be prompted about whether you want to see only active sessions displayed in the output:
Active only processes y/n? [y is default]:
Press Enter; here’s some sample output from the previous query:
USERNAME : SYS
SCHEMA : SYS
OSUSER : oracle
MODULE : rman@cs-xvm (TNS V1-V3)
ACTION : SYS
CLIENT INFO : oracle
PROGRAM : rman@cs-xvm (TNS V1-V3)
SPID : 9458
SID : 102
SERIAL# : 49521
KILL STRING : ’102,49521’
MACHINE : cs-xvm
TYPE : USER
TERMINAL : pts/0
CPU :
ELAPSED_TIME :
BUFFER_GETS :
SQL_ID :
CHILD_NUM :
START_TIME :
STATUS : ACTIVE
SQL_TEXT :
From the name of the program in the prior output, you can tell this is an RMAN process executing. The SPID column in the output is the OS PID. Once the SPID is identified, the kill command can be used to terminate the process:
$ kill -9 9458
Caution In some rare situations, killing an Oracle process associated with a SQL transaction can have an adverse impact on the stability of the instance. For example, killing a process participating in a distributed transaction might cause the instance to crash. To determine whether this is an issue for the version of the database you’re using, see MOS bug IDs 8686128 and 12961905. In older versions of Oracle, various other bugs associated with killing a process have been identified and fixed and are documented in MOS bug IDs 5929055 and 6865378.
You can also kill a database connection with the SQL*Plus alter system kill session command by using the session ID (SID) and serial number. Here is the general syntax:
alter system kill session ’integer1, integer2 [,integer3]’ [immediate];
In this syntax statement, integer1 is the value of the SID column, and integer2 is the value from the SERIAL# column (of V$SESSION). In an RAC environment, you can optionally specify the value of the instance ID for integer3. The instance ID can be retrieved from the GV$SESSION view.
As a DBA privileged user, the following command kills the database connection that has an SID of 102 and a serial number of 49521:
SQL> alter system kill session ’102,49521’
If successful, you should see this output:
System altered.
When you kill a session, the session is marked as terminated, active transactions (within the session) are rolled back, and any locks (held by the session) are released. The session will stay in a terminated state until any dependent transactions are rolled back. If it takes a minute or more to roll back the transaction, Oracle reports the session as “marked to be terminated” and returns control to the SQL prompt. Suppose that you specify IMMEDIATE:
SQL> alter system kill session ’102,49521’ immediate;
Oracle will roll back any active transactions and immediately return control back to you.
Caution Killing a session that is executing a select statement is fairly harmless. However, if you terminate a session that is performing a large insert/update/delete, you might see a great deal of database activity (including I/O in the online redo logs) associated with Oracle rolling back the terminated transaction.
