Use the DBMS_SCHEDULER package to manage scheduled tasks and dbmsscheduler

Use the DBMS_SCHEDULER package to manage scheduled tasks and dbmsscheduler

Dbms_scheduler is a package provided by Oracle to create scheduled tasks. The task type can be PL \ SQL program, external scripts, and operating system commands. It is usually used to create scheduled tasks, it is stored in the database without dependency on the operating system and is not affected during database migration. If an error occurs, logs can be queried, which is convenient for creation and use. For example, to implement a data backup plan, save the data backup steps in day_backup.sh, and then create a plan to regularly perform this step:

$ vim /oracle/db_backup.sh#!/bin/shORACLE_SID=sydb;export ORACLE_SIDORACLE_HOME=/u01/app/product/11.2.0/db_1;export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$PATH; export PATHLD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATHLOCAL=/EXPORT;export LOCAL$ORACLE_HOME/bin/rman target sys/oracle catalog rman/rman log='/tmp/db_backup.log' append <<EOFrun{allocate channel dev type disk;backup incremental level 1 database plus archivelog format '/disk2/backup/sydb/%d_%U_%T.bkp'tag 'db_backup';release channel dev;}exit;EOF

Note: you need to set the environment variables here. It takes a lot of effort to set the file permissions and environment variables correctly;

Create a scheduled task:

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'db _ backup', -- job name job_type => 'executable', -- job task type, for details, refer to the description in job_action => '/oracle/db_backup.sh' in section 2, -- job operation start_date => to_date ('2017-05-19 03:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), -- if the execution time and execution interval are null, it indicates immediate execution/* 12 days execution once */repeat_interval => 'freq = MONTHLY; BYMONTHDAY = 15, -1', -- execution interval enabled => true, -- whether to enable/* next night at PM */comments => 'nightly incremental backups'); END ;/

Detailed description of supported task types:

job_typeThis attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are:'PLSQL_BLOCK'This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0.'STORED_PROCEDURE'This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported.'EXECUTABLE'This specifies that the job is external to the database. External jobs are anything that can be executed from the command line of the operating system. Anydata arguments are not supported with a job or program type of EXECUTABLE. The job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.'CHAIN'This specifies that the job is a chain. Arguments are not supported for a chain, so number_of_arguments must be 0.

Example

Create a table and then insert data:

Create table t_insert (cname varchar2 (30), cnum number (3, 4);/create or replace procedure fun_insertdata/* function: insert data into the t_insert table edit: 2015-05-15 */asv_ SQL varchar2 (300); begin v_ SQL: = 'insert into t_insert (cname, cnum) values (: 1,: 2) '; for I in 1 .. 10000 loop execute immediate v_ SQL using 'aaa', I; end loop; commit; end ;/

Finally, create an immediate task.

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'insert _ DATATOTAB ', -- job name job_type => 'plsql _ Block', -- job task type, for details, see job_action => '-- job operation BEGIN fun_insertdata; END;', start_date => NULL, -- if the execution time and execution interval are null, indicates immediate execution/* 12 days execution once */repeat_interval => NULL, -- execution interval enabled => true, -- enable/* next night at PM */comments => 'nightly incremental backups'); END ;/

Query results:

SQL> column job_name format a15SQL> column status format a8SQL> column SESSION_ID format a7SQL> column SLAVE_PID format a7SQL> SELECT JOB_NAME,STATUS,session_id,slave_pid,cpu_used FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME='INSERT_DATATOTAB' and owner='SYS' ;JOB_NAME        STATUS   SESSION SLAVE_P CPU_USED--------------- -------- ------- ------- ---------------------------------------------------------------------------INSERT_DATATOTA SUCCEEDE 173,263 5228    +000 00:00:01.36B               D        3

Therefore, scheders can be queried in the dbms_schedulers view. The execution result of each scheduler task can be queried in DBA_SCHEDULER_JOB_RUN_DETAILS. If you want to periodically execute a job, you can configure repeat_interval, for example, this configuration can be performed on the first, fifth, and last day of each month.

Repeat_interval => 'freq = MONTHLY; BYMONTHDAY =,-1' -- execution interval,-1 indicates the last day of each month

The above uses a FREQ =, which indicates the execution time cut:

YEARLY indicates every year
MONTHLY indicates every January
WEEKLY indicates every week
DAILY indicates every day
HOURLY indicates every hour
MINUTELY indicates minute
SECONDLY indicates every second

So (FREQ = MONTHLY; BYMONTHDAY =,-1) MONTHLY indicates that the cycle is executed every month, and BYMONTHDAY indicates that the execution is performed in the day of each month;

Manual execution plan

SQL> EXEC DBMS_SCHEDULER.RUN_JOB('DB_BACKUP');

Note: by default, the task is not enabled unless it is set (enabled => true) during plan creation. so if the plan is not enabled, enable it first:

EXEC DBMS_SCHEDULER.enable('DB_BACKUP');

Delete plan

SQL> exec dbms_scheduler.drop_job('DB_BACKUP');

-- Then end)