Dbms_job the knowledge points involved

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

create or replace procedure proce_t isbegin   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 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

The

Indicates that 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, view 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 ----------------------------------- ------------------          26 2012/9/14 1 11:12:08                   0 N           25 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).

Dbms_job the knowledge points involved