Oracle database timing task DBMS_ The usage of job

Time:2020-9-25

1、 DBMS_ Knowledge points involved in job

1. Create a job:

variable jobno number;
dbms_ job.submit (: jobno, - job No 
 'your_ Procedure; ', - - the stored procedure to be executed,'; 'cannot be omitted 
 next_ Date, - next execution time 
 'interval' - the interval time, in days
);

– the system will automatically assign a task number jobno.
2. Delete job: dbms_job.remove(jobno);

3. Modify the action to perform:job:dbms_job.what(jobno, what); 

4. Modify next execution time:dbms_job.next_date(jobno, next_date);

5. Modification interval:dbms_job.interval(jobno, interval);

6. Start job:dbms_job.run(jobno);

7. Stop job:dbms.broken(jobno, broken, nextdate);– broken is a Boolean value

2、 Initializing related parameters job_ queue_ processes

1、job_queue_processIndicates the number of concurrent jobs that Oracle can perform whenjob_queue_processA value of 0 means that all Oracle jobs are stopped.

2. Viewjob_queue_processesparameter

Method 1


show parameter job_queue_process;

Method 2


select * from v$parameter where name='job_queue_processes';

3. Modificationjob_queue_processesparameter


alter system set job_queue_processes = 10;

3、 User_ Job table structure

Field (column) type description
Job number the unique identifier of the task
log_ User VARCHAR2 (30) the user who submitted the task
priv_ User VARCHAR2 (30) the user who has been given task permission
schema_ User VARCHAR2 (30) user mode for task parsing
last_ Date date the last time the task ran successfully
last_ SEC VARCHAR2 (8) as last in hh24: mm: SS format_ The hour, minute, and second of the date
this_ Date date the start time of the running task. Null if no task is running
this_ SEC VARCHAR2 (8) such as this in hh24: mm: SS format_ The hour, minute, and second of the date
next_ Date date the next scheduled time to run the task

The following uses a case study to demonstrate DBMS_ Job usage

1、 Create a table in PLSQL:


create table t(
 id varchar2(30),
 name varchar2(30)
);

2、 Create a stored procedure in PLSQL:


create or replace procedure proce_t is
begin
 insert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
 commit;
end proce_t;
/

3、 Create a job task (executed once a minute)

Execute after SQL >:


variable jobno number;
begin
 dbms_job.submit(:jobno,'proce_t;', sysdate, 'sysdate+1/24/60');
 commit;
end;
/

Prompt after submission:


pl/sql procedure successfully completed
jobno
---------
25

4、 To track the status of a task (view the task queue):


sql> select job, next_date, next_sec, failures, broken from user_jobs;
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 25 2012/9/14 1 10:59:46 0 n

Description task created successfully.

implementselect * from t;View the results of scheduled tasks. It can be seen that the timing task is executed normally.

5、 Stop scheduled tasks

1. View the job number of the scheduled task.


sql> select job, next_date, next_sec, failures, broken from user_jobs; 
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 25 2012/9/14 1 11:01:48 0 n

2. To stop a started scheduled task:


begin
 dbms_job.broken(25, true, sysdate);
 commit;
end;
/

Indicates that the task with job 25 is stopped.

After execution, it is shown as follows:


pl/sql procedure successfully completed

3. Check whether the scheduled task has stopped successfully


sql> select job, next_date, next_sec, failures, broken from user_jobs; 
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 25 4000/1/1 00:00:00 0 y

The broken value is y, indicating that the scheduled task has been stopped.

6、 Start scheduled task

1. View stop scheduled tasks


sql> select job, next_date, next_sec, failures, broken from user_jobs; 
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 25 4000/1/1 00:00:00 0 y

The broken value is y, indicating that the scheduled task has been stopped.

2. Start scheduled task


begin
 dbms_job.run(25);
 commit;
end;
/

3. Check whether the scheduled task is started


sql> select job, next_date, next_sec, failures, broken from user_jobs;
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 25 2012/9/14 1 11:06:17 0 n

The broken value is n, which indicates that the timing task is started successfully.

7、 View number of processes


show parameter job_queue_processes;

Must be greater than 0, otherwise execute the following command modification:


alter system set job_queue_processes=10;

8、 Create another task (every 5 minutes)::

variable jobno number;
begin
 dbms_ job.submit (:jobno, 'proce_ t. ', sysdate,' sysdate + 1 / 24 / 12 '); -- interval is in days
 commit;
end;
/

9、 Implementation


select job,next_date,next_sec,failures,broken from user_jobs;

result:


sql> select job,next_date,next_sec,failures,broken from user_jobs; 
 
 job next_date next_sec failures broken
---------- ----------- ---------------- ---------- ------
 26 2012/9/14 1 11:12:08 0 n
 25 2012/9/14 1 11:07:18 0 n

10、 Summary

About job runtime

1: Execution per minute


Interval => TRUNC(sysdate,'mi') + 1/(24*60)

2: Regular execution every day

For example: every day at 1:00 a.m


Interval => TRUNC(sysdate) + 1 +1/(24)

3: Regular weekly execution

For example: every Monday at 1:00 a.m

Interval => TRUNC(next_ Day (sysdate, 'Monday') + 1 / 24

4: Regular execution every month

For example: on the first day of every month at 1:00 a.m


Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

5: Regular execution every quarter

For example, it is implemented at 1:00 a.m. on the first day of each quarter


Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

6: Regular implementation every half a year

For example: 1:00 a.m. on July 1 and January 1 every year


Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

7: It is carried out regularly every year

For example, it is implemented at 1:00 a.m. on January 1 every year


Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'), 12)+1/24

Setting the running frequency of job

1. Run at a fixed time every day, such as 8:10 in the morning:Trunc(Sysdate+1) + (8*60+10)/24*60

2. Toad provides:

Every day:trunc(sysdate+1)

Weekly:trunc(sysdate+7)

Monthly:trunc(sysdate+30)

Every Sunday:next_ Day (TRUNC (sysdate), 'Sunday')

6:00 every day:trunc(sysdate+1)+6/24

Half an hour:sysdate+30/(24*60)

3. Run at the 15th minute of each hour, such as: 8:15, 9:15, 10:15 :trunc(sysdate,'hh')+(60+15)/(24*60)

summary

The above is the whole content of this article, I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message for exchange.