概要
スケジューラ自動タスクの再作成方法についてのメモ
前提
ウィンドウがオープンされるべきではない時間帯にオープンされていたり、ウインドウがクローズされるべき時間に
DBA_SCHEDULER_WINDOWS.ACTIVE=TRUEになっていることが解消できない場合、またはDBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIMEで
過去の日付が示された場合には、ウィンドウを削除して再作成してみてください。
SQL> SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,NEXT_START_DATE,NEXT_START_DATE,LAST_START_DATE,ENABLED,COMMENTS FROM DBA_SCHEDULER_WINDOWS WHERE ENABLED = 'TRUE'; WINDOW_NAME REPEAT_INTERVAL DURATION NEXT_START_DATE NEXT_START_DATE ENABL -------------------- ------------------------------------------------------- -------------------- ---------------------------------------- ---------------------------------------- ----- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 08-NOV-21 10.00.00.000000 PM ASIA/TOKYO 08-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE TUESDAY_WINDOW freq=daily;byday=TUE;byhour=17;byminute=0; bysecond=0 +000 04:00:00 09-NOV-21 05.00.00.000000 PM ASIA/TOKYO 09-NOV-21 05.00.00.000000 PM ASIA/TOKYO TRUE WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 03-NOV-21 10.00.00.000000 PM ASIA/TOKYO 03-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 04-NOV-21 10.00.00.000000 PM ASIA/TOKYO 04-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 05-NOV-21 10.00.00.000000 PM ASIA/TOKYO 05-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 06-NOV-21 06.00.00.000000 AM ASIA/TOKYO 06-NOV-21 06.00.00.000000 AM ASIA/TOKYO TRUE SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 07-NOV-21 06.00.00.000000 AM ASIA/TOKYO 07-NOV-21 06.00.00.000000 AM ASIA/TOKYO TRUE
コマンド
-- メンテナンスウィンドウの削除（※エラーが出る場合がある）
@?/rdbms/admin/catnomwn.sql
-- ウィンドウの手動削除
execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');
-- ウィンドウの作成
@?/rdbms/admin/catmwin.sql
再作成
SQL> conn / as sysdba Connected. SQL> alter session set container=pdb1; Session altered. SQL> sho user con_name USER is "SYS" CON_NAME ------------------------------ PDB1 SQL> @?/rdbms/admin/catnomwn.sql BEGIN dbms_scheduler.drop_job('gather_stats_job'); END; * ERROR at line 1: ORA-27475: unknown job "SYS"."GATHER_STATS_JOB" ORA-06512: at "SYS.DBMS_ISCHED", line 278 ORA-06512: at "SYS.DBMS_SCHEDULER", line 751 ORA-06512: at line 1 BEGIN dbms_scheduler.drop_window_group('MAINTENANCE_WINDOW_GROUP'); END; * ERROR at line 1: ORA-27479: Cannot drop "SYS"."MAINTENANCE_WINDOW_GROUP" because other objects depend on it ORA-06512: at "SYS.DBMS_ISCHED", line 650 ORA-06512: at "SYS.DBMS_SCHEDULER", line 1494 ORA-06512: at line 1 PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. BEGIN dbms_scheduler.drop_job_class('AUTO_TASKS_JOB_CLASS'); END; * ERROR at line 1: ORA-27476: "SYS"."AUTO_TASKS_JOB_CLASS" does not exist ORA-06512: at "SYS.DBMS_ISCHED", line 518 ORA-06512: at "SYS.DBMS_SCHEDULER", line 945 ORA-06512: at line 1 PL/SQL procedure successfully completed. BEGIN dbms_resource_manager.delete_consumer_group('AUTO_TASK_CONSUMER_GROUP'); END; * ERROR at line 1: ORA-29368: consumer group AUTO_TASK_CONSUMER_GROUP does not exist ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1633 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 390 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 1616 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 226 ORA-06512: at line 1 PL/SQL procedure successfully completed. SQL> execute dbms_scheduler.drop_window('MONDAY_WINDOW'); execute dbms_scheduler.drop_window('TUESDAY_WINDOW'); execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW'); execute dbms_scheduler.drop_window('THURSDAY_WINDOW'); execute dbms_scheduler.drop_window('FRIDAY_WINDOW'); execute dbms_scheduler.drop_window('SATURDAY_WINDOW'); execute dbms_scheduler.drop_window('SUNDAY_WINDOW'); PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> PL/SQL procedure successfully completed. SQL> @?/rdbms/admin/catmwin.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Session altered. SQL> SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,NEXT_START_DATE,NEXT_START_DATE,LAST_START_DATE,ENABLED FROM DBA_SCHEDULER_WINDOWS WHERE ENABLED = 'TRUE'; WINDOW_NAME REPEAT_INTERVAL DURATION NEXT_START_DATE NEXT_START_DATE LAST_START_DATE ENABL -------------------- ------------------------------------------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----- MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 08-NOV-21 10.00.00.000000 PM ASIA/TOKYO 08-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 09-NOV-21 10.00.00.000000 PM ASIA/TOKYO 09-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 03-NOV-21 10.00.00.000000 PM ASIA/TOKYO 03-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 04-NOV-21 10.00.00.000000 PM ASIA/TOKYO 04-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 05-NOV-21 10.00.00.000000 PM ASIA/TOKYO 05-NOV-21 10.00.00.000000 PM ASIA/TOKYO TRUE SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00 06-NOV-21 06.00.00.000000 AM ASIA/TOKYO 06-NOV-21 06.00.00.000000 AM ASIA/TOKYO TRUE SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00 07-NOV-21 06.00.00.000000 AM ASIA/TOKYO 07-NOV-21 06.00.00.000000 AM ASIA/TOKYO TRUE
