Hi all,
Just sharing an experience here. Recently in a migrated 12.2 environment I created a Scheduler Job and it simply disappeared. How come?
Well, seems since 12.2 the AUTO_DROP attribute is set to true by default. I absolutely don’t know why. So I had to recreate the job and set auto_drop to false, simple like this.
I couldn’t find any related bug on MOS, but I’d relate this as a bug, once the default behavior have changed with no prior information.
So, now on, I strongly suggest you to check on your job AUTO_DROP attribute after job creation.
Here is a test case that reproduces the problem:
In a 12.2 database:
begin DBMS_SCHEDULER.create_job ( job_name => 'TEST_JOB', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN null; END;', start_date => SYSTIMESTAMP, repeat_interval => 'SYSTIMESTAMP + INTERVAL ''10'' SECOND', end_date => NULL, enabled => false, comments => 'Test job'); end; / select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB'; -- Job should appear here begin dbms_scheduler.enable('TEST_JOB'); dbms_scheduler.run_job('TEST_JOB',false); end; / select ENABLED, AUTO_DROP, STATE, RUN_COUNT, FAILURE_COUNT,DEFERRED_DROP from dba_scheduler_jobs where job_name = 'TEST_JOB'; -- There is no job now in 12.2.
You can take the AUTO_DROP easily from the query above, or using getting the job_ddl and you’ll see the AUTO_DROP set to true, as in the bottom of this page by Tim Hall.
Hope it helps!
Cheers!
Rules changed.
On 12.2 we have another rule
“It is not a repeating job and has run once.”
11.1
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHCCBFA
11.2
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72961
12.1
https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235
12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87
On 12.2 we have another rule
“It is not a repeating job and has run once.”
11.1
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHCCBFA
11.2
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72961
12.1
https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235
12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87
Since 11g
Rules changed, on 12.2 we have another rule
“It is not a repeating job and has run once.”
11.1
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHCCBFA
11.2
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72961
12.1
https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235
12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87
Rules changed.
On 12.2 we have another rule
“It is not a repeating job and has run once.”
11.1
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHCCBFA
11.2
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72961
12.1
https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72235
12.2
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SCHEDULER.html#GUID-A24DEB5D-2EAF-4C0B-8715-30DC947B3F87
Scheduler Job Disappeared After Creation on 12.2, But auto_drop is default until 11g (11.1, 11.2, 12.1 and 12.2) I think this is a Bug.