Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Killing Oracle Job Sessions
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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. 

 

This next section will present an assortment of advanced topics related to administration of the Oracle scheduler.  Topics to be covered include setting default scheduler attributes along with object specific attributes, scheduler logging, resource allocation and security. 


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.