Oracle Job scheduled task usage tutorial, oraclejob usage tutorial

Oracle Job scheduled task usage tutorial, oraclejob usage tutorial
How to Use OracleJob scheduled tasks

What jobs in oracle can do for you is to execute the stored procedure in the specified time format and regularly execute a task. The following is a small case where a data entry is inserted to a table every 15 minutes.

Primary process 1. Create a test table

-- Create tablecreate table A8(  a1 VARCHAR2(500))tablespace DSP_DATA  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );

2. Create a stored procedure to insert data to the test table

Create or replace procedure proc_add_test asbegin insert into a8 values (to_char (sysdate, 'yyyy-mm-dd hh: mi ');/* insert data to the test table */commit; end;

3. Create a job scheduled task to automatically call the Stored Procedure (current time)

Declare job number; BEGIN DBMS_JOB.SUBMIT (JOB => job,/* automatically generate JOB_ID */WHAT => 'proc _ add_test ;', /* Name of the stored procedure to be executed or SQL statement */NEXT_DATE => sysdate + 3/(24*60 ), /* initial execution time-next 3 minutes */INTERVAL => 'trunc (sysdate, ''mi'') + 1/(24*60) '/* execute once every 1 minute */); commit; end;

4. That is, the stored procedure should be executed every 1 minute starting from. The following is the data of the test table as.

2. Common commands 1. You can view the job information in the query system table

select * from user_jobs；

2. How to manually call a job by SQL? (Directly calling a job can ignore the start time)

Begin DBMS_JOB.RUN (40);/* 40 job id */end;

3. delete a task

Begin/* Delete automatically executed job */dbms_job.remove (40); end;

4. Stop a job

Dbms. broken (job, broken, nextdate); dbms_job.broken (v_job, true, next_date);/* stop a job. The true parameter in the job is also false, next_date (stop at a certain time) or sysdate (stop immediately ). */

5. Modify the interval

dbms_job.interval(job，interval);

6. Modify the next execution time

dbms_job.next_date(job，next_date);

7. Modify the operation to be executed

Dbms_job.what (jobno, 'SP _ fact_charge_code; '); -- modify a job name

Iii. Other knowledge 1. Description of main fields in the user_jobs table storing job information

Column name	Data Type	Explanation
JOB	NUMBER	Unique ID of a task
LOG_USER	VARCHAR2 (30)	Submitter
PRIV_USER	VARCHAR2 (30)	Users with task Permissions
SCHEMA_USER	VARCHAR2 (30)	User Mode for task syntax analysis
LAST_DATE	DATE	Time when the last task was successfully run
LAST_SEC	VARCHAR2 (8)	For example, HH24: MM: the hour, minute, and second of the SS format last_date.
THIS_DATE	DATE	The start time of the running task. if the task is not running, it is null.
THIS_SEC	VARCHAR2 (8)	For example, HH24: MM: this_date in SS format: hour, minute, and second
NEXT_DATE	DATE	Time of the next scheduled task
NEXT_SEC	VARCHAR2 (8)	For example, HH24: MM: next_date in SS format: hour, minute, and second
TOTAL_TIME	NUMBER	The total time required for running the task, in seconds.
BROKEN	VARCHAR2 (1)	Mark parameter. Y indicates that the task is interrupted and will not be run later.
INTERVAL	VARCHAR2 (200)	Expression used to calculate the next line time
FAILURES	NUMBER	Number of consecutive unsuccessful tasks
WHAT	VARCHAR2 (2000)	PL/SQL block for task execution

2. Example of common values of INTERVAL Parameters

Name	Usage
Every day at midnight	"TRUNC (SYSDATE + 1 )"
Every morning	"TRUNC (SYSDATE + 1) + (8*60 + 30)/(24*60 )"
Every Tuesday	"NEXT_DAY (TRUNC (SYSDATE)," TUESDAY ") + 12/24"
Midnight on the first day of each month	"TRUNC (LAST_DAY (SYSDATE) + 1 )"
PM on the last day of each quarter	"TRUNC (ADD_MONTHS (SYSDATE + 2/24, 3)," Q ")-1/24"
Every Saturday and 06:10 AM	"TRUNC (LEAST (NEXT_DAY (SYSDATE," SATURDAY "), NEXT_DAY (SYSDATE," SUNDAY ") + (6 × 60 + 10)/(24 × 60 )"
Run once every 3 seconds	'Sysdate + 3/(24*60*60 )'
Run once every 2 minutes	'Sysdate + 2/(24*60 )'

1. Execution per minute

Name	Usage
Interval	TRUNC (sysdate, 'mi') + 1/(24*60)-executed per minute
Interval	'Sysdate + 1/(24*60) '-executed per minute
Interval	'Sysdate + 1'-daily
Interval	'Sysdate + 100'-hourly
Interval	'Sysdate + 2/24 * 60'-every 2 minutes
Interval	'Sysdate + 30/24*60 * 60'-every 30 seconds

2: daily scheduled execution

Name	Usage
Interval	TRUNC (sysdate + 1)-execute at every day
Interval	TRUNC (sysdate + 1) + 1/24-execute at every day
Interval	TRUNC (SYSDATE + 1) + (8*60 + 30)/(24*60)-executed at 08:30 every day

3: scheduled weekly execution

Name	Usage
Interval	TRUNC (next_day (sysdate, 'monday') + 1/24-every Monday
Interval	TRUNC (next_day (sysdate, 1) + 2/24-every Monday

4: scheduled monthly execution

Name	Usage
Interval	TTRUNC (LAST_DAY (SYSDATE) + 1)-execute at on the first day of each month
Interval	TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24-execute at on the first day of each month

5: Periodical execution on a quarterly basis

Name	Usage
Interval	TRUNC (ADD_MONTHS (SYSDATE, 3), 'q')-executed at on the first day of each quarter
Interval	TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24-am on the first day of each quarter
Interval	TRUNC (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-1/24-run at on the last day of each quarter

6: scheduled execution every six months

Name	Usage
Interval	ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24-each year on January 1, July 1 and January 1, January 1

7: scheduled execution every year

Name	Usage
Interval	ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24-execute at on January 1, January 1 every year