A detailed description of the job usage in Oracle

In project development, we often have some complex business logic. With Oracle's stored procedures, you can greatly reduce the amount of Java program code written, and the stored procedures are executed on the database, which can take advantage of Oracle's good performance support and greatly improve program execution efficiency and stability. The job is used when the stored procedure is executed regularly.

The following are the common parameter descriptions:

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

Describe interval parameter values
Every night 12 o'clock ' 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) '
The 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) '

first, set the initialization parameters job_queue_processes
Sql> alter system set job_queue_processes=n; (n>0)
Job_queue_processes Maximum value is 1000
　　
View the job queue background process
Sql>select name,description from V$bgprocess;
　　
　　 Two, Dbms_job package usage Introduction
Contains the following sub-procedures:
　　
Broken () process.
Change () procedure.
Interval () process.
Isubmit () process.
Next_date () process.
Remove () procedure.
Run () procedure.
Submit () procedure.
User_export () process.
what () process.
　　
1. The broken () process updates the status of a submitted work, typically used to mark a broken job as not broken.
This process has three parameters: job, broken, and next_date.
　　
PROCEDURE broken (Job in Binary_integer,
Broken in Boolean,
Next_date in Date: =sysdate)
　　
The job parameter is the work number, which uniquely identifies the work in the problem.
The broken parameter indicates whether this work will be marked as broken--true this work will be marked as broken, and Flase indicates that the work will be marked as not broken. Bitscn.com
The Next_date parameter indicates when this work will run again. The default value for this parameter is the current date and time.
If the job fails for any reason, Oracle will retry 16 times and fails to execute successfully and will be marked as broken restart job with broken status, as in the next two ways;
A. Use Dbms_job.run () to execute the job immediately
Sql>begin
Sql>dbms_job.run (: jobno) The job number returned when the Jobno is submitted for the submit process
sql>end;
sql>/
B. Use Dbms_job.broken () to re-mark broken as false
Sql>begin
Sql>dbms_job.broken (: job,false,next_date)
sql>end;
sql>/
2. The change () process is used to alter the settings of the specified work.
This process has four parameters: job, what, next_date, and interval.
　　
PROCEDURE change (Job in Binary_integer,
What in Varchar2,
Next_date in Date,
Interval in varchar2)
　　
This job parameter is an integer value that uniquely identifies the job.
What parameter is a PL/SQL code block that is run by this operation.
The Next_date parameter indicates when this work will be performed.
The interval parameter indicates the frequency of a work re-execution. China. Network Management Alliance
　　
3. The Interval () procedure is used to explicitly set the number of intervals between the re-execution of a work. This process has two parameters: Job and interval.
　　
PROCEDURE Interval (Job in Binary_integer,
Interval in VARCHAR2)
　　
The job parameter identifies a specific job. The interval parameter indicates the frequency of a work re-execution.
　　
4. The Isubmit () process is used to submit a job with a specific work number. This process has five parameters: job, what, next_date, interval, and No_parse.
　　
PROCEDURE isubmit (Job in Binary_ineger,
What in Varchar2,
Next_date in Date,
Interval in varchar2,
No_parse in Booean:=false)
　　
The only difference between this process and the submit () process is that the job parameter is passed as an in parameter and includes a work number provided by the developer. If the supplied work number is already in use, an error is generated.
　　
5. The next_date () process is used to explicitly set the execution time of a job. This process receives two parameters: Job and Next_date.
　　
PROCEDURE next_date (Job in Binary_ineger,
Next_date in date) bitscn.com
The job identifies a work that already exists. The Next_date parameter indicates the date and time that this work should be performed.
　　
6. Remove () procedure to delete a work that has been scheduled to run. This process takes a parameter:
　　
PROCEDURE Remove (Job in Binary_ineger);
　　
The job parameter uniquely identifies a job. The value of this parameter is the value of the job parameter returned by the submit () procedure called for this work. Work that is already running cannot be deleted by the calling program.
　　
7. The Run () procedure is used to perform a specified job immediately. This procedure receives only one parameter:
　　
PROCEDURE Run (Job in Binary_ineger)
　　
The job parameter identifies the work that will be performed immediately.
　　
8, the use of the submit () process, the work is normally planned.
This process has five parameters: job, what, next_date, interval, and No_parse.
　　
PROCEDURE Submit (Job out Binary_ineger,
What in Varchar2,
Next_date in Date,
Interval in varchar2,
No_parse in Booean:=false)
　　
The job parameter is the Binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job.
What parameter is the PL/SQL code block that will be executed.

China. Network Management Alliance

The Next_date parameter indicates when the job will run.
Interval parameter when this work will be re-executed.
The No_parse parameter indicates whether this work should be parsed at the time of submission or execution--true indicates that this PL/SQL code should be parsed when it first executes, and false indicates that this PL/SQL code should parse immediately.
　　
9. The User_export () procedure returns a command that is used to schedule an existing work so that the work can be resubmitted.
This program has two parameters: Job and My_call.
　　
PROCEDURE user_export (Job in Binary_ineger,
My_call in Out VARCHAR2)
　　
The job parameter identifies an assigned job. The My_call parameter contains the body that is required to resubmit this work in its current state.
　　
10. What () process promises to reset this running command at work execution time. This process receives two parameters: job and what.
　　
PROCEDURE what (Job in Binary_ineger,
What in Out varchar2)
The job parameter identifies a work that exists. What parameter indicates the new PL/SQL code that will be executed.
　　
　　Third, check the relevant job information
1. Related views
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running job

Www_bitscn_com

　　
2. View related information
　　
Sql>select JOB, Next_date, next_sec, failures, broken
Sql>from Dba_jobs;
　　
JOB next_date next_sec Failures B
------- --------- -------- -------- -
9125 01-jun-01 00:00:00 4 N
14144 24-oct-01 16:35:35 0 N
9127 01-jun-01 00:00:00 Y
3 Rows selected.
　　
Job-related information that is running
　　
SELECT SID, R.job, Log_user, R.this_date, r.this_sec
From Dba_jobs_running R, Dba_jobs J
WHERE r.job = j.job;
　　
SID JOB log_user this_date this_sec
----- ---------- ------------- --------- --------
14144 HR 24-oct-94 17:21:24
8536 QS 24-oct-94 16:45:12
2 rows selected.
　　　
JOB QUEUE Lock Related information
　　
SELECT SID, TYPE, ID1, ID2
From V$lock
WHERE TYPE = ' JQ ';
　　
SID TY ID1 ID2
--------- -- --------- ---------
JQ 0 14144
1 row selected.
　　
　　 Iv. Simple Examples
A simple example: China Network Management Alliance
　　
Create a test table
Sql> CREATE TABLE TEST (a date);
　　
The table is created.
　　
Create a custom procedure
sql> Create or replace procedure MYPROC as
2 begin
3 INSERT into TEST values (sysdate);
4 End;
5/
　　
The process has been created.
　　
Create Job
sql> variable JOB1 number;
Sql>
Sql> begin
2 Dbms_job.submit (: Job1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes per day, or one minute to run the test procedure once
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Run Job
Sql> begin
2 Dbms_job.run (: JOB1);
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Sql> Select To_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;
　　
Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
　　
Delete Job
Sql> begin
2 Dbms_job.remove (: JOB1);
3 END;
4/bitscn_com
　　
The PL/SQL process has completed successfully.

A detailed description of the job usage in Oracle