Stop long running scheduled job

jobsoracleoracle-12cscheduled-tasks

I need to launch a scheduled job at 12 o'clock at night and keep this job running for a period of 8 hours. What happens now is that the job is running for more than 12 hours and I really need to avoid running for so long. I have used the Max Run Duration option but it does not have the desired effect for my purpose. I would appreciate if someone could give me an example of how it should be. Database is Oracle 12c. Here I put the script:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.POB_TB_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2019/02/10 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SELLER.FILL_TB_S1'
      ,comments        => NULL
    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'MAX_RUN_DURATION'
     ,value     => TO_DSINTERVAL('+000 05:00:00'));

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.POB_TB_JOB'
     ,attribute => 'number_of_arguments'
     ,value     => 4);

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 1
     ,argument_value       => '30');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 2
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 3
     ,argument_value       => 'N');

  SYS.DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
    ( job_name             => 'SYS.POB_TB_JOB'
     ,argument_position    => 4
     ,argument_value       => '1');

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.POB_TB_JOB');
END;
/

Best Answer

Attribute MAX_RUN_DURATION does not terminate the job, it only raises an event.

I created a procedure like this:

procedure SchedulerWatchdog(jobName in varchar2) is

cursor Jobs is
select job_name
from user_scheduler_jobs
where JOB_NAME = jobName
   AND STATE = 'RUNNING'
   AND systimestamp - LAST_START_DATE > MAX_RUN_DURATION;

begin
   for aJob in Jobs loop
      DBMS_SCHEDULER.STOP_JOB(JobName, FORCE => TRUE);
   end loop;
end;

Run this procedure by scheduler job, perhaps every Minute.

Related Solutions

Sql-server – Running a query within a scheduled job

As per the code in your comment:

USE msdb 

EXEC sp_send_dbmail 
    @profile_name = 'SQL Mail', 
    @recipients = 'vince.chan@ufa.com', 
    @subject = 'T-SQL Query Result', 
    @body = 'The result from SELECT is appended below.', 
    @query = '(include script from above)'

The reason this won't work is the same reason why this wouldn't work:

declare @query varchar(2048);

set @query = 'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN 'D' THEN 'Full'
      WHEN 'I' THEN 'Differential'
      WHEN 'L' THEN 'Transaction Log'
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date
 GO';

 exec (@query);

You're not taking into account the single quotes in the query string. Something like this should be what you need:

declare @query varchar(2048);

set @query = 'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date';

 exec (@query);

Therefore, your constructed query string should be:

'Select TOP 10 
      s.database_name, 
      m.physical_device_name,
      CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(100)) + '' '' + ''Seconds'' TimeTaken,s.backup_start_date,
      CASE s.[type]
      WHEN ''D'' THEN ''Full''
      WHEN ''I'' THEN ''Differential''
      WHEN ''L'' THEN ''Transaction Log''
      END AS BackupType,
      s.server_name,
      s.recovery_model
 FROM msdb.dbo.backupset s
 INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
 ORDER BY backup_start_date DESC, backup_finish_date'

You need to use two single quotes in a string that contains a single quote.

Creating a job scheduled based on a specific timezone

The following creates a job to execute daily at 17.30 local time specified by country.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'update_sales',
   job_type           =>  'STORED_PROCEDURE',
   job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
   start_date         =>  '07-AUG-13 17.30.00 PM India/Delhi',
   repeat_interval    =>  'FREQ=DAILY',
   end_date           =>  '07-AUG-14 17.30.00 PM India/Delhi',
   auto_drop          =>   FALSE,
   job_class          =>  'batch_update_jobs',
   comments           =>  'My new job');
END;
/

Best Answer

Related Solutions

Sql-server – Running a query within a scheduled job

Creating a job scheduled based on a specific timezone

Related Question