Oracle database timer Job
Written as follows:
Declare
Jobno number
Begin
Dbms_job.submit (
Jobno
'nextMonthInventoryCARRYFORWARD (to_char (sysdate,' | |''| | 'yyyy-MM-dd' | |''| |'));',-- what
Sysdate,-- next_date, you can leave it unfilled.
'TRUNC (LAST_DAY (SYSDATE)) + 1+2/24'--'Interval time string'-interval, key setting
);
End
In Oracle database, we often use timer Job to make the database execute some scripts automatically, or do database backup, or do data extraction, or do database performance optimization, including rebuilding index and so on. However, the Oracle timer Job time processing, ever-changing, or relatively flexible. In this article, we summarized some ways to write the Oracle database timer Job in each time period, and then we began to introduce these.
The Job parameter is the binary_integer returned by the Submit () procedure.
The what parameter is the block of PL/SQL code that will be executed.
The next_date parameter indicates when the work will be run. You can write Job without specifying this value.
The interval parameter when the work will be reperformed.
The value of Interval is the key to determine when the Job is re-executed.
1. Execute every minute
Interval = > TRUNC (sysdate, 'mi') + 1 / (24060)
2. Execute regularly every day
For example: execution at 2: 00 a. M. every day
Interval = > TRUNC (sysdate) + 1x2 / (24)
3. Execute regularly every week.
For example: every Monday at 2: 00 a. M.
Interval = > TRUNC (next_day (sysdate, 2)) + 2max 24-Monday, the second day of the week
4. Execute regularly every month
For example: execution at 2: 00 a. M. on the 1st of every month
Interval = > TRUNC (LAST_DAY (SYSDATE)) + 1x 2max 24
5. regular execution on a quarterly basis
For example, it is executed at 2 am on the first day of every quarter.
Interval = > TRUNC (ADD_MONTH (SYSDATE), 3),'Q') + 2max 24
6. Regular execution every half a year
For example: 2: 00 a.m. on July 1 and January 1 every year
Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 6) + 2max 24
7. Regular execution every year
For example, it will be executed at 2: 00 a.m. on January 1 every year.
Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 12) + 2max 24
-query JOB-
Select job, what, next_date, next_sec, sysdate, failures, broken,interval
From user_jobs a
-modify JOB-
Begin
Dbms_job.change (62, 'MY_JOP;', sysdate,'sysdate+2/ (24060)')
Commit
End
Begin
Dbms_job.change (62, 'MY_JOP;', to_date (' 2011-08-01 22 yyyy-mm-dd hh34:mi:ss'), 'sysdate+1')
Commit
End
-Delete JOB-
Begin
Dbms_job.remove (41)
End
-run Job
Begin
Dbms_job.run (41)
End
Www.walekan.com/qc/singulato
Www.maogepingbeauty.com/
