Question: I need a dictionary query to find the SID for a
dbms_job dbms_scheduler job. I'm trying to use the sys_context
function for a task., and it works great when I run it via SQL*Plus.
However, when I schedule it to run via a dbms_job, there appears to
be no session ID.
I also see the same issue when I attempt to kill a job that is
submitted via dbms_job because there is no entry in the v$session
view and it has a SID equal to zero. This query works fine if
I create a dbms_job and run that job manually from
SQL*Plus, but it fails when I execute it independently when sheduled
via dbms_job.
select
sid
from
v$session
where
audsid = sys_context( 'userenv',
'sessionid');
How does Oracle control
the session ID for a job that is executed independently from a
connected session, such as a dbms_job or dbms_scheduler task?
Answer: This is similar to the issues on a
UNIX crontab when a job fails because it is not connected to an
active user session! In your case, the dbms_job scheduled task
is not connected to any session because is run independently from
your own online session (as per v$session).
Oracle scheduled job are submitted via a job queue
process using dbms_job.submit and a "child" process will be
associated with the scheduled job. For long running jobs you
can query the
v$session_longops view and get the system ID (SID).
select
sid,
message
from
v$session_longops
order by
start_time;
This
SID can then be used in a "alter system kill session" command.
You can also force a row into v$session by invoking
the procedure dbms_application_info.set_client_info:
dbms_application_info.set_client_info(client_info => 'submitted via
dbms_scheduler');
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
