Changing start date for system jobs related to automatic statistics collections in 11g

1.3k Views Asked by At

I have Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production running on Windows 2008 R2 that somehow got the Next Run Dates for scheduled jobs set to dates in the year 2016. For the jobs created by us I was able to simply reset the START_DATE which in turn caused the NEXT_RUN_DATE to reset. For the jobs related to the Oracle Automatic Statistics Collections I am unable to get DMS_SCHEDULER.SET_ATTRIBUTE to work for changing their START_DATE values.

I tried resetting the weekly window's dates as per Oracle document ID 1450173.1 using SQL like following and it did in fact reset the values I see within DBA_SCHEDULER_WINDOWS but had no change to DBA_SCHEDULER_JOBS

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"SYS"."MONDAY_WINDOW"',
attribute => 'START_DATE',
value => '10-JUN-15 10.00.00.000000000 PM US/CENTRAL');
END;
/

The job names I am seeing this issue on are:

RSE$CLEAN_RECOVERABLE_SCRIPT
ORA$AUTOTASK_CLEAN
PURGE_LOG
SM$CLEAN_AUTO_SPLIT_MERGE
BSLN_MAINTAIN_STATS_JOB
MGMT_STATS_CONFIG_JOB

Some of those lack Schedule Names but if I do try the DBMS_SCHEDULER.SET_ATTRIBUTE for the ones that have Schedule Names I get the follow error:

   BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => '"SYS"."BSLN_MAINTAIN_STATS_SCHED"',
attribute => 'START_DATE',
value => '10-JUN-15 12.00.00.000000000 AM -05:00');
END;
Error report -
ORA-27476: "SYS.BSLN_MAINTAIN_STATS_SCHED" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4398
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2892
ORA-06512: at line 2
27476. 00000 -  "\"%s\".\"%s\" does not exist"
*Cause:    A database object was specified that does not exist.
*Action:   Reissue the command using an object that exists or create a new
           object and then reissue this command.

I did try to simply execute the jobs but that throws an error as well:

exec dbms_scheduler.run_job('"SYS"."BSLN_MAINTAIN_STATS_JOB"', false)
Error report -
ORA-27476: "SYS.BSLN_MAINTAIN_STATS_JOB" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
27476. 00000 -  "\"%s\".\"%s\" does not exist"
*Cause:    A database object was specified that does not exist.
*Action:   Reissue the command using an object that exists or create a new
           object and then reissue this command.

Tried to run DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS which ran successfully but had no change to the NEXT_RUN_DATE values within DBA_SCHEDULER_JOBS

I have also tried using DBMS_AUTO_TASK_ADMIN.ENABLE and DBMS_AUTO_TASK_ADMIN.DISABLE for the 'auto optimizer stats collection' to see if that perhaps would some how trigger date resets but it did not. I had someone suggest to me that perhaps a restart of Oracle would result in resets of the dates since the Windows Start Dates did successfully reset and maybe the ones in DBA_SCHEDULER_JOBS were somehow in memory but the restart changed nothing.

So my ultimate question is how can those START_DATEs or even the NEXT_RUN_DATEs be reset?

0

There are 0 best solutions below