Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.