One: simple test job Create a process case:
1 , first create a job_test table, field is a Date Format Sql> CREATE TABLE Job_test (a date); Table created Sql> commit; Commit Complete 2 , create a stored procedure BB the effect is toward pig inserting data into the 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 , the name is job2010 The function is every minute ( x24=1440 ) Executes the stored procedure once job_pro_test . 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 Jobs The associated View 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. II: Job relevant knowledge of: 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 Values 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 3. View related job information Related views Dba_jobs All_jobs User_jobs Dba_jobs_running contains information about running job Three: Practical application Step One: Create a job , monthly Day Execute the following SQL . sql> variable job2010 number; Sql> begin Sql> Dbms_job.submit (: job2010, ' delete from I0216_inv_balance_curstock where trunc (sysdate-mtime) > and Ib_qty = 0;commit; ', Sysdate, ' TRUNC (Last_day (sysdate) + 25) '); <== 25th # 00:00 per month Sql> end; Sql>/ Step two: Query the generated job ID Sql> Select Job, what from Dba_jobs; Job what 61 ..... Step three: Run the job you just created Sql> begin Sql> Dbms_job.run (61); Sql> end; Sql>/ Step four: Query the job time of next execution Sql> Select Job,next_date,what from Dba_jobs sql> where job= ' 61 '; Step five: Delete the job Sql> begin Sql> Dbms_job.remove (61); Sql> end; Sql>/ |
