Oracle Create job Learn notes detailed

One: The simple test job creation process case:
1, first create a job_test table, the field is a date format

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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 named job2010, which performs a stored procedure job_pro_test per minute (60x24=1440).

The code is as follows	Copy Code
sql> variable job2010 number; Sql> begin 2 Dbms_job.submit (: job2010, ' job_pro_test; ', sysdate, ' sysdate+1/1440′ '); 3 END; 4/

Note: This system automatically generates a job ID of 41

The code is as follows	Copy Code
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

The code is as follows	Copy Code
Sql> begin 2 Dbms_job.remove (: job2010); 3 END; 4/

6, check the related view of jobs

The code is as follows	Copy Code
Select Job,last_date,last_sec,broken,failures,interval, what from Dba_jobs

Detailed dba_jobs several more important fields
Job: Refers to the job's ID number. Like the top 41.
Failures:job execution times, if more than 15 times, then the broken column will be labeled Y, will not run the job in the future
Broken: The default is n, if y, which means that the job! is no longer executed
Interval: The interval between the execution of the job.
What: The actual work of the job.
second: 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 permissions to the task
Schema_user VARCHAR2 (30) User mode for parsing a task
Last_date date the last time the task was successfully run
Last_sec VARCHAR2 (8) such as HH24:MM:SS format last_date date hours, minutes, and seconds
This_date date is running the start time of the task, or null if the task is not running
This_sec VARCHAR2 (8) such as HH24:MM:SS format this_date date hours, minutes, and seconds
Next_date date the next time the task is scheduled to run
Next_sec VARCHAR2 (8) such as HH24:MM:SS format next_date date hours, minutes, and seconds
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 run later
INTERVAL VARCHAR2 (200) An expression used to calculate the next run time
Failures number task runs continuously without success
WHAT VARCHAR2 (2000) Pl/sql block to perform a task
Current_session_label RAW MLSLABEL Trusted Oracle Session character 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) The NLS session settings that the task runs
Some other session parameters that the Misc_env RAW (32) task runs
－－－－－－－－－－－－－－－－－－－－－－－－－－
2, describe interval parameter value
12 O'Clock Midnight every day: ' 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 the Month 12 O'Clock: ' TRUNC (Last_day (sysdate) + 1) '
On the evening of the last day of the Quarter 11 o'clock: ' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24′
Every Saturday and 6:10 A.M.: ' TRUNC (Least (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY"))) + (6X60+10)/(24x60) '
25th # 00:00 per month: ' TRUNC (Last_day (sysdate) + 25) '
－－－－－－－－－－－－－－－－－－－－－－－－－－
1: Per minute execution
Interval => TRUNC (sysdate, ' mi ') + 1/(24*60)
Or
Interval => sysdate+1/1440
2: Regular daily execution
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
Example: Monthly 1st 1 o'clock in the morning
Interval =>trunc (Last_day (sysdate)) +1+1/24
5: Quarterly Regular execution
For example, the first day of every quarter, 1 o'clock in the morning execution
Interval => TRUNC (add_months (sysdate,3), ' Q ') + 1/24
6: Every six months regular execution
For example: July 1 and January 1 every year 1 o'clock in the morning
Interval => add_months (trunc (sysdate, ' yyyy '), 6) +1/24
7: Regular Annual execution
For example: January 1 every year 1 o'clock in the morning execution
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 that executes the following SQL 25th every month.
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 for execution
Sql> end;
Sql>/
Step two: Query-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 next execution time for the job
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>/