Manage scheduled tasks using the Dbms_scheduler package

Dbms_scheduler is a package that Oracle provides to create scheduled tasks, such as executing pl\sql programs, executing external scripts, invoking operating system commands, typically for creating scheduled tasks, not relying on the operating system, saving in a database, and not being affected when the database is migrated. A log can be queried for errors, which is easier to create and use. For example, implement a data backup plan, save the steps of the data backup in day_backup.sh, and then create a schedule to perform that step regularly:

$ 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: Here to set environment variables, here eat a lot of bitterness, in short, the file permissions and environment variables to set the correct;

To create a scheduled task:

BEGINDBMS_SCHEDULER.CREATE_JOB(job_name=>‘DB_BACKUP‘, --job名称job_type => ‘EXECUTABLE‘, --job任务类型,请参考2中的解释job_action =>‘/oracle/db_backup.sh‘, --job操作start_date=> to_date(‘2015-05-19 03:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘),--执行时间和执行间隔时间为null,则表示立即执行/*12 天执行一次*/repeat_interval=>‘FREQ=MONTHLY;BYMONTHDAY=15,-1‘,--执行间隔时间enabled=>true,--是否启用/* next night at 11:00 PM */comments => ‘Nightly incremental backups‘);END;/

Detailed description of supported task types:

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

Example

Create a table, and then create the process for inserting the data:

create table t_insert(cname varchar2(30),cnum number(3,4);/create or replace procedure fun_insertdata/*功能:向t_insert 表中插入数据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 a task that executes immediately

BEGINDBMS_SCHEDULER.CREATE_JOB(job_name=>‘INSERT_DATATOTAB‘, --job名称job_type => ‘PLSQL_BLOCK‘, --job任务类型,请参考2中的解释job_action =>‘--job操作BEGIN    fun_insertdata; END; ‘, start_date=>NULL, --执行时间和执行间隔时间如果为null,则表示立即执行/*12 天执行一次*/repeat_interval=>NULL,--执行间隔时间enabled=>true,--是否启用/* next night at 11:00 PM */comments => ‘Nightly incremental backups‘);END;/ 

Query Result:

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

So the SCHEDULER can be queried in the Dbms_schedulers view, the results of each SCHEDULER task can be queried in dba_scheduler_job_run_details; If you want to perform a job on a regular basis, you can configure it by configuring Repeat_interval, such as 1, 5, and last day of the month

repeat_interval=>‘FREQ=MONTHLY;BYMONTHDAY=1,5,-1‘--执行间隔时间 ,-1表示每月最后一天

The above is used to a freq=, which indicates the time of execution of the truncation:

Yearly says every year
MONTHLY says every January
WEEKLY says every week
DAILY means every day
HOURLY means every hour
minutely means every minute
Secondly means every second

So above the (freq=monthly; BYMONTHDAY=1,5,-1) monthly indicates that the cycle is executed monthly, bymonthday that the days of the month are executed;

To execute a plan manually

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

Note: Tasks are not enabled by default unless the schedule is created (enabled=>true), so if the schedule is not enabled, first enable it:

EXEC DBMS_SCHEDULER.enable(‘DB_BACKUP‘);

Delete Schedule

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

--then End (2015-05-18)

Manage scheduled tasks using the Dbms_scheduler package