Use DBMS_SCHEDULER package to manage scheduled tasks

Use DBMS_SCHEDULER package to manage scheduled tasks

Read navigation

• Create a scheduled task:
• Detailed description of supported task types:
• Example
• Manual execution plan
• Delete plan

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/sh
ORACLE_SID = sydb;
Export ORACLE_SID
ORACLE_HOME =/u01/app/product/11.2.0/db_1;
Export ORACLE_HOME
PATH = $ ORACLE_HOME/bin: $ PATH;
Export PATH
LD_LIBRARY_PATH = $ ORACLE_HOME/lib;
Export LD_LIBRARY_PATH
LOCAL =/EXPORT;
Export LOCAL
$ ORACLE_HOME/bin/rman target sys/oracle catalog rman/rman log = '/tmp/db_backup.log' append <EOF
Run
{
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:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
Job_name => 'db _ backup', -- job name
Job_type => 'executable', -- job Task Type, please refer to the explanation in 2
Job_action => '/oracle/db_backup.sh', -- job operation
Start_date => to_date ('2017-05-19 03:00:00 ', 'yyyy-mm-dd hh24: mi: ss'), -- the execution time and execution interval are null, indicates immediate execution.
/* Execute once every 12 days */
Repeat_interval => 'freq = MONTHLY; BYMONTHDAY = 15,-1', -- execution Interval
Enabled => true, -- enable or not
/* Next night at 11: 00 PM */
Comments => 'nightly incremental backups ');
END;
/

 

Detailed description of supported task types:
Job_type
This 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 to the t_insert table.
Edit: 2015-05-15
*/
As
V_ 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.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
Job_name => 'insert _ DATATOTAB ', -- job name
Job_type => 'plsql _ Block', -- job Task Type, please refer to the explanation in 2
Job_action => '-- job operation
BEGIN
Fun_insertdata;
END;
',
Start_date => NULL, -- execution time and execution interval if it is null, it indicates immediate execution
/* Execute once every 12 days */
Repeat_interval => NULL, -- execution Interval
Enabled => true, -- enable or not
/* Next night at 11: 00 PM */
Comments => 'nightly incremental backups ');
END;
/

Query results:
SQL> column job_name format a15
SQL> column status format a8
SQL> column SESSION_ID format a7
SQL> column SLAVE_PID format a7
SQL> 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. 36
B 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)