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

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Killing Oracle Scheduling Sessions

Oracle Tips by Burleson Consulting

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

   dba_jobs_running jr,
   v$session s,
   v$process p
   jr.sid = s.sid
   s.paddr = p.addr
order by

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

SQL> @running_job_processes

----- --------- ------ ---------- ---- -------- --------------------
   42 JOB_USER     265          3 3231          23-JUN-2004 08:21:25
   99 JOB_USER     272         77 3199          23-JUN-2004 08:55:35

2 rows selected.

Running jobs that were scheduled using the dbms_scheduler package can be identified using the dba_scheduler_running_jobs view.  The jobs_running_10g.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_10g.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
-- To license this script for a commercial purpose,
-- contact
-- *************************************************

   dba_scheduler_running_jobs rj,
   v$session s,
   v$process p
   rj.session_id = s.sid

 s.paddr = p.addr
order by

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

SQL> @running_job_processes_10g

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

1 row selected.

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 circumstance 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.

* 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.  In the next section, the dbms_application_info package will be introduce.  This package can be used to help monitor jobs and their sessions.


Although the dbms_application_info package is not directly related to job scheduling, it is valuable for identifying and monitoring the progress of any sessions that support scheduled jobs.  The package allows programs to add information to the v$session and v$session_longops views to make tracking of session activities more simple and more accurate.

When a program initiates, it should register itself using the set_module procedure to indicate that it is currently using the session.

PROCEDURE set_module (
  module_name  IN  VARCHAR2,
  action_name  IN  VARCHAR2)

The module_name parameter is used to specify the program name; while the action_name parameter is used to indicate what the program is currently doing. 

As programs progress, the set_action procedure can be used to alter the value of the action column of the v$session view.

PROCEDURE set_action (
  action_name  IN  VARCHAR2)

Assuming there was a procedure called add_order, which adds an order into an application schema, the following dbms_application_info package might be used.

    module_name => 'add_order',
    action_name => 'insert into orders'); 

  -- Do insert into ORDERS table.

    action_name => 'insert into order_lines');

  -- Do insert into ORDER_LINESS table.

    action_name => 'complete');

In the above example, the set_module procedure sets the value of the module column in the v$session view to add_order, while the ACTION column is set to the value insert into orders.

The action is regularly amended using the set_action procedure to make sure the ACTION column of the v$session view stays accurate.

The set_client_info procedure allows information to be stored in the CLIENT_INFO column of the v$session view.

PROCEDURE set_client_info (
  client_info  IN  VARCHAR2)

The following example may be executed to indicate that the procedure is being run as a job.

    client_info => 'job');

The following query shows that the values in the v$session view are being set correctly.

   username = 'JOB_USER'

MODULE             ACTION           CLIENT_INFO
------------------ ---------------- -------------------------

add_order          complete         job

1 row selected.

The set_session_longops procedure can be used to publish information about the progress of long operations by inserting and updating rows in the v$session_longops view.

PROCEDURE set_session_longops (
  rindex       IN OUT  PLS_INTEGER,
  slno         IN OUT  PLS_INTEGER,
  op_name      IN      VARCHAR2    DEFAULT NULL,
  target       IN      PLS_INTEGER DEFAULT 0,
  context      IN      PLS_INTEGER DEFAULT 0,
  sofar        IN      NUMBER      DEFAULT 0,
  totalwork    IN      NUMBER      DEFAULT 0,
  target_desc  IN      VARCHAR2    DEFAULT 'unknown target',
  units        IN      VARCHAR2    DEFAULT NULL)

This procedure is especially useful when operations contain long running loops such as in the example below.

  l_rindex     PLS_INTEGER;
  l_slno       PLS_INTEGER;
  l_totalwork  NUMBER;
  l_sofar      NUMBER;
  l_obj        PLS_INTEGER;
  l_rindex    := DBMS_APPLICATION_INFO.set_session_longops_nohint;
  l_sofar     := 0;
  l_totalwork := 10;

  WHILE l_sofar < 10 LOOP
    -- Do some work
    l_sofar := l_sofar + 1;

      rindex      => l_rindex,
      slno        => l_slno,
      op_name     => 'BATCH_LOAD',
      target      => l_obj,
      context     => 0,

 sofar       => l_sofar,
      totalwork   => l_totalwork,
      target_desc => 'BATCH_LOAD_TABLE',
      units       => 'rows');

While the above code is running, the contents of the v$session_longops view can be queried as follows.

column opname format A20
column target_desc format A20
column units format A10


   opname = 'BATCH_LOAD';

The resulting output looks something like the one listed below.

-------------------- -------------------- ------ ---------- --------
BATCH_LOAD           BATCH_LOAD_TABLE          3         10 rows

1 row selected.

The my_job_proc procedure that is used throughout this book utilizes the dbms_application_info package.

In Oracle10g, the dbms_monitor package can be used to initiate SQL tracing for sessions based on their service, module and action attributes, making the use of the dbms_application_info package even more valuable.  A complete introduction to these SQL tracing enhancements is beyond the scope of this book, but the following example shows how the dbms_monitor package is used in this context.

  DBMS_MONITOR.serv_mod_act_trace_enable (
    service_name  => 'my_service',
    module_name   => 'add_order',
    action_name   => 'insert into order_lines');

  DBMS_MONITOR.serv_mod_act_trace_disable (
    service_name  => 'my_service',
    module_name   => 'add_order',
    action_name   => 'insert into order_lines');

The same attributes can be used by the new trcsess utility to consolidate information from several trace files into a single file, which can then be processed by the TKPROF utility.  The following example searches all files with a file extension of ?.trc? for trace information related to the specified service, module and action.  The resulting information is written to the ?client.trc? file.

trcsess output=client.trc service=my_service module=add_order action=?insert into order_lines? *.trc

This section showed how the dbms_application_info package offers valuable tools for identifying and monitoring the progress of any sessions that support scheduled jobs.  The next section will present how the dbms_system package can be used to write text directly to trace files and alert logs.


The dbms_system package is an undocumented and unsupported package that contains a number of useful functions and procedures, including the ksdwrt procedure. 

PROCEDURE ksdwrt (
  tst   IN  VARCHAR2)

The ksdwrt procedure allows the text to be written directly to the alert log and trace files. The dest parameter indicates the destination of the message, which can be one of the following.

* A trace file.

* The alert log.

* Both.

The tst parameter is used to specify the text that should be written to the destination.

The following command shows how it can be used to write text to the alert log.

SQL> exec dbms_system.ksdwrt(2, ?*** KSDWRT Test ****?);

Checking the instances alert log will reveal that a message like the following message has been appended.

Wed Jun 23 12:14:46 2004
*** KSDWRT Test ****

The ksdwrt procedure name is not that memorable in and of itseld, so one may wish to wrap it up in a more obvious procedure as shown in the write_to_alert_log.sql below.

* write_to_alert_log.sql

-- *************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- To license this script for a commercial purpose,
-- contact
-- *************************************************

-- *****************************************************************
-- Parameters:
--    1) Text to be written to the alert log.
-- *****************************************************************

CREATE OR REPLACE PROCEDURE write_to_alert_log (
  p_text  IN  VARCHAR2) AS
  sys.dbms_system.ksdwrt(2, p_text);

Now users have an additional tool that can be used capture user defined text in an alert log or trace file.  The my_job_proc procedure that is used throughout this book as a running example makes used of the dbms_system package, so this tool can be added to that procedure, if desired.

* The alert log is a very important file and filling it with lots of extra text may distract attention from important messages.  In addition, Oracle support may wish to use alert log contents to diagnose problems, so use this functionality with discretion.


This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational