The job DBMS_SCHEDULER is not executed and uses slaves (2023)

The Oracle parameter JOB_QUEUE_PROCESSES specifies the maximum number of job slaves per instance that can be created to run DBMS_JOB jobs and Oracle Scheduler (DBMS_SCHEDULER) jobs. The number of freely available slaves (#free_slaves) is JOB_QUEUE_PROCESSES minus the number of slaves used (#used_slaves). If #used_slaves >= JOB_QUEUE_PROCESSES, no other slave jobs can be executed.

In this blog, we will show that #used_slaves counts not only currently running jobs, but also abnormally ended jobs, which includes incident jobs and ended jobs.

Monitoring:Tested and reproducible on Oracle 18.10, 19.6 and 19.7. Partially reproducible on Oracle 12.1. Not playable on Oracle 19.8. * Oracle 19.8 changed the behavior tested in the blog.

1. Test configuration

Let's create two types of repeating DBMS_SCHEDULER tasks. The first is abnormally ended tasks crashing with ORA-600 [4156] (see blog:ORA-600 [4156] SAVEPOINT and PL/SQL exception handling); The second are normal jobs with a runtime of 60 seconds.

drop table t purge;cria tabela t(id number, label varchar2(20));insert in t(id, label) select level, 'label_'||level from dual connect by level <= 100;commit;create or replace procedimento test_proc_crashed(p_i number) asbegin savepoint sp; update t set label = label onde id = p_i; Immediatamente ausführen 'begin raise_application_error(-20000, ''error-sp''); exceção quando outros revertem para o ponto de salvamento sp; update t set label = label where id = '||p_i||'; Levantar; fim;'; end;/criar oder ersetzen Sie das Verfahren start_job_crashed(p_count number) asbegin for i in 1..p_count loop dbms_scheduler.create_job ( job_name => 'TEST_JOB_CRASHED_'||i, job_type => 'PLSQL_BLOCK', job_action => 'begin test_proc_crashed(' ||i||'); end;', ​​​​start_date => systimestamp, repeat_interval => 'systimestamp', auto_drop => verdadeiro, ativado => verdadeiro); end loop;end;/criar ou substituir procedimento start_job_normal(p_count number) como l_job_name varchar2(50);begin for i in 1..p_count loop l_job_name := 'TEST_JOB_NORMAL_'||i; dbms_scheduler.create_job ( job_name => l_job_name, job_type => 'PLSQL_BLOCK', job_action => 'begin dbms_lock.sleep(13); dbms_scheduler.set_attribute( name => '''||l_job_name||''' ,attribute => ''start_date'' ,value => systimestamp); dbms_lock.sleep(47); end;', ​​​​start_date => systimestamp, repeat_interval => 'systimestamp', auto_drop => verdadeiro, ativado => verdadeiro); fim tun ciclo;fim;/

2. Start des Tests

First we set job_queue_processes to 13 and at the same time trace the DBMS_SCHEDULER CJQ0 coordinator with Event 27402 Level 65535.

alter system set job_queue_processes=13 scope=memory;alter system set max_dump_file_size = unlimited scope=memory;-- coordinator DBMS_SCHEDULER CJQ0(sid, s.serial#) = (7, 15601)exec sys.dbms_system.set_ev(7, 15601, 27402, 65535, '');

Then start two test_proc_crashed jobs and one normal job.

exec start_job_crashed(2);exec start_job_normal(1);

During the test, we continuously monitor the jobs running in dba_scheduler_running_jobs and the status of the job ("RUNNING") in dba_scheduler_jobs:

selecione job_name,session_id, slave_process_id, slave_os_process_id, elapsed_time, log_id de dba_scheduler_running_jobs; JOB_NAME SESSION_ID SLAVE_PROCESS_ID SLAVE_OS_PRO ELAPSED_TIME LOG_ID ------------------- ---------- --------------- - ------------ ------------------------ TEST_JOB_CRASHED_2 375 62 25302 +000 00:00:06.93 8587896 TEST_JOB_NORMAL_1 731 64 25130 +000 00:00:06.95 8587892 TEST_JOB_CRASHED_1 910 65 25270 +000 00:00:06.95 8587894select job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_jobs from; JOB_NAME AKTIVIERUNGSZUSTAND RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ------------------- ----- ----------------- ------------- ----------- ------------ -------------- -- ------------- TEST_JOB_CRASHED_1 TRUE RUNNING 2 0 0 10:37:05.497 10:38:12.072 10:37:45.054 TEST_JOB_CRASHED_2 TRUE RUNNING 2 0 0 10:37:06.370 10:38 :12.090 10:37:45.071 TEST_JOB_NORMAL_1 TRUE LÄUFT 1 0 0 10:37:24.732 10:38:12.071 10:37:24.769

After a few minutes we can see that there are no more jobs running in dba_scheduler_running_jobs and the job status in dba_scheduler_jobs remains "SCHEDULED":

selecione job_name,session_id, slave_process_id, slave_os_process_id, elapsed_time, log_id de dba_scheduler_running_jobs; nenhuma linha selecionada selecionada job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME AKTIVIERUNGSZUSTAND RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ------------------- ----- ----------------- -- ------------- ----------- ------------- ----------- ----- ------------- TEST_JOB_CRASHED_1 TRUE AGENDADO 7 0 0 10:37:05.497 10:41:12.340 10:41:12.341 TEST_JOB_CRASHED_2 TRUE AGENDADO 6 0 0 10:37:06.370 10:39:44.948 10:39:44.950 TEST_JOB_NORMAL_1 WAHR GEPLANT 4 0 0 10:40:25.148 10:40:12.145 10:40:25.152

Now we can disable the CJQ0 trace (otherwise the CJQ0 trace file will be many MB):

exec sys.dbms_system.set_ev(7, 15601, 27402, 0, '');

Note that we used trace event CJQ0 27402 level 65535 (0xffff) instead of level 65355 (0xff4b) as documented in MOS: Scheduler Stuck Executing Jobs And CJQ0 Locking SYS.LAST_OBSERVED_EVENT (Doc ID 2341386.1).

You can also initiate a database-wide 27402 trace by:

Change system configuration events '27402 trace naming context forever, level 65535'; change system configuration event "27402 naming context tracking disabled";

As a test, we can run the "SCHEDULED" job immediately in the current session (foreground session) instead of the job's slave session (background session) as follows:

exec dbms_scheduler.run_job(job_name => 'TEST_JOB_NORMAL_1', use_current_session => true);exec dbms_scheduler.run_job(job_name => 'TEST_JOB_CRASHED_1', use_current_session => true);

3. Slaves used per job

In the dba_scheduler_jobs query above, we can see that TEST_JOB_CRASHED_1 run_count=7, TEST_JOB_CRASHED_2 run_count=6, the total run_count of two test_proc_crashed jobs is 13:

selecione sum(run_count) "#used_slaves" de dba_scheduler_jobs onde job_name como 'TEST_JOB_CRASHED%'; #used_slaves ------------- 13

The number of ORA-00600:[4156] occurrences in v$diag_alert_ext during the testing interval is 13:

selecione count(distinct process_id) "#used_slaves", min(originating_timestamp), max(originating_timestamp) de v$diag_alert_ext where (message_text like '%incident%ORA-00600%4156%' or problem_key like '%ORA 600 [4156]% ') e originating_timestamp > timestamp'2021-01-18 10:30:00'; #used_slaves MIN(ORIGINATING_TIMESTAMP) MAX(ORIGINATING_TIMESTAMP) ------------ ---------------------- - ----- -------------------- 13 10:37:06.572 10:41:21.195

Open DB alert.log, count the various OS process ID numbers in all incident files ORA-00600:[4156] (around the text "ORA-00600: internal error code, arguments: [4156]"), is exactly 13.

Go to the diagnostic incidents directory, count the number of incidents files with different OS process IDs, it's also 13.

Now open the CJQ0 trace file. It shows that when the condition "CDB slave limit" <= "CDB used/reserved slaves" is not true, the stanza jscr_can_pdb_run_job returns 1, more slaves can become "RUNNING". The text in the trace file looks like this:

SCHED 18.01 00 23038 CJQ0 0(jscr_can_pdb_run_job):CDB Slave Limit=13, CDB Used/Reserved Slaves=5, MSL=2 SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 Slaves: used=4 , attribut=1, reservado=5, max=13 SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Return 1 SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_assigned_pdb): Digite SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_increase_pdb_assigned_slaves):Retorne SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscrsq_select_queue): JOB FLOW TRACE: SELECT TJL: Q65 JOB TRACE Schedule 01-18 10: 38: 12.061 1 00 23038 CJQ0 0 (JSCRSQ_SELECT_QUEUEL): Beachten Sie und in Betracht und TRABALHO 3715270 SCHLAD 01-18 10: 38: 12.061 1 00 23038 CJQ0 0 (JSCR_CAN_PDB 10: 38: 12.061 1 00 23038 0:3-1CHED_pdb 10-1CHED 1) :12.061 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):CDB Slave limit= 13, CDB Usado/Reservado escravos=6, MSL=2 SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 escravos: usado=4, atribuído =2, reservado=6, max=13 SCHED 01-18 10:38:12.061 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Return 1 ... SCHED 01-18 10:39:12.095 1 00 23038 CJQ0 0(jscrsq_select_queue) :Considerando o trabalho 3715 1018:1018 SCHED 39:12.095 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Enter SCHED 01-18 10:39:12.095 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Used/Reserved CDB slave slaves=8, MSL=2 SCHED 01 -18 10:39:12.095 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 escravos: usado=8, atribuído=0, reservado=8, max=13 SCHED 01-18 10: 39:12.095 1 00 23038 CJQ0 0 (jscr_can_pdb_run_job): Zurück 1

If "CDB slave limit" <= "CDB used/reserved slaves" is true, jscr_can_pdb_run_job returns 0, slaves stay in "scheduled" state and cannot enter "RUNNING" state. The trace file is repeatedly populated with the following text.

SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscrsq_select_queue):Considerando o trabalho 3715270 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Insira SCHED 10101:438 10.438 00 23038 CJQ0 0 (jscr_can_pdb_run_job):CDB-Slave-Limit=13, CDB-verwendete/reservierte Slaves=13, MSL=2 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0-Slaves: verwendet=13, Attribute=0 , reservado=13, max=13 SCHED 18.01. 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Retornar 0 SCHED 18.01 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Insira SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job3):Reservado, CDB-Slave-Limit=1CDB-Slave-Limit=13 , MSL=2 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 escravos: usado=13, atribuído=0, reservado=13, max=13 SCHED 01-18 10: 43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job): Rückgabe 0 SCHED 01-18 10:43 :10.442 1 00 23038 CJQ0 0(jscrsq_sele ct_queue):Considerando o trabalho 3715269 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Insira SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0( jscr_can_pdb_run_job):CD slave=CDB_run limite: Escravos usados/reservados=13, MSL=2 SCHED 01-18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 escravos: usado=13, attributo=0, reservado =13, max=13 SCHED 01- 18 10:43:10.442 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Return 0

In the test above and in the CJQ0 trace file, we can see that #used_slaves is the number of occurrences, "CDB Slave Limit" is specified by JOB_QUEUE_PROCESSES. As soon as #used_slaves has reached JOB_QUEUE_PROCESSES, no other JOB may be in the "RUNNING" state. dba_scheduler_jobs indicates that the job is activated ("TRUE"), but the state remains "SCHEDULED". Restarting the database resets #used_slaves.

In the trace above, the slave limit CDB is prefixed as "CDB-Slave-Limit = 13". Job slaves are generated in the root container since they are a shared resource between PDBs as documented in MOS: Alter System Kill Session Failed With ORA-00026 When Kill a Job Session From PDB (Doc ID 2491701.1)). with Oracle documentation on JOB_QUEUE_PROCESSES which said: is the maximum number of worker slaves per instance, not root containers).

"MSL=2" looks like the maximum number of jobs allocated at each schedule check. jscr_can_pdb_run_job The call stack looks like this:

#0 0x00000000041bfcbb in jscr_can_pdb_run_job () #1 0x00000000041be8a8 in jscrsq_select_queue () #2 0x00000000041b3224 in jscrs_select0 () #3 0x00000000041b00f2 in jscrs_select () #4 0x00000000122ac797 in rpiswu2 () #5 0x0000000003726293 in kkjcjexe () #6 0x0000000003725d65 in kkjssrh () # 7 0x0000000012360495 in ksb_act_run_int () #8 0x000000001235f162 in ksb_act_run () #9 0x000000001235dec5 in ksbcti () #10 0x0000000003d2a550 in ksbabs () #11 0x0000000003d48611 in ksbrdp () #12 0x0000000004168bf7 in opirip () #13 0x00000000027b8138 in opidrv () #14 0x00000000033be90f in sou2o () #15 0x0000000000d81f9a in opimai_real () #16 0x00000000033cb767 in ssthrdmain () #17 0x0000000000d81ea3 in main ()

The maximum value of JOB_QUEUE_PROCESSES is set as:

até Oracle 12cR1: 1000 von Oracle 12cR2: 4000

It looks like CJQ0 is regularly waking up every 200ms to look for activated jobs. Related hidden parameters are:

Name Description Default ---------------------------------------- ------- -------------------------------------------------- - ------ _sched_delay_sample_interval_ms Planverzögerung Abtastintervall in ms 1000 _sched_delay_max_samples Planverzögerung max. Anzahl von Abtastungen 4 _sched_delay_sample_collection_thresh_ms Planverzögerung Abtastdauer Schwellwert ms 200 _sched_delay_measurement_sleep_us Planverzögerung Messung us 1000 _sched_delay_ular_os_tick verwendet pro 1000 _sched_delay_ular_os_tick Berechnung pro Plan

In the trace file above jobs 3715269, 3715270 and 3715271 can be found by querying:

selecione o.object_name, start_date, last_enabled_time, last_start_date, next_run_date, last_end_date de dba_objects o, sys.scheduler$_job j onde o.object_id = j.obj# e j.obj# em (3715269, 3715270, 3715271); OBJECT_NAME START_DATE LAST_ENABLED_TIME LAST_START_DATE NEXT_RUN_DATE LAST_END_DATE -------------- ------------ -------------- --- ---------- ------------- -------------TEST_JOB_CRASHED_1 10:37:05.497 10:37:06.351 10:41:12.340 10:41:12.341 10:41:24.322TEST_JOB_CRASHED_2 10:37:06.370 10:37:06.373 10:39:44.948 10:39:44.950 10:40:10.12.12 10:40:25.152 10:40:12.145 10:40:25.152 10:41:12.153

Class object 12166 is DEFAULT_JOB_CLASS:

selecione proprietário, object_name, object_type de dba_objects onde object_id = 12166; EIGENTÜMER OBJEKTNAME OBJEKTTYP ------ ------------------ ----------- SYS DEFAULT_JOB_CLASS JOBKLASSE

On Oracle 12.1, the above behavior can only be partially reproduced. Once #used_slaves have reached JOB_QUEUE_PROCESSES, they can still start and stop regularly (job status changed from "RUNNING" to "SCHEDULED", then back to "RUNNING" after a while). The following CJQ0 trace shows that jleft (job left) is started with JOB_QUEUE_PROCESSES(13), once it reaches 0, it resets to 0 after a certain interval.

Programa 11: 58: 35.575 1 00 19988592 CJQ0 0 (JSCRS_SELECT0): Posição 0, Jó 1287236, Considerado_count 0, PRIO 6 AGRONGEM 11: 58: 35.575 1 00 19988592 CJQ0 0 (JSCRMR_MA_RUN_RUN_RUN_RUN_RUN_RUN_RUN_RUN_RUN_RUN_RUNM 00 19988592 CJQ0 0(jscrmrr_mark_run_resource_mgr):jleft=13 , sleft=1032, pleft=638 SCHED 11:58:35.575 1 00 19988592 CJQ0 0(jscrrsa_rm_slave_allocation):all cg concedido total de 1 ... SCHED 161:59: 00 19988592 CJQ0 0(jscrs_select0):position 0, job 1287236 , berücksichtigt_count 0, Prio 6 ender 11: 59: 36.145 1 00 19988592 CJQ0 0 (JSCRMRR_MARK_RUN_RESOURCE_MGR): Eingabering Jscrmr_mark_run_resource_mgred 11: 59: 36.145 1 00 1998592 CJQ0 0 (JSCRMRRREL): JSCRMRRRRAGN. 11:59:36.145 1 00 19988592 CJQ0 0(jscrrsa_rm_slave_allocation):todos cg concedido total de 1 ... SCHED 11:59:44.941 1 00 19988592 CJQ0 0(jscrs_select0) :posição 0, trabalho 1287237, Consider_count SCHED 11:59:44.941 1 00 19988592 CJQ0 0(jscrs_select0):p OITO 1, Job 1287236, Achtado_Count 0, PRIO 6 Agenda 11: 59: 44.941 1 00 19988592 CJQ0 0 (JSCRMRR_MARK_RUN_RESOURCE_MGR): 40: 40: 40: RUSTERCHEN1S1S1S1: 51:, PLEFT = 637 PLAN 11: 59: 44.941 1 001 001 00 19988592 Trabalhos Que Podemos Executar é 0 Plan 11: 59: 44.941 1 00 19988592 CJQ0 0 (JSCRS_SELECT0): NENHUM Trabalho Para Executar: Sched 11 59: 44.941 1 00 1998592 CJQ0 0 (JSCRCWW_COMPUT): JSCRCWW_WAIDA: 00 19988592 CJQ0 0 (JSCRCW_Compute_Wait): 0 198592 0 jobs: SCHED 11:59:44.941 1 00 19988592 CJQ0 0(jscrs_select0):Aguardando 4000 milissegundos: ... SCHED 12:01:19.509 1 9J80 19J80 19J80 19 Position 0, Job 1287236, _priocount 2 5 SCHED 12:01:19.509 1 00 19988592 CJQ0 0(jscrs_select0):Position 1, trabalho 17408, Consider_count 13, prio 6 SCHED 12:01:19.509 1 00 1998Jrs_select 0(jsc0859) :posição 2, trabalho 1287238, Considerado_contagem 12, Prio 6 SCHED 12:01:19.509 1 00 19988592 CJQ0 0(jscrs _select0):posição 3, considerado_contagem 3,712872 PRIO 6 AGRÃO 12: 01: 19.509 1 00 19988592 CJQ0 0 (JSCRMRR_MARK_RUN_RESOURCE_MGR): Digitando JSCRMRR_MARK_RUN_RESOURCE_MGR SGOND 12: 01: 19.509 1 00 19988592 19.509 1 00 19988592 CJQ0 0(jscrrsa_rm_slave_allocation):todo cg concedido total de 4

Probably this different behavior shows that DBMS_SCHEDULER is still evolving after releases.

4. Slaves used by Job Kill

First, we disabled the two incident jobs. Next, look at various task kill commands and their effects on #used_slaves.

exec dbms_scheduler.disable ('TEST_JOB_CRASHED_1', force => true, commit_semantics =>'ABSORB_ERRORS'); exec dbms_scheduler.disable ('TEST_JOB_CRASHED_2', force => true, commit_semantics => 'ABSORB_ERRORS');

4.1. Comando do SO Kill

Increase job_queue_processes from 13 to 14 to allow a slave job to run:

Change system pool job_queue_processes=14 scope=memory;

Now we can see that TEST_JOB_NORMAL_1 has changed from SCHEDULED to RUNNING (the Incident jobs are all in DISABLED state):

selecione job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ------------------ ----- -------- --------- - ------------ ----------- ------------ ---------- ------------- TEST_JOB_CRASHED_1 FALSO DESATIVADO 7 0 0 10:37:05.497 10:41:12.340 10:41:12.341 TEST_JOB_CRASHED_2 FALSO DESATIVADO 6 0 0 10:37:06.370 10:39:44.948 10:39:44.950 TEST_JOB_NORMAL_1 TRUE LÄUFT 4 0 0 11:36:31.298 11:36:18.276 11:36:31.305

Locate the OS Process ID of TEST_JOB_NORMAL_1:

select s.program, s.module, s.action, s.sid, s.serial#, p.pid, p.spid, s.event from v$session s, v$process p where s.paddr=p. addr und s.program als '%J0%'; PROGRAMMMODUL AKTION SID SERIENNUMMER PID SPID EREIGNIS -------------------- -------------- ------ - ---------- --- ------- --- ----- ----------------- oracle@testdb ( J000 ) DBMS_SCHEDULER TEST_JOB_NORMAL_1 373 27399 50 23709 PL/SQL-Blockierungstimer

Then kill it with the operating system command:

Matar -9 23709

Now we can see that TEST_JOB_NORMAL_1 is in the SCHEDULED state, just like in the case of the incident above. Therefore, the job killed by the OS command is also counted in #used_slaves (the TEST_JOB_CRASHED_1 and TEST_JOB_CRASHED_2 lines are the same, removed in the output).

selecione job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ----------------- ----- --------- --------- - ------------ ----------- ------------ ---------- ------------- TEST_JOB_NORMAL_1 WAHR GEPLANT 13 0 0 11:44:31.619 11:44:18.617 11:44:18.617

Trace CJQ0 shows "CDB Slave Limit=14, CDB Used/Reserved Slaves=14", no other jobs may be in the "RUNNING" state.

SCHED 01-18 11:49:48.662 1 00 23038 CJQ0 0(jscrsq_select_queue):Considerando o trabalho 3715271 SCHED 01-18 11:49:48.662 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Insira SCHED 8:61 418 101. 00 23038 CJQ0 0(jscr_can_pdb_run_job):CDB Slave Limit=14, CDB Used/Reserved Slaves=14, MSL=2 SCHED 01-18 11:49:48.662 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):PDB 0 Slaves: used=14 , atribuído=0, reservado=14, max=14 SCHED 01-18 11:49:48.662 1 00 23038 CJQ0 0(jscr_can_pdb_run_job):Return 0

There is no information in the database warning log about this job terminated by the operating system, but the pmon trace contains the following text:

*** 2021-01-18T11:45:36.206630+01:00 process with tag 0xb75d64a0 pid=50 serial=18 ospid=23709 just dead user session info: sid: 373 ser: 945 client details: O info /S: user: Oracle, Term: UNKNOWN, Ospid: 23709 Machine: testdb Program: oracle@testdb (J000) Application name: DBMS_SCHEDULER, hash value=2478762354 Action name: TEST_JOB_NORMAL_1, hash value=355935408

4.2. Oracle kill statement with option "immediately" (KILL HARD)

We increased job_queue_processes from 14 to 15:

Change system pool job_queue_processes=15 scope=memory;

TEST_JOB_NORMAL_1 back to the "RUNNING" status:

selecione job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ----------------- ----- ------- --------- --- ---------- ----------- ------------ ---------- -- ----------- TEST_JOB_NORMAL_1 TRUE RUNNING 13 0 0 12:17:43.620 12:17:30.610 12:17:43.627 selecione s.program, s.module, s.action, s.sid, s .serial#, p.pid, p.spid, s.event von v$session s, v$process p wobei s.paddr=p.addr und s.program wie '%J0%'; MÓDULO DE PROGRAMA AÇÃO SID SERIAL# PID SPID EVENT -------------------- -------------- ------ - ---------- --- ------- --- ---- ----------------- oracle@testdb (J000 ) DBMS_SCHEDULER TEST_JOB_NORMAL_1 722 45490 58 5571 Temporizador de bloqueio PL/SQL

Choose sid and serial#, eliminate TEST_JOB_NORMAL_1 by Oracle statement with option "immediate":

Change system break session '722,45490,@1' immediately;

Now we can see that TEST_JOB_NORMAL_1 is in the SCHEDULED state, just like in the case of the incident above. Therefore, the work eliminated by the Oracle statement with the "immediate" option is counted in #used_slaves.

selecione job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ----------------- ----- --------- --------- - ------------ ----------- ------------ ---------- ------------- TEST_JOB_NORMAL_1 WAHR GEPLANT 20 0 0 12:23:43.714 12:23:30.712 12:23:30.712

The database alarm log shows the termination of the process by "KILL HARD SAFE":

2021-01-18T12:23:54.011134+01:00 Termination of process requested for PID 5571 [Source=rdbms],[info=2] [Request issued by PID:5315, UID:100] 2021-01-18T12:23 : 54.060574+01:00 KILL SESSION for sid=(722, 45490): Reason = Change system kill session Mode = KILL HARD SAFE -/-/- Requestor = USER (orapid = 50, ospid = 5315, inst = 1 ) Owner = Process: J000 (orapid = 58, ospid = 5571) Result = ORA-0

pmon Tracking-Text:

*** 2021-01-18T12:23:54.061510+01:00 process with tag 0xb75df060 pid=58 serial=17 ospid=5571 simply dead user session info: sid: 722 ser: 45490 client details: O info /S: user: Oracle, Term: UNKNOWN, Ospid: 5571 Machine: testdb Program: oracle@testdb (J000) Application name: DBMS_SCHEDULER, hash value=2478762354 Action name: TEST_JOB_NORMAL_1, hash value=355935408

The kill instruction seems to be implemented by the subroutine call: "kpoal8() -> opiexe() -> kksExecuteCommand()".

4.3. Oracle Kill Statement ohne "sofort"-Option (KILL SOFT)

We increased job_queue_processes from 15 to 16:

Change system pool job_queue_processes=16 scope=memory;

TEST_JOB_NORMAL_1 back to the "RUNNING" status:

selecione job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ------------------ ----- -------- --------- - ------------ ----------- ------------ ---------- ------------- TEST_JOB_NORMAL_1 TRUE RUNNING 20 0 0 12:23:43.714 12:26:16.292 12:23:30.712 Auswahl an Programm, an Modul, an Aktion, an Sid , s.serial#, p.pid, p.spid, s.event von v$session s, v$process p wobei s.paddr=p.addr und s.program wie '%J0%'; MÓDULO DE PROGRAMA AÇÃO SID SERIAL# PID SPID EVENT -------------------- -------------- ------ - ---------- --- ------- --- ---- ----------------- oracle@testdb (J000 ) DBMS_SCHEDULER TEST_JOB_NORMAL_1 722 58202 58 6652 Temporizador de bloqueio PL/SQL

Choose sid and serial#, end TEST_JOB_NORMAL_1 by Oracle statement without "immediate" option and check its status:

System-Kill-Sitzung '722,58202,@1' ändern; job_name, ativado, estado, run_count, failed_count, retry_count, start_date, last_start_date, next_run_date de dba_scheduler_jobs v onde job_name como '%TEST_JOB%'; JOB_NAME ENABL STATE RUN_COUNT FAILURE_COUNT RETRY_COUNT START_DATE LAST_START_DATE NEXT_RUN_DATE ----------------- ----- -------- --------- -- ----------- ----------- ------------ ---------- - ------------ TEST_JOB_NORMAL_1 TRUE LÄUFT 24 0 0 12:28:58.543 12:29:45.639 12:28:58.549

Now we can see that TEST_JOB_NORMAL_1 can be started in "RUNNING" status. Therefore, work killed by the Oracle statement without the "immediate" option is not counted in #used_slaves.

The database alert log shows the abort process by "KILL SOFT":

2021-01-18T12:28:30.705849+01:00 opidrv kills process J000 ospid (6652) as a result of ORA-28 2021-01-18T12:28:30.754338+01:00 KILL SESSION for sid=(722, 58202 ) : Reason = change system kill session Mode = KILL SOFT -/-/- Requestor = USER (orapid = 50, ospid = 5315, inst = 1) Owner = Process: J000 (orapid = 58, ospid = 6652) Result = ORA -0

Pmon-Tracking:

*** 2021-01-18T12:28:33.714660+01:00 Process marked with 0xb75df060 pid=58 serial=18 ospid=6652 recently exited

In Oracle 12.1, all of the above job kill behaviors are not observed.

4.4. Session kill statement change Sinais UNIX, v$session and v$process changes

Here are some observations of Alter Session Kill Statement With vs. WithOut "immediate" in Oracle 18c and 19c.

(1). Send signals (track with streaks/trellis)-. Change the sysbreak session 'Sid, Serial#' immediately; sends SIGTERM to the deleted session (process). The terminated process received: SIGTERM {si_signo=SIGTERM, si_code=SI_QUEUE, si_pid=24984, si_uid=100, si_value={int=2, ptr=0x100000002}} -. Change the sysbreak session 'Sid, Serial#'; sends SIGTSTP to the deleted session (process). The killed process got: SIGTSTP {si_signo=SIGTSTP, si_code=SI_TKILL, si_pid=24984, si_uid=100} Both SIGTERM and SIGTSTP can be ignored or treated (caught) differently depending on the operating system (platform) and versions of Oracle. While its brother SIGKILL, SIGSTOP is unblockable and cannot be caught or ignored.(two). After sending the kill instruction-. If the session ends with "immediate", the UNIX process is terminated. The session disappeared from v$session because the KSSPAFLG flag in the underlined x$ksuse was changed from 1 to 6 (filtered by BITAND("S","KSSPAFLG",1)<>0). Your information is still visible in x$ksuse (v$session .SID=x$ksuse). The process has disappeared from v$process/x$ksupr. -. Session terminated Without "immediate" the UNIX process is not terminated. It's still visible in v$session/x$ksuse and v$process/x$ksupr.(3). After the kill statement, use it again to run a statement on the terminated session:-. Session ended with "immediate" throws ORA-03113. The UNIX process has already ended. It is still maintained in x$ksuse and x$ksupr. Changed the KSSPAFLG flag in the underlined x$ksuse from 6 to 2 (filtered by BITAND("S","KSSPAFLG",1)<>0). -. Terminated session WithOut "immediate" triggers ORA-00028. The UNIX process is still active (not terminated). It is no longer visible in v$session because the KSSPAFLG flag in the x$ksuse underscore has been changed from 1 to 2 (filtered by BITAND("S","KSSPAFLG",1)<>0). It is still maintained in v$process/x$ksupr and x$ksuse with KSSPAFLG=2. We can see that the UNIX process is actively responding to each session action. Every time that dead session was used again, your process got: SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_MAPERR, si_addr=0}

There are also instructions for canceling and disconnecting sessions. Here is the observed behavior:

change system cancel sql 'sid,serial number';sends SIGTSTP to the terminated session (process). The killed process received: SIGTSTP {si_signo=SIGTSTP, si_code=SI_TKILL, si_pid=24984, si_uid=100} The killed session is active in v$session and v$process. It simply aborts the current execution (even in the middle of processing or in a loop). If you use this session again, it will behave normally.Change system disconnection session 'Sid, Serial#' immediately;sends SIGTERM to the disconnected session (process). The disconnected process received: SIGTERM {si_signo=SIGTERM, si_code=SI_QUEUE, si_pid=24984, si_uid=100, si_value={int=2, ptr=0x100000002}} The disconnected session in x$ksuse has KSSPAFLG from 1 to 6 changed. Using this session again will result in ORA-03113: End of file on communication channelChange system disconnect session 'sid, serial#';sends SIGTSTP to the disconnected session (process). The disconnected process received the following: SIGTSTP {si_signo=SIGTSTP, si_code=SI_TKILL, si_pid=24984, si_uid=100} --- The disconnected session is held in v$process/x$ksupr and x$ksuse with KSSPAFLG=2. Using this session again returns ORA-00028: Your session has ended

For the Cancel statement, it's similar to kill WithOut "immediate". For the disconnect statement, the two flavors (With/WithOut) are similar to kill (With/WithOut).

We can see that the same SIGTSTP signal is treated differently. in case of:

Change the sysbreak session 'Sid, Serial#';

This renders the session unusable. Using this session again will trigger ORA-00028.

While no case of:

change system cancel sql 'sid,serial number';

It just aborts the current run. If you use this session again, it will behave normally.

By the way, we can also toggle SIGSTOP and SIGCONT to pause and resume execution of the current session:

kill -s SIGSTOP-PID kill -s SIGCONT-PID

Anyway, SIGKILL is used as a last resort to kill processes immediately. It cannot be captured or ignored, and the receiving process cannot clean up upon receiving this signal.

4.5 v$resource_limit und x$ksupr/v$process, x$ksuse/v$session

At instance startup, Oracle allocates the number of processes and sessions based on their initial_allocation values ​​specified in v$resource_limit.

select * from v$resource_limit where resource_name in('processes', 'sessions');

You can see that the number of rows in x$ksupr, x$ksuse is the same as your initial allocation values. But the processes and sessions visible in v$process and v$session are smaller because recursive sessions and unused sessions are filtered out (note: x$ksupr and x$ksuse contain a column of the same name: "KSSPAFLG")

The v$process lines are selected as follows:

select ksspaflg, bitand(ksspaflg,1), p.* from x$ksupr at where BITAND("KSSPAFLG",1)<>0;

The v$session lines are selected as follows:

selecione ksuseflg, bitand(ksuseflg,1), ksspaflg, bitand(ksspaflg,1), s.* de x$ksuse s where BITAND("S",."KSSPAFLG",1)<>0 AND BITAND("S" ."KSUSEFLG",1)<>0;

current_utilization of sessions in v$resource_limit are those selected without the BITAND("S","KUSEFLG",1)<>0 predicate.

selecione ksuseflg, bitand(kspaflg,1), ksspaflg, bitand(kspaflg,1), s.* de x$ksuse s where BITAND("S",."KSSPAFLG",1)<>0;

We can see that the session type is mapped in v$session by x$ksuse.ksuseflg as follows:

DECODE (BITAND (S.KSSPAFLG, 19),17, 'BACKGROUND',1, 'USER',2, 'RECURSIVE','?'),

and 'RECURSIVE' sessions have BITAND(S.KSSPAFLG, 19)=2. All even numbers are filtered by the predicate BITAND("S",."KSUSEFLG",1)<>0.

If the initialization values ​​are exceeded, Oracle throws the appropriate error:

ORA-00018 maximum number of sessions exceeded ORA-00020: maximum number of processes (1200) exceeded

Default values ​​for the SESSIONS parameter are calculated by (1.5 * PROCESSES) + 24.

The following MOS Notes have specific related descriptions:

-.KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION LINE FROM V$SESSION (Doc ID 1041427.6) -.Troubleshooting Guide - ORA-18: Maximum Number of Sessions (%S) Exceeded (Doc ID 1307128.1) -.Troubleshooting - ORA-18 : Maximale Anzahl von Sitzungen (%S) überschritten (Doc ID 1307128.1)

5. Date, time of work and start delay

For job TEST_JOB_NORMAL_1, its job_action contains a set_attribute of 'start_date':

-- TEST_JOB_NORMAL_1 job_action begin dbms_lock.sleep(13); dbms_scheduler.set_attribute( name => 'TEST_JOB_NORMAL_1' ,attribute => 'start_date' ,value => systimestamp); dbms_lock.sleep(47); Film;

Remove the "start_date" setting, create a similar job TEST_JOB_NORMAL_B:

begin dbms_scheduler.create_job ( job_name => 'TEST_JOB_NORMAL_B', job_type => 'PLSQL_BLOCK', job_action => 'begin dbms_lock.sleep(13); dbms_lock.sleep(47); end;', ​​​​start_date => systimestamp, repeat_interval => 'systimestamp', auto_drop => verdadeiro, ativado => verdadeiro); Film; /

We increased job_queue_processes from 16 to 17 to run TEST_JOB_NORMAL_B:

Change system pool job_queue_processes=17 scope=memory;

Both jobs are running and now we're looking for their data time (we're querying sys.scheduler$_job instead of dba_scheduler_jobs for two additional files:last_end_date, last_enabled_time):

selecione o.object_name, o.created, o.last_ddl_time, start_date, last_start_date, next_run_date, last_end_date, last_enabled_time de dba_objects o, sys.scheduler$_job j onde o.object_id = j.obj# und o.object_name like 'TEST_JOB_NORMAL_%' ; OBJECT_NAME CRIADO LAST_DDL START_DATE LAST_START_DATE NEXT_RUN_DATE LAST_END_DATE LAST_ENABLED_TIME ----------------- -------- -------- --------- --- -------------- ------------- ------------- ------ - ---------- TEST_JOB_NORMAL_1 10:37:11 15:49:50 15:49:50.791 15:49:37.786 15:49:50.810 15:49:37.758 15:49:50.810 TEST_JOB_NORMAL_B 14: 14 :55 15:49:34 14:14:55.515 15:49:34.731 15:48:34.726 15:49:34.728 15:43:34.625

Work in the simple case (TEST_JOB_NORMAL_B).start dateis (almost) the sameDBA_OBJECTS.created.Bothstart dateelast_enabled_timeare not changed after startup.last_start_dateelast_end_dateare the timestamp of the start and end of the job action (actuallylast_start_dateis the current start timestamp,last_end_dateis the previous end timestamp).next_run_dateis an expected time for the next run calculated when the current run starts (just a planned time, not an actual run time).DBA_OBJECTS.last_ddlis the last modification time of the workobject.

Not with TEST_JOB_NORMAL_1,job_actionto edit 'start date' Pro 'Systemstempel' After starting work for 13 seconds, continue for another 47 seconds. Thereforestart dateit's 13 seconds laterlast_start_date.next_run_dateelast_enabled_timeare also nearby modifiedstart date.DBA_OBJECTS.last_ddlrecords the timestamp of this change.

Now we can also query dba_scheduler_job_run_details to get the historical timestamp of job execution:

selecionar job_name, log_date, status, req_start_date, actual_start_date, session_id, slave_pid ,(actual_start_date - req_start_date) atraso de dba_scheduler_job_run_details v onde job_name = 'TEST_JOB_NORMAL_1' for v.log_date JOB_NAME LOG_DATE STATUS REQ_START_DATE ACTUALION_START_ID ---- SLAVE-DA SESS ------- ------------ --------- -------------- ----- --- ------- ---------- ------ ------------- -- TEST_JOB_NORMAL_1 RUN_COUNT=4 até o estado=GEPLANTER TEST_JOB_NORMAL_1 10: 38: 11.853 com Sucesso 10: 37: 11.681 10: 37: 11.703 731,19298 25130 +00: 00: 00.022 Test_JOB_NORMAL_1 10: 39: 12.080 Com Sucesso 10: 37: 24,769 10: 38: 12.080 00: 47.302 test_job : 12.112 tercedido 10: 38: 25.077 10: 39: 12.105 731,411 25130 +00: 00: 47.027 test_job_normal_1 10: 41: 12.153 Soded 10. 00: 00: 47.034 - AMALER TEST_QUED_QUE_PROCES_PROCESSEDE_PROCESSES PARAATORSAR OSTODAGAGE. 18.437 ERFOLGREICH 10:40:25.152 11:36:18.276 373,30624 23709 +00:55:53 .124 TEST_JOB_NORMAL_1 11: 38: 18.554 sucedeu 11: 36: 31.305 11: 37: 18.546 373.36987 23709 +00: 00: 47.240 test_job_normal_1 11: 39: 18.564 Soded 11 +00: 00: 47.003 TEST_JOB_NORMAL_1 11: 40: 18.574 sucedeu 11: 38: 31.562 11: 39: 18.566 373,54579 23709 +00: 00: 47.004 Test_Job_Normal_1 11: 41: 373,4241 23709 +00:00:47.003 TEST_JOB_NORMAL_1 11:42:18.594 SUCCEEDED 11:40:31.583 11:41:18.587 373,27399 23709 +00:00 : 47.003 test_job_normal_1 11: 43: 18.604 Nachfolger 11: 41: 31.593 11: 42: 18.597 373,41459 23709 +00: 00: 47.004 test_job_normal_1 11: 43: 18.614. +00:00:47.003 Befehl für Pelo SO (state=SCHEDULED) TEST_JOB_NORMAL_1 11:45:45.239 STOPPED 11:44:31.623 11:44:18.617 373.945 -00:00:13.006 -- aumentar job_queue_processes para fazer state=NORM_RUN_JOBNING 12:18:30.629 SUCCEED411: 12:17:30.611 722,16157 5571 +00:33:11.993 TEST_JOB_NORMAL_1 12:19:30.66 9 COM SUCESSO 12:17:43.627 12:18:30.660 722 503 +4 503 47.033

In the normal case of work execution, fromcurrent_start_dateProlog_dateis from the beginning to the end of each run.req_start_dateit is 13 seconds after the previous onecurrent_start_date.

If we use (current_start_date - req_start_date) To determine the delay in starting work, we must consider 'start date' Configuration, status 'PLANNED', task finished. Even if there is no real delay, (current_start_date - req_start_date) can show specific values, even some large or negative ones.

Run the same query for the TEST_JOB_CRASHED_1 incident job. The output lists all 7 runs (RUN_COUNT=7) with 4 calculated fields (DELAY, DURATION, START_DIFF, LOG_DIFF). ÖDELAYeSTART_DIFFare (almost) the same in the same line, but differ in different lines. During the incident, the slave job process is still active and the eviction is being performed by the slave job process itself, butSLAVE_PIDit is not filled.

selecione job_name, log_date, status, req_start_date, actual_start_date, session_id, slave_pid ,(actual_start_date - req_start_date) atraso ,(log_date - actual_start_date) duração ,(actual_start_date - lag(actual_start_date) over(order by log_date)) start_diff ,(log_date - lag( log_date) over(order by log_date)) log_diff from dba_scheduler_job_run_details v where job_name = 'TEST_JOB_CRASHED_1' order by v.log_date; JOB_NAME LOG_DATE STATUS REQ_START_DATE ACTUAL_START_DA SESSION_ID SLAVE_ DURAÇÃO DO ATRASO START_DIFF LOG_DIFF ------------- ------------ ------- - - ------------ ---------- ---------- ------ ------ - ------ ------------- ------------- ------------- TEST_JOB_CRASHED_1 10:37 : 44.948 PARADO 10:37:06.351 10:37:06.416 375,57672 +00:00:00.064 +00:00:38.532 TEST_JOB_CRASHED_1 10:38:12.048 PARADO 10:37:06.439 10:37:438 +63.00 05.052 10:38:44.843 00:27.020 +00:00:32.795 TEST_JOB_CRASHED_1 10:39:12.082 PARADO 10:38:12.075 10:38:44.883 375,55842 +00:00:32.808 +00:00:20:199 00:32.810 +00:00 00:32.810 12.114 PARADO 10:39:12.109 10:39:44.932 375,49907 +00:00:32.822 +00:00:27.182 +00:00:32. 825 +00:00:27.221 TEST_JOB_CRASHED_1 10:41:24.323 PARADO 10:39:44.934 10:41:12.340 731,55755 +00:01:27.405 +00:00:11.983 +00:01:27.408: +00:01 12.209select job_name, log_date, operation, status,dbms_lob.substr(additional_info, 120,1) add_info from dba_scheduler_job_log v where job_name like 'TEST_JOB_CRASHED_1' order by v.log_date; JOB_NAME LOG_DATUM BETRIEBSSTATUS ADD_INFO ------------------ ------------ --------- ------ - ----------------------------------------- TEST_JOB_CRASHED_1 10:37:44.790 EXECUÇÃO PARADA REASON="O Processo Escravo do Trabalho for Encerrado" TEST_JOB_CRASHED_1 10:38:12.047 RUN STOPPED REASON="O Processo Escravo Do Trabalho for Encerrado" TEST_JOB_CRASHED_1 10:38:44.843 RUN STOPPED REASON="O Processo Escravo Do Trabalho" TEST_OBCRASH1 TEST_OBCRASH1 10:38:12.047 RUN STOPPED 10:39:12.082 RUN STOPPED REASON="O Processo Escravo do Trabalho for Encerrado" TEST_JOB_CRASHED_1 10:39:44.892 RUN STOPPED REASON="O Processo Escravo Do Trabalho For Encerrado" TEST_JOB_CRASHED_1 10:40:12.114 RUN STOPPED Processo escravo do trabalho foi encerrado" TEST_JOB_CRASHED_1 10:41:24.323 RUN STOPPED REASON="O processo escravo da tarefa foi finalizado"

In fact, as we have seensys.scheduler$_job_run_detailsis filled in by the working session that convenes: "jslgLogJobRunInt() -> OCIStmtExecute()"comLOG_DATE = SYSTIMESTAMP, Duringsys.scheduler$_event_logis inserted by both the work session and the control session (enable/disable/stop/enable).

A similar OEM Grid Control query looks like this:

selecione j.job_name, j.owner, caso em que j.state = 'SCHEDULED' então to_char (j.next_run_date, 'DD-MON-YYYY HH24:MI:SS TZH:TZM') caso contrário, final nuloched_date, caso em que j. state = 'RUNNING' then ( (sys_extract_utc (systimestamp) + 0) - (sys_extract_utc (j.last_start_date) + 0))* 24*60 else null end duration_mins, j.state from dba_scheduler_jobs j, dba_scheduler_running_jobs r where ( (j. state = 'RUNNING' oder j.state = 'CHAIN_STALLED') oder (j.state = 'SCHEDULED')) e j.job_subname é null e r.job_subname é null e j.job_name = r.job_name(+) e j . Eigentümer = r.Eigentümer(+) e ( ( j.state = 'SCHEDULED' e ( (sys_extract_utc (j.next_run_date) + 0) - (sys_extract_utc (systimestamp) + 0)) * 24*60 < 24*60) ou (j.state = 'RUNNING' oder j.state = 'CHAIN_STALLED'))unionselect sj.job_name, sj.owner, null Scheduled_date, extrair (dia de 24 * 60 * sj.run_duration) duration_mins, sj.status de dba_scheduler_job_run_details sj onde ( ( sj.status = 'PARADO' e ( (sys_extract_utc (systimestamp) + 0) - ( ( sys_extract_utc (sj.actual_start_date) + 0) + extract (dia de sj.run_duration))) * 24*60 < 24* 60) ou ( sj.status = 'FAILED' e ( (sys_extract_utc (systimestamp) + 0) - ( (sys_extract_utc (sj.actual_start_date) + 0) + extrato (dia de sj.run_duration))) * 24*60 < 24*60));

6. Test cleaning

After the test, we can delete the test by following the procedure below:

create or replace procedure clearup_test asbegin for c in (select * from dba_scheduler_jobs where job_name as '%TEST_JOB%') loop start --set DBA_SCHEDULER_JOBS.enabled=FALSE dbms_scheduler.disable(c.job_name, force => true, commit_semantics = > ' ABSORB_ERRORS'); --set DBA_SCHEDULER_JOBS.enabled=TRUE to allow execution to be scheduled (state='RUNNING') -- dbms_scheduler.enable (c.job_name, commit_semantics =>'ABSORB_ERRORS'); Exception if others are null; The end; last loop; for c in (select * from dba_scheduler_running_jobs, where job_name is '%TEST_JOB%') start of loop --if force=FALSE, stops job normally, slave process can update status of job in job queue. --If force=TRUE, the scheduler terminates the slave job immediately. --To retry job with attribute "start_date => systimestamp" and enabled = TRUE, --restart immediately (status changed from 'SCHEDULED' to 'RUNNING'), DBA_SCHEDULER_JOBS.run_count increases by 1. dbms_scheduler.stop_job (e.g .job_name , force => true, commit_semantics => 'ABSORB_ERRORS'); Exception if others are null; The end; last loop; for c in (select * from dba_scheduler_jobs where job_name like '%TEST_JOB%') loop begin --If force=TRUE, the scheduler first tries to stop the running job instances --(by issuing the STOP_JOB call with the force flag set false) and then discards the jobs. dbms_scheduler.drop_job (c.job_name, force => true, commit_semantics => 'ABSORB_ERRORS'); Exception if others are null; end;end loop;end;/exec clearup_test;