Thursday, March 30, 2017

How to execute any command without wainting for server answer



On Oracle, I use DBMS_JOB. On SQL Server, do I have to create a SQL SERVER Agent Job? What if I don't have permission to create that kind of jobs?



Find the SID for a dbms_job dbms_scheduler job


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');

No comments:

Man in the Rain