Oracle job timed execution of stored procedures

One: The simple test job creation process case:

1, first create a job_test table, the field is a date format

Sql> CREATE TABLE Job_test (a date);

Table created

Sql> commit;

Commit Complete

2, create a stored procedure BB action is to insert data into the Pig table

sql> Create or replace procedure job_pro_test as

2 begin

3 INSERT into job_test values (sysdate);

4 End;

5/

Procedure created



3, create a job with the name job2010; the function is to perform a stored procedure job_pro_test every Minute (60x24=1440).

sql> variable job2010 number;

Sql> begin

2 Dbms_job.submit (: job2010, ' job_pro_test; ', sysdate, ' sysdate+1/1440 ');

3 END;

4/

Note: Here the system automatically generates a job ID of 41

PL/SQL procedure successfully completed

job2010

---------

41

4, Run job2010

Sql> begin

2 Dbms_job.run (: job2010);

3 END;

4/

PL/SQL procedure successfully completed

job2010

---------

41

5, delete job2010

Sql> begin

2 Dbms_job.remove (: job2010);

3 END;

4/

6, query the related view of jobs

Select Job,last_date,last_sec,broken,failures,interval, what from Dba_jobs

A few more important fields in Dba_jobs

Job: Refers to the job ID number. Like the 41 above.

Failures:job the number of failures executed, if more than 15 times, then the broken column will be labeled Y, the job will not be run in the future

Broken: The default is N, and if y means that the job! is no longer executed

Interval: The interval between the time the job was executed.

What: The job's actual work.

6. After the creation is automatically running, we query the job table to see the job we created
SQL code
Select job,broken,what,interval,t.* from User_jobs t;

Select job,broken,what,interval,t.* from User_jobs t;
Job Broken what interval ...
Bayi N Job_proc; TRUNC (sysdate, ' mi ') + 1/(24*60) ...

II: Job Related knowledge:

1,dba_jobs

Dba_jobs

===========================================

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

Next_sec VARCHAR2 (8) hours, minutes and seconds of next_date date in HH24:MM:SS format

Total_time number The total time, in seconds, required for the task to run

Broken VARCHAR2 (1) flag parameter, y indicates the task is interrupted and will not run later

INTERVAL VARCHAR2 (200) expression for calculating the next run time

Failures number task runs with no successive successes

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

Current_session_label RAW MLSLABEL Trust Oracle Session break for this task

Clearance_hi RAW MLSLABEL Oracle maximum gap that the task can trust

Clearance_lo RAW MLSLABEL Oracle minimum gap that the task can trust

Nls_env VARCHAR2 (2000) NLS session settings for task runs

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

－－－－－－－－－－－－－－－－－－－－－－－－－－

2, description interval parameter value

12 O'Clock Midnight: ' TRUNC (sysdate + 1) '

Daily 8:30 A.M.: ' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '

Every Tuesday noon 12 o'clock: ' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '

Midnight of the first day of every month 12 o'clock: ' TRUNC (Last_day (sysdate) + 1) '

Last day of each quarter 11 o'clock: ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '

Every Saturday and Sunday 6:10 A.M.: ' TRUNC (LEAST (Next_day (sysdate, ' SATURDAY '), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '

25th # 00:00 per month executed: ' TRUNC (Last_day (sysdate) + 25) '

－－－－－－－－－－－－－－－－－－－－－－－－－－

1: Execute Every Minute

Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)

Or

Interval = sysdate+1/1440



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

Oracle job timed execution of stored procedures