Shulou(Shulou.com)06/01 Report--

Sample oracle JOB executed at 1 o'clock every day

DECLARE

X NUMBER

BEGIN

SYS.DBMS_JOB.SUBMIT

(job = > X

What = > 'ETL_RUN_D_Date;'

Next_date = > to_date ('2009-08-26 01PUR 001MMMAUDD hh34:mi:ss')

Interval = > 'trunc (sysdate) + 1 inch 1max 24'

No_parse = > FALSE

);

SYS.DBMS_OUTPUT.PUT_LINE ('Job Number is:' | | to_char (x))

COMMIT

END

/

The above is clearly specified at 1 o'clock every day to execute this job, if you specify that every day at 12:00 to execute interval needs to be specified as' trunc (sysdate) + 1: 00 12 interval 24', if only specify interval as a day, so that when you manually use dbms_job.run (job) to run once, the daily execution time of job will change, if you want job to execute at a fixed time every day, you can refer to the above example.

Initialize the related parameter job_queue_processes

The maximum value of alter system set job_queue_processes=39 scope=spfile;// cannot exceed 1000; job_queue_interval = 10 / / scheduling job refresh rate is in seconds

Job_queue_process represents the number of job that oracle can concurrently, which can be achieved by statement

Show parameter job_queue_process

To see the value of job_queue_process in oracle. When the job_queue_ process value is 0, it means to stop all the job of oracle. You can use the statement

ALTER SYSTEM SET job_queue_processes = 10

To adjust the job that starts oracle.

Related views:

Dba_jobs

All_jobs

User_jobs

Dba_jobs_running contains information about running job

-

Submit job syntax:

Begin

Sys.dbms_job.submit (job = >: job

What = > 'playing Cleary PACKBALT'

Next_date = > to_date ('04-08-2008 05 dd-mm-yyyy hh34:mi:ss')

Interval = > 'sysdate+ 1gamma 360')

Commit

End

/

-

Create JOB

Variable jobno number

Begin

Dbms_job.submit (: jobno, 'paired CRENTPLANETS)

Commit

Run JOB

SQL > begin

Dbms_job.run (: job1)

End

/

Delete JOB

SQL > begin

Dbms_job.remove (: job1)

End

/

DBA_JOBS

=

Field (column) type description

The unique identification number of the JOB NUMBER task

LOG_USER VARCHAR2 (30) users who submit tasks

PRIV_USER VARCHAR2 (30) users who grant permissions to the task

SCHEMA_USER VARCHAR2 (30) user mode for parsing tasks

The time when LAST_DATE DATE last ran the task successfully

LAST_SEC VARCHAR2 (8) hours, minutes and seconds of last_date dates such as HH24:MM:SS format

The start time of the THIS_DATE DATE running task, or null if no task is running

THIS_SEC VARCHAR2 (8) hours, minutes and seconds of this_date dates such as HH24:MM:SS format

The time of the next scheduled task run by NEXT_DATE DATE

NEXT_SEC VARCHAR2 (8) hours, minutes and seconds of next_date dates such as HH24:MM:SS format

TOTAL_TIME NUMBER the total time required for the task to run (in seconds)

BROKEN VARCHAR2 (1) flag parameter, Y indicates that the task is interrupted and will not be run in the future

The expression used by INTERVAL VARCHAR2 to calculate the next run time

The number of consecutive unsuccessful runs of the FAILURES NUMBER task

WHAT VARCHAR2 (2000) PL/SQL block for performing tasks

CURRENT_SESSION_LABEL RAW MLSLABEL the trust Oracle session character for this task

CLEARANCE_HI RAW MLSLABEL the maximum Oracle gap that can be trusted for this task

CLEARANCE_LO RAW MLSLABEL the minimum Oracle gap that can be trusted for this task

NLS session settings for NLS_ENV VARCHAR2 (2000) task run

Some other session parameters that the MISC_ENV RAW (32) task runs

--

Describe the INTERVAL parameter valu

12:00 midnight every day at 'TRUNC (SYSDATE + 1)'

At 08:30 every morning, 'TRUNC (SYSDATE + 1) + (8' 60'/ (24'60)'

Every Tuesday at 12:00 'NEXT_DAY (TRUNC (SYSDATE),' 'TUESDAY'') + 12 Compact 24'

12:00 at midnight on the first day of every month. 'TRUNC (LAST_DAY (SYSDATE) + 1)'

11:00 on the evening of the last day of each quarter 'TRUNC (ADD_MONTHS (SYSDATE + 2Acer 24,3),' Q')-1Accord 24'

Every Saturday and Sunday 06:10 'TRUNC (LEAST (NEXT_DAY (SYSDATE,' 'SATURDAY "), NEXT_DAY (SYSDATE," SUNDAY ")) + (6 × 60 × 10) / (24 × 60)'

--

1: execute every minute

Interval = > TRUNC (sysdate,'mi') + 1 / (24060)

Or

Interval = > sysdate+1/1440

2: regular execution every day

For example: execution at 1: 00 a. M. every day

Interval = > TRUNC (sysdate) + 1 + 1 / (24)

3: regular execution every week

For example, it is executed at 1: 00 a.m. every Monday.

Interval = > TRUNC (next_day (sysdate,' Monday')) + 1 Unix 24

4: regular execution every month

For example: execution at 1: 00 a. M. on the 1st of every month

Interval = > TRUNC (LAST_DAY (SYSDATE)) + 1x 1max 24

5: regular execution every quarter

For example, it is executed at 1 am on the first day of every quarter.

Interval = > TRUNC (ADD_MONTHS (SYSDATE,3),'Q') + 1max 24

6: regular execution every half a year

For example: 1: 00 a.m. on July 1 and January 1 every year

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 6) + 1 Universe 24

7: regular execution every year

For example, it will be executed at 1: 00 a.m. on January 1 every year.

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 12) + 1 Universe 24

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.