Oracle System Package--DBMS Job Usage (Oracle timed Task)

Source: Internet
Author: User

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 createtable t(  id   varchar2(30),  namevarchar2(30));

Second, create the stored procedure in Plsql:

123456 createor replace procedure proce_t isbegin   insert into t(id, name) values(‘1‘, to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss‘));   commit;endproce_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 completedjobno---------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 0n

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.