Oracle Scheduler学习

参考文档：
Primary Note: Overview of Oracle Scheduler (Doc ID 1485539.1)

Oracle® Database Administrator's Guide
12c Release 1 (12.1)
E17636-21
Chapter(30) Administering Oracle Scheduler
Examples of Using the Scheduler
http://docs.oracle.com/cd/E16655_01/server.121/e17636/schedadmin.htm#ADMIN12062

Oracle® Database Administrator's Guide
12c Release 1 (12.1)
E17636-21
Chapter(28) Oracle Scheduler Concepts
http://docs.oracle.com/cd/E16655_01/server.121/e17636/schedover.htm#ADMIN033

Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14
Chapter(138) DBMS_SCHEDULER
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sched.htm#ARPLS72235

Oracle® Database 
Database Administrator’s Guide 19c 
E96348-16 September 2022
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/index.html

Creating a scheduler job that inserts the systimestamp into a table every second

SQL> create table messages (message varchar2(100) not null);Table created.SQL> select * from messages;no rows selectedSQL> Begin2    DBMS_SCHEDULER.CREATE_JOB (3             job_name             => 'job_runs_every_second',4             job_type             => 'PLSQL_BLOCK',5             job_action           => 'Begin insert into messages values (''Current Timestamp is: '' || SYSTIMESTAMP); commit; End;',6             start_date           => SYSTIMESTAMP,7             repeat_interval      => 'freq=secondly; interval=1',8             end_date             => null,9             enabled              => TRUE,10             comments             => 'A job scheduled to insert into messages table the current timestamp every second');11  End;12  /PL/SQL procedure successfully completed.SQL> Begin2    DBMS_SCHEDULER.RUN_JOB (job_name => 'job_runs_every_second');3  End;4  /PL/SQL procedure successfully completed.SQL> select * from messages;MESSAGE
----------------------------------------------------------------------------------------------------
Current Timestamp is: 24-AUG-12 04.57.00.647000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.01.624000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.02.757000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.03.807000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.04.816000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.05.814000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.06.813000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.07.812000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.08.813000 PM +02:00
Current Timestamp is: 24-AUG-12 04.57.09.810000 PM +02:0010 rows selected.SQL> Begin2    DBMS_SCHEDULER.DISABLE (name => 'job_runs_every_second', FORCE=>TRUE);3  End;4  /PL/SQL procedure successfully completed.SQL> Begin2    DBMS_SCHEDULER.DROP_JOB (job_name => 'job_runs_every_second');3  End;4  /PL/SQL procedure successfully completed.SQL> drop table messages;Table dropped.

Creating a scheduler job that gathers <USER> CBO stats every day at mid-night and every weekend day at 06:00 am

-- create weekdays schedule
SQL> Begin2    DBMS_SCHEDULER.CREATE_SCHEDULE (3        schedule_name => 'WEEKDAYS_MID_NIGHTS_SCHEDULE',4        start_date => NULL, -- If start_date is null, then the date that the job or window is enabled is used.5        repeat_interval => 'freq=daily;byhour=0;byminute=0;bysecond=0;byday=MON,TUE,WED,THU,FRI',6        end_date => NULL,7        comments => 'starts every day Mon-Fri at 12:00 am');8  End;9  /PL/SQL procedure successfully completed.-- create weekends schedule
SQL> Begin2    DBMS_SCHEDULER.CREATE_SCHEDULE (3        schedule_name => 'WEEKENDS_SCHEDULE',4        start_date => NULL, -- If start_date is null, then the date that the job or window is enabled is used.5        repeat_interval => 'freq=daily;byhour=6;byminute=0;bysecond=0;byday=SAT,SUN',6        end_date => NULL,7        comments => 'starts every weekend SAT-SUN at 06:00 am');8  End;9  /PL/SQL procedure successfully completed.-- create weekdays window
SQL> Begin2    DBMS_SCHEDULER.CREATE_WINDOW (3        window_name => 'WEEKDAYS_MID_NIGHTS_WINDOW',4        resource_plan => 'DEFAULT_MAINTENANCE_PLAN',5        schedule_name => 'WEEKDAYS_MID_NIGHTS_SCHEDULE',6        duration => '0 06:00:00',7        window_priority => 'HIGH',8        comments => 'window opens every day Mon-fri at 12:00 am and ends at 06:00 am');9  End;10  /PL/SQL procedure successfully completed.-- create weekends window
SQL> Begin2    DBMS_SCHEDULER.CREATE_WINDOW (3        window_name => 'WEEKENDS_WINDOW',4        resource_plan => 'DEFAULT_MAINTENANCE_PLAN',5        schedule_name => 'WEEKENDS_SCHEDULE',6        duration => '0 06:00:00',7        window_priority => 'HIGH',8        comments => 'window opens every Saturday & Sunday at 06:00 am');9  End;10  /PL/SQL procedure successfully completed.-- create a window group for both weekdays and weekends schedule
SQL> Begin2    DBMS_SCHEDULER.CREATE_GROUP (3     group_name => 'MAINTENANCE_GROUP',4     group_type => 'WINDOW',5     member => 'WEEKDAYS_MID_NIGHTS_WINDOW,WEEKENDS_WINDOW',6     comments => 'This group is designed for grouping weekdays and weekends maintenance windows');7  End;8  /PL/SQL procedure successfully completed.-- create stored procedure
SQL> CREATE OR REPLACE PROCEDURE "SYS"."GATHER_<USER>_STATISTICS_PROC" AS2  Begin3     DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '<USER>', estimate_percent => 100);4  End;5  /Procedure created.-- create program that runs the stored procedure
SQL> Begin2    DBMS_SCHEDULER.CREATE_PROGRAM (3     program_name             => 'GATHER_<USER>_STATS_PROGRAM',4     program_type             => 'STORED_PROCEDURE',5     program_action           => '"SYS"."GATHER_<USER>_STATISTICS_PROC"',6     number_of_arguments      => 0,7     enabled                  => TRUE,8     comments                 => 'This is the program unit that calls the concerned stored procedure');9  End;10  /PL/SQL procedure successfully completed.-- create job
SQL> Begin2    DBMS_SCHEDULER.CREATE_JOB (3     job_name                => 'GATHER_<USER>_STATISTICS_JOB',4     program_name            => 'GATHER_<USER>_STATS_PROGRAM',5     schedule_name           => 'MAINTENANCE_GROUP',6     enabled                 => TRUE,7     auto_drop               => FALSE,8     comments                => 'Job that gathers CBO stats for <USER> schema everyday at 12:00 am and every weekend at 06:00 am',9     job_style               => 'REGULAR');10  End;11  /PL/SQL procedure successfully completed.

File Watcher Case Study

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 6 13:31:09 2014Copyright (c) 1982, 2013, Oracle.  All rights reserved.Enter user-name: / as sysdbaConnected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> --
SQL> -- Setting the monitoring repeat interval to 1 minute
SQL> --
SQL> begin2      dbms_scheduler.set_attribute ('FILE_WATCHER_SCHEDULE', 'REPEAT_INTERVAL', 'FREQ=MINUTELY;INTERVAL=1');3  end;4  /PL/SQL procedure successfully completed.SQL> grant dba to fw identified by fw;Grant succeeded.SQL> conn fw/fw
Connected.
SQL> --
SQL> -- creating necessary credential
SQL> --
SQL> begin2      dbms_scheduler.create_credential('FW_CREDENTIAL', 'Administrator', 'vcap4CEL');3  end;4  /PL/SQL procedure successfully completed.SQL> --
SQL> -- creating file watcher
SQL> --
SQL> begin2      dbms_scheduler.create_file_watcher (3          file_watcher_name => 'FW_FILE_WATCHER',4          directory_path => 'C:\xxx',5          file_name => '*.txt',6          credential_name => 'FW_CREDENTIAL',7          destination => null,8          enabled => false);9  end;10  /PL/SQL procedure successfully completed.SQL> --
SQL> -- creating monitoring table
SQL> --
SQL> create table fw_monitoring (2      file_timestamp timestamp with time zone,3      directory_path varchar2(4000),4      file_size number);Table created.SQL> --
SQL> -- creating procedure
SQL> --
SQL> create or replace procedure FW_PROC (fw_result in sys.scheduler_filewatcher_result) as2  begin3      insert into fw_monitoring values (4          fw_result.file_timestamp,5          fw_result.directory_path || '/' || fw_result.actual_file_name,6          fw_result.file_size);7  end;8  /Procedure created.SQL> --
SQL> -- creating program
SQL> --
SQL> begin2      dbms_scheduler.create_program (3          program_name => 'FW.FW_PROG',4          program_type => 'STORED_PROCEDURE',5          program_action => 'FW_PROC',6          number_of_arguments => 1,7          enabled => false);8  end;9  /PL/SQL procedure successfully completed.SQL> --
SQL> -- defining metadata argument
SQL> --
SQL> begin2      dbms_scheduler.define_metadata_argument (3          program_name => 'FW.FW_PROG',4          metadata_attribute => 'event_message',5          argument_position => 1);6  end;7  /PL/SQL procedure successfully completed.SQL> --
SQL> -- creating job
SQL> --
SQL> begin2      dbms_scheduler.create_job (3          job_name => 'FW.FW_JOB',4          program_name => 'FW.FW_PROG',5          event_condition => null,6          queue_spec => 'FW_FILE_WATCHER',7          auto_drop => false,8          enabled => false);9  end;10  /PL/SQL procedure successfully completed.SQL> --
SQL> -- setting parallel instances
SQL> --
SQL> begin2      dbms_scheduler.set_attribute ('FW.FW_JOB','PARALLEL_INSTANCES', true);3  end;4  /PL/SQL procedure successfully completed.SQL> --
SQL> -- enable program, job and file watcher
SQL> --
SQL> begin2      dbms_scheduler.enable ('FW.FW_PROG, FW.FW_JOB, FW_FILE_WATCHER');3  end;4  /PL/SQL procedure successfully completed.SQL> -- create <file.txt> in 'c:\xxx' folder
SQL> select to_char(sysdate, 'dd-mm-yyyy hh:mi:ss') as "current time" from dual;current time                                                                                                                                                                                                                                                                                         
-------------------                                                                                                                                                                                                                                                                                         
06-03-2014 01:27:54                                                                                                                                                                                                                                                                                         SQL> select * from fw_monitoring;no rows selectedSQL> -- wait for at least 1 minute
SQL> select to_char(sysdate, 'dd-mm-yyyy hh:mi:ss') as "current time" from dual;current time                                                                                                                                                                                                                                                                                                
-------------------                                                                                                                                                                                                                                                                                         
06-03-2014 01:29:00                                                                                                                                                                                                                                                                                         SQL> select * from fw_monitoring;FILE_TIMESTAMP                                               DIRECTORY_PATH                  FILE_SIZE                                                                                                  
------------------------------------------------------------ ------------------------------ ----------                                                                                                  
06-MAR-14 01.27.32.376000 PM +00:00                          C:\xxx/file.txt                      0                                                                                                  

END