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