I have a procedure in a package, since it might take some time, it will be called by a scheduler job run in background(dbms_job.submit). If schedule takes too long (an hour), I'll need to insert a record in a table immediately. How can I do this? Is there an easy way? I don't want to frequently to check the scheduler_jobs table, I prefer to use something like an event lisenter to monitor its performance, as long as the execution time hits the 60 minutes, an event will be triggered.
1 Answer
Have a look at DBMS_SCHEDULER:
max_run_duration:This attribute specifies the maximum amount of time that the job should be allowed to run. Its datatype is
INTERVAL DAY TO SECOND. If this attribute is set to a non-zero and non-NULL value, and job duration exceeds this value, the Scheduler raises an event of typeJOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.
I never used it, but looks like you have to use DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE to listen to this event.
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
schedule_name => max_execution_time_exceeded,
event_condition => 'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''');
Then use it to create the job which insert a record in a table immediately and optionally kills the long running job.
DBMS_SCHEDULER.CREATE_JOB (
job_name => ...,
job_type => 'PLSQL_BLOCK',
job_action => ...,
schedule_name => 'max_execution_time_exceeded'
)
As said, so far i never used this by myself but it gives you an idea how it should work.
answered Aug 7, 2023 at 9:16
Wernfried Domscheit
60.4k10 gold badges92 silver badges132 bronze badges
ALL_SCHEDULER_JOB_RUN_DETAILStable (ACTUAL_START_DATEorRUN_DURATIONcolumns) and inserts the data you need into some table. But you may directly use this table instead.dbms_jobis the legacy scheduler. Be sure to usedbms_schedulerinstead.