Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

ALTER SYSTEM KILL SESSION Tips

Oracle Tips by Burleson Consulting

There are a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows you to kill an Oracle session.  Also see:  Using dbms_shared_pool.purge to remove a single task from the library cache

The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.

First you have to identify the session to be killed with alter system kill session.

The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Sometimes Oracle.exe is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will be ‘marked for kill'. It will then be killed as soon as possible.

In the case of a session being 'marked for kill' after issuing the alter system kill session  command and not killed immediately, the alter system kill session  command can be forced by adding the immediate keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Important Oracle 11g changes to alter session kill session

Oracle author Mladen Gogala notes that an @ sign is now required to kill a session when using the inst_id column:

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

SQL>  alter system kill session '130,620,1';
 alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

Now, it works:

SQL>  alter system kill session '130,620,@1';

System altered.

For more notes on alter system kill session and how to kill a session from the operating system side, see my notes on the pages below:

 ALTER SYSTEM KILL SESSION notes

Granting ALTER SYSTEM KILL SESSION to users

Methods of Murder

Killing Oracle Sessions

On occasion, it may be necessary to kill an Oracle session that is associated with a running job.  The first step in the process is to identify the session to be killed. 

Running jobs that were scheduled using the dbms_job  package can be identified using the dba_jobs_running view.  The jobs_running.sql script listed below uses this view along with the v$session and v$process views to gather all information needed about the running jobs.

 

running_job_processes.sql

 

set feedback off
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set feedback on
 
select
   jr.job,
   s.username,
   s.sid,
   s.serial#,
   p.spid,
   s.lockwait,
   s.logon_time
from
   dba_jobs_running jr,
   v$session s,
   v$process p
where
   jr.sid = s.sid
and
   s.paddr = p.addr
order by
   jr.job;

 

The type of output expected from this script is listed below.

 

SQL> @running_job_processes

 

  JOB USERNAME     SID    SERIAL# SPID LOCKWAIT LOGON_TIME

 

----- --------- ------ ---------- ---- -------- --------------------

   42 JOB_USER     265          3 3231          23-JUN-2004 08:21:25

   99 JOB_USER     272         77 3199          23-JUN-2004 08:55:35

 

 

Running jobs that were scheduled using the dbms_scheduler package can be identified using the dba_scheduler_running_jobs view.  The following jobs_running_10g.sql script uses this view along with the v$session and v$process views to gather all information needed about the running jobs.

 

running_job_processes_10g.sql

 

select
   rj.job_name,
   s.username,
   s.sid,
   s.serial#,
   p.spid,
   s.lockwait,
   s.logon_time
from
   dba_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;

 

The type of output expected from this script is listed below.

 

SQL> @running_job_processes_10g

 

JOB_NAME                   USERNAME SID SERIAL# SPID LOCK  LOGON_TIME

-------------------------- -------- --- ------- ---- ----- --------------------

TEST_FULL_JOB_DEFINITION   SYS      272     125 3199       23-JUN-2004 09:22:12

 

Regardless of the job scheduling mechanism, the important thing to note is that there are sid, serial#, and spid values associated with the running jobs.  The sid and serial# values are necessary in order to kill the session, while the spid value is necessary if the associated operating system process or thread must be killed directly.

 

To kill the session from within Oracle, the sid and serial# values of the relevant session can then be substituted into the following statement:

 

alter system kill session 'sid,serial#';

 

With reference to the job listed above by the jobs_running_10g.sql script, the statement would look like this:

 

SQL> alter system kill session '272,125';

 

System altered.

 

This command tells the specified session to rollback any un-committed changes and release any acquired resources before terminating cleanly.  In some situations, this cleanup processing may take a considerable amount of time, in which case the session status is set to “marked for kill" until the process is complete.

 

Under normal circumstances, no further actions are needed, but occasionally it may be necessary to bypass this cleanup operation to speed up the release of row and object locks held by the session.  Killing the operating system process or thread associated with the session releases the session’s locks almost immediately, forcing the PMON process to complete the rollback operation.

 

WARNING: Killing the operating system processes associated with Oracle sessions should be used as a last resort.  Killing the wrong process could result in an instance crash and loss of data.

 

In UNIX and Linux environments, the kill command is used to kill specific processes.  In order to use this command, the operating system processes ID must be specified.  The jobs_running.sql and jobs_running_10g.sql scripts list the operating system process ID associated with each running job in the spid column.  With this information, the operating system process can be killed by issuing the following command:

 

kill –9 3199

 

The ps command can be used to check the process list before or after killing the operating system process.

 

ps –ef | grep ora

 

In Windows environments, Oracle runs as a single multi-threaded process, so a specific process is unable to be killed.  Instead, Oracle provides the orakill.exe command to allow a specific thread within the Oracle executable to be killed.

 

orakill.exe ORACLE_SID spid

 

The first parameter should not be confused with the sid value of the Oracle session.  It is, in fact, the SID associated with the instance.  The spid value in windows environments identifies the thread within the Oracle executable, rather than an operating system process ID.  With reference to the job listed above by the jobs_running_10g.sql script, the command issued would look something like this:

 

C:> orakill.exe DB10G 3199

 

These processes can be used to kill jobs, sessions or processes as needed. 

 


 

   

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.