How to create JOB in oracle
Today, I will talk to you about how to create JOB in oracle. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.
1. To create a JOB, you can specify a fixed time (the management of running time is more detailed).
Select job_name, state from user_scheduler_jobs
Grant create job to btupayprod
Begin
Dbms_scheduler.create_job (job_name = > 'JOB'
Job_type = > 'STORED_PROCEDURE'
Job_action = > 'proc_balance_fix_mon',-- stored procedure name
Start_date = > sysdate
Repeat_interval = > 'FREQ=minutely; INTERVAL=45; BYHOUR=15,16,17'
Comments = > 'execute balance check complement program')
End
2. The required permissions are relatively small. Generally, you do not need to grant other permissions, but you can only specify a fixed time interval.
Declare
Job_id pls_integer
Begin
Sys.dbms_job.submit (job = > job_id)
What = > 'proc_balance_fix_mon;'
Next_date = > to_date ('07-08-2015 20 dd-mm-yyyy hh34:mi:ss')
Interval = > 'TRUNC (sysdate,''mi'') + 40 / (24 * 60)')
-- commit
End
Common execution time of oracle JOB
TRUNC (SYSDATE + 1) + (3,60,10) / (24,60)
1. Execute every minute
TRUNC (sysdate,'mi') + 1 / (24060)
Www.2cto.com
2. Execute regularly every day
For example:
Executed at 0: 00 a. M. every day
TRUNC (sysdate+1)
Executed at 1: 00 a. M. every day
TRUNC (sysdate+1) + 1 Compact 24
Executed at 08:30 every morning
TRUNC (SYSDATE+1) + (8: 60: 30) / (24: 60)
3. Execute regularly every week.
For example:
Every Monday at 2: 00 a. M.
TRUNC (next_day (sysdate,1)) + 2Acer 24
TRUNC (next_day (sysdate,' Monday')) + 2 Unix 24
Every Tuesday at 12:00
TRUNC (next_day (sysdate,2)) + 12Accord 24
TRUNC (next_day (sysdate,' Tuesday')) + 12 Unix 24
4. Execute regularly every month
For example:
Implemented at 0: 00 a. M. on the 1st of every month
TRUNC (LAST_DAY (SYSDATE) + 1)
Implemented at 1: 00 a. M. on the 1st of every month
TRUNC (LAST_DAY (SYSDATE) + 1) + 1 Universe 24
5. regular execution on a quarterly basis
Executed at 0: 00 a.m. on the first day of every quarter.
TRUNC (ADD_MONTHS (SYSDATE,3),'q')
Executed at 2: 00 a.m. on the first day of every quarter.
TRUNC (ADD_MONTHS (SYSDATE,3),'q') + 2 Unix 24
Executed at 11:00 in the evening on the last day of each quarter
TRUNC (ADD_MONTHS (SYSDATE+ 2ple 24jue 3),'q')-1 Universe 24
6. Regular execution every half a year
For example:
Implemented at 1: 00 a.m. on July 1 and January 1 every year
ADD_MONTHS (TRUNC (sysdate,'yyyy'), 6) + 1 Unix 24
7. Regular execution every year
For example:
Implemented at 2: 00 a.m. on January 1 every year.
ADD_MONTHS (TRUNC (sysdate,'yyyy'), 12) + 2 Unix 24
After reading the above, do you have any further understanding of how to create JOB in oracle? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.
