Detailed description of oracle job creation learning notes

I don't know much about oracle. Today, a friend said that oracle creates a job. I thought it was a job. As a result, oracle creates a job, let me introduce how to create a job instance in oracle.

I. Simple Test of the job creation process case:
1. Create a JOB_TEST table with the field in 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 inserts data into the pig table.

The Code is as follows:	Copy code
SQL> create or replace procedure JOB_PRO_TEST 2 begin 3 insert into JOB_TEST values (sysdate ); 4 end; 5/ Procedure created

3. Create a job named job2010 to execute the Stored Procedure JOB_PRO_TEST every minute (60 × 24 = 1440.

The Code is as follows:	Copy code
SQL> variable job2010 number; SQL> begin 2 dbms_job.submit (: job2010, 'job _ PRO_TEST; ', sysdate, 'sysdate + 100 ′); 3 end; 4/

Note: here the 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. query jobs-related views

The Code is as follows:	Copy code
Select job, last_date, last_sec, broken, failures, interval, what from dba_jobs

Several important fields in dba_jobs
Job: the ID of a job. For example, the above 41
Failures: number of failed job execution times. If the number of failed jobs exceeds 15, the broken column is marked as Y, and the job will not run in the future.
Broken: The default value is N. If it is Y, the job is not executed!
Interval: The interval between job execution.
What: the actual work of the job.
Ii. job knowledge:
1, DBA_JOBS
DBA_JOBS
========================================================== ===
Field (column) type description
Unique ID of a JOB NUMBER
LOG_USER VARCHAR2 (30) user who submits the task
PRIV_USER VARCHAR2 (30) user with task Permissions
SCHEMA_USER VARCHAR2 (30) User Mode for Syntactic Analysis of tasks
LAST_DATE DATE the time when the last task was successfully run
LAST_SEC VARCHAR2 (8) such as HH24: MM: hour, minute, and second of the SS format last_date
The start time of the task being run by THIS_DATE. If no task is running, the value is null.
THIS_SEC VARCHAR2 (8) such as HH24: MM: the hour, minute, and second of the this_date date in SS format
NEXT_DATE DATE the next scheduled task running time
NEXT_SEC VARCHAR2 (8) such as HH24: MM: the hour, minute, and second of the SS format next_date
TOTAL_TIME NUMBER the total time required to run the task, in seconds
The BROKEN VARCHAR2 (1) Flag parameter. Y indicates that the task is interrupted and will not run later.
INTERVAL VARCHAR2 (200) is the expression used to calculate the next line time.
Failures number the NUMBER of times the task failed to run continuously
WHAT VARCHAR2 (2000) PL/SQL block for task execution
CURRENT_SESSION_LABEL raw mlslabel trust Oracle session character of the task
CLEARANCE_HI raw mlslabel maximum gap between Oracle databases trusted by this task
CLEARANCE_LO raw mlslabel minimum Oracle gap trusted by this task
NLS session settings for running the NLS_ENV VARCHAR2 (2000) task
Other session parameters of the MISC_ENV RAW (32) task
--------------------------
2. Description of the INTERVAL parameter value
Midnight every day: 'trunc (SYSDATE + 1 )'
08:30 every morning: 'trunc (SYSDATE + 1) + (8*60 + 30)/(24*60 )'
Every TUESDAY: 'Next _ DAY (TRUNC (SYSDATE), "TUESDAY") + 123 ′
Midnight on the first day of each month: 'trunc (LAST_DAY (SYSDATE) + 1 )'
On the last day of each quarter: 'trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-100 ′
06:10 every SATURDAY and morning: 'trunc (LEAST (NEXT_DAY (SYSDATE, "SATURDAY"), NEXT_DAY (SYSDATE, "SUNDAY") + (6 × 60 + 10) /(24 × 60 )'
Run 'trunc (LAST_DAY (SYSDATE) + 25) 'at every month )'
--------------------------
1: executed per minute
Interval => TRUNC (sysdate, 'mi') + 1/(24*60)
Or
Interval = & gt; sysdate + 1/1440
2: daily scheduled execution
Example: Execute at every day
Interval => TRUNC (sysdate) + 1 + 1/(24)
3: scheduled weekly execution
Example: Execute at every Monday
Interval => TRUNC (next_day (sysdate, 'monday') + 1/24
4: scheduled monthly execution
Example: Execute at on the first day of every month
Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24
5: Periodical execution on a quarterly basis
For example, the statement is executed at on the first day of each quarter.
Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24
6: scheduled execution every six months
For example, at a.m. on January 1, July 1 and January 1, January 1
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24
7: scheduled execution every year
Example: Execute at on January 1, January 1 every year.
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24
3. View related job information
Related View
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running jobs.
Iii. Practical Application
Step 1: Create a job and execute the following SQL statement on the 25 th day of each month.
SQL> variable job2010 number;
SQL> begin
SQL> dbms_job.submit (: job2010, 'delete from i0216_inv_balance_curstock where trunc (sysdate-mtime)> 90 and ib_qty = 0; commit; ', sysdate, 'trunc (LAST_DAY (SYSDATE) + 25) '); <= execution at on the 25 th of every month
SQL> end;
SQL>/
Step 2: query the generated job id
SQL> select job, what from dba_jobs;
Job what
61 ..........
Step 3: run the created job
SQL> begin
SQL> dbms_job.run (61 );
SQL> end;
SQL>/
Step 4: query the next execution time of the job
SQL> select job, next_date, what from dba_jobs
SQL> where job = '61 ';
Step 5: Delete the job
SQL> begin
SQL> dbms_job.remove (61 );
SQL> end;
SQL>/