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.
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');
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?sid
from
v$session
where
audsid = sys_context( 'userenv', 'sessionid');
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:
Post a Comment