Killing Oracle Scheduling Sessions

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.

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.

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

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:

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

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:

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

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.

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.

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.

dbms_application_info

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.

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.

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.

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.

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

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

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.

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

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

The resulting output looks something like the one listed below.

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.

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.

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.

dbms_system

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

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.

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

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.

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.