Used to schedule and manage job queues, which enable Oracle databases to perform specific tasks on a regular basis by using jobs.
First, the knowledge points involved in Dbms_job
1. Create Job:
Variable jobno number;
Dbms_job.submit (: Jobno,--job No.
' Your_procedure; ',—-execution of stored procedures, '; ' Cannot omit
next_date,--Next Execution time
' Interval '--each time interval, interval in days
);
– The system will automatically assign a task number jobno.
2, delete job:dbms_job.remove (jobno);
3. Modify the operation to be performed: Job:dbms_job.what (Jobno, what);
4, modify the next execution time: Dbms_job.next_date (Jobno, next_date);
5, Modified interval time: 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
Second, the initialization of the relevant parameters job_queue_processes
1. job_queue_process represents the number of jobs that Oracle can concurrently, and when the job_queue_process value is 0, it stops the Oracle job altogether.
2. View Job_queue_processes Parameters
Method One:
Show parameter job_queue_process;
Method Two:
SELECT * from V$parameter where name= ' job_queue_processes ';
3. Modify Job_queue_processes Parameters
alter system set job_queue_processes = 10;
Third, USER_JOBS table structure
field (column) type description
Unique identifier of the job number task
Log_user VARCHAR2 (30) the user who submitted the task
Priv_user VARCHAR2 (30) Users assigned to task permissions
Schema_user varchar2 (30) User mode for syntactic analysis of tasks
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) hours, minutes and seconds of last_date date in HH24:MM:SS format
This_date date the start time of the task is running, or null if no task is running
This_sec VARCHAR2 (8) hours, minutes and seconds of this_date date in HH24:MM:SS format
Next_date date Time of the next scheduled run of the task
The following uses a case to demonstrate the use of dbms_job
First, create a table in Plsql:
1234 |
create table t( id varchar2(30), name varchar2(30) ); |
Second, create the stored procedure in Plsql:
123456 |
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; / |
Iii. Create a job task (1-minute execution):
Execute after sql>:
123456 |
variable jobno number; begin dbms_job.submit(:jobno, ‘proce_t;‘ , sysdate, ‘sysdate+1/24/60‘ ); commit; end; / |
Prompt after submission:
1234 |
pl/sql procedure successfully completed jobno --------- 25 |
Iv. Tracking the situation of the task (View task Queue):
12345 |
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 The task was created successfully.
Execute select * from T; View the results of the scheduled task. You can see that the scheduled tasks are performed normally.
V. Stop Scheduled Tasks
1. Check the job number of the scheduled task.
12345 |
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. Stop a scheduled task that has been started:
12345 |
begin dbms_job.broken(25, true , sysdate); commit ; end ; / |
Represents a task that stops job 25.
After execution, the following appears:
PL/SQL procedure successfully completed
3. See if the scheduled task has stopped successfully
12345 |
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, which indicates that the timer task has stopped.
Six, start the scheduled task
1. View Stop Scheduled Tasks
12345 |
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, which indicates that the timer task has stopped.
2. Start a scheduled task
12345 |
begin dbms_job.run(25); commit ; end ; / |
3. See if the scheduled task is started
12345 |
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 |
A broken value of n indicates that the scheduled task started successfully.
Vii. viewing the number of processes
Show parameter job_queue_processes;
must be greater than 0, otherwise perform the following command modification:
alter system set JOB_QUEUE_PROCESSES=10;
VIII. Create a task again (executes every 5 minutes):
123456 |
variable jobno number; begin dbms_job.submit(:jobno, ‘proce_t;‘ , sysdate, ‘sysdate+1/24/12‘ ); --interval是以天为单位的 commit ; end ; / |
Nine , the implementation of select Job,next_date,next_sec,failures,broken from User_jobs;
Results:
123456 |
sql> Select Job,next _date,next_sec,failures,broken from User_jobs; Job next_date next_sec failures Broken --- -------------------------------------------------- 2012/9/14 1 11:12:08 0 N 2012/9/14 1 11:07:18 0 n |
X. SUMMARY
About Job run time
1: Execute Every Minute
Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)
2: Regular execution every day
Example: Daily 1 o'clock in the morning execution
Interval = TRUNC (sysdate) + 1 +1/(24)
3: Regular Weekly execution
For example: Every Monday 1 o'clock in the morning execution
Interval = TRUNC (Next_day (sysdate, ' Monday ')) +1/24
4: Regular Monthly execution
For example: 1st 1 o'clock in the morning every month to execute
Interval =>trunc (Last_day (sysdate)) +1+1/24
5: Quarterly Scheduled execution
For example, the first day of each quarter is 1 o'clock in the morning execution
Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 1/24
6: Every half-yearly scheduled execution
For example: Every July 1 and January 1 1 o'clock in the morning
Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +1/24
7: Scheduled execution every year
For example: January 1 1 o'clock in the morning every year to execute
Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24
Operating frequency settings for the job
1. Daily fixed time operation, such as morning 8:10 minutes: Trunc (sysdate+1) + (8*60+10)/24*60
Available in 2.Toad:
Daily: Trunc (sysdate+1)
Weekly: Trunc (sysdate+7)
Per month: trunc (sysdate+30)
Each Sunday: Next_day (trunc (sysdate), ' Sunday ')
6 points per day: trunc (sysdate+1) +6/24
Half Hour: sysdate+30/(24*60)
3. Run the 15th minute of every hour, for example: 8:15,9:15,10:15 ... : Trunc (sysdate, ' hh ') + (60+15)/(24*60).
Reprint to: http://blog.itpub.net/23055736/viewspace-1115938/
Oracle System Package--DBMS Job Usage (Oracle timed Task)