Detailed tutorial on creating a JOB in ORACLE: Create a job in oracle

Detailed tutorial on creating a JOB in ORACLE: Create a job in oracle

-- 1. learning job in plsql -- learning job -- creating table create table test_job (para_date date); commit; insert into test_job values (sysdate); commit; select * from test_job; -- create or replace procedure test_jobproce as begin insert into test_job values (sysdate); end test_jobproce; -- create a job -- declare test_job_really number executed by default after a job is created; begin dbms_job.submit (test_job_really, 'test _ jobproce; ', sysdate, 'sysdate + 100'); co Mmit; end; --- stop job 25 is the created job test_job_really begin dbms_job.broken (25, true); commit; end; -- start job begin dbms_job.run (25); commit; end; -- delete job begin dbms_job.remove (25); commit; end; -- View execution result select * from test_job order by test_job.para_date desc; -- View job select * from sys. user_jobs -- use the following SQL statement to query whether the JOB is still Running, provided that the job execution time cannot be too short select * from dba_jobs_running -- besides the submit parameter, the following parameters are available: -- run job dbms_jo B. run (v_job); -- stop a job. If the parameter is true or false, next_date (stop at a certain time) is also sysdate (stop immediately ). Dbms_job.broke (v_job, true, next_date); -- delete a job dbms_job.remove (v_job); dbms_job.what (v_job, 'SP _ fact_charge_code ;'); -- modify a job name and modify the next running time dbms_job.next_date (v_job, sysdate );

Oracle job Usage Details

In project development, we often have complicated business logic. Using the stored procedures of oracle can greatly reduce the compilation workload of java program code, and the stored procedures are executed on the database, so that the good performance of oracle can be used, greatly improve program execution efficiency and stability. Scheduled execution of stored procedures requires the use of jobs.

Common parameters are described as follows:

Field (column) type description job number the unique identifier of the JOB: LOG_USER VARCHAR2 (30) submitter user PRIV_USER VARCHAR2 (30) User SCHEMA_USER VARCHAR2 (30) user Mode for job syntax analysis LAST_DATE DATE the last time the job was successfully run LAST_SEC VARCHAR2 (8) for example, HH24: MM: SS format last_date hour, minute and second THIS_DATE start time of the task being run. If no task is run, the value is null THIS_SEC VARCHAR2 (8), for example, the hour of the this_date DATE in the format of HH24: MM: SS, minutes and seconds NEXT_DATE DATE the next scheduled run of the task NEXT_SEC VARCHAR2 (8) such as HH24: MM: SS format next_date hour, minute and second TOTAL_TIME NUMBER the total time required for the task to run. Unit: Second BROKEN VARCHAR2 (1) Flag parameter. Y indicates that the task is interrupted. INTERVAL VARCHAR2 (200) will not be run in the future) the expression used to calculate the next running time. failures number, the NUMBER of times the task failed to run continuously. WHAT VARCHAR2 (2000) PL/SQL block for executing the task CURRENT_SESSION_LABEL RAW MLSLABEL the trusted Oracle session character CLEARANCE_HI RAW MLSLABEL the maximum Oracle gap that the task can trust CLEARANCE_LO RAW MLSLABEL the minimum Oracle gap that the task can trust NLS_ENV VARCHAR2 (2000) set other session parameters for the MISC_ENV RAW (32) task.

Description INTERVAL parameter value 'trunc (SYSDATE + 1) '08:30 every day 'trunc (SYSDATE + 1) + (8*60 + 30)/(24*60) 'Next _ DAY (TRUNC (SYSDATE), ''tuesday'') + 123' TRUNC (LAST_DAY (SYSDATE) at midnight on the first DAY of each month) + 1) 'trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q') at on the last day of each quarter ') -1/24 'every SATURDAY and 06:10' TRUNC (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY "))) + (6 × 60 + 10)/(24 × 60 )'

1. Set the initialization parameter job_queue_processes.

　　sql> alter system set job_queue_processes=n;（n>0）

Job_queue_processes: the maximum value is 1000.
　　
View the job queue background process

　　sql>select name,description from v$bgprocess;

2. dbms_job package usage

It contains the following sub-processes:
　　

Broken () process.
Change () process.
Interval () process.
Isubmit () process.
Next_Date () process.
Remove () process.
Run () process.
Submit () process.
User_Export () process.
What () process.

1. The Broken () process updates the status of a submitted job. It is typically used to mark a Broken job as a non-Broken job.
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 the job is marked as broken -- TRUE indicates that the job is marked as broken, and FLASE indicates that the job is marked as not broken. BitsCN. Com
The next_date parameter indicates when the job will run again. The default value of this parameter is the current date and time.

If a job fails for some reason, oracle will retry 16 times and fail to execute successfully. It will be marked as a job in broken restart status, there are two methods;
A. Use dbms_job.run () to execute the job immediately.
　　

SQL> begin SQL> dbms_job.run (: jobno) This jobno is the job number SQL> end; SQL>/

B. Use dbms_job.broken () to mark the broken as false again.
　　

　 sql>begin　　　 sql>dbms_job.broken (:job,false,next_date)　　　 sql>end;　　　 sql>/

2. The Change () process is used to Change the settings of a specified job.
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)

　　
The job parameter is an integer that uniquely identifies the job.
The What parameter is a PL/SQL code block that runs from this job.
The next_date parameter indicates when the job will be executed.
The interval parameter indicates the frequency of re-execution. China Network Management Alliance
　　
3. The Interval () process is used to explicitly set the time Interval between re-execution of a job. 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 re-execution.
　　
4. The ISubmit () process is used to submit a job with a specific job 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 this job parameter is passed as an IN-type parameter and includes a work number provided by the developer. If the provided work number is used, 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)

A job identifies an existing job. The next_date parameter indicates the date and time when the job should be executed.
　　
6. Remove () to delete a scheduled job. This process receives 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 when Submit () is called for this job. A running job cannot be deleted by a called program.
　　
7. The Run () process is used to immediately execute a specified job. This process only receives one parameter:
　　

　　PROCEDURE Run(job IN binary_ineger) 

　　
The job that will be executed immediately.
　　
8. Use the Submit () process and the work is properly 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 () process. This value uniquely identifies a job.
What parameter is the PL/SQL code block to be executed.
The next_date parameter specifies when the job will be run.
The interval parameter indicates when the job will be re-executed.
The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- TRUE indicates that the PL/SQL code should perform syntax analysis during its first execution, FALSE indicates that the PL/SQL code should be analyzed immediately.
　　
9. A command is returned during the User_Export () process. This command is used to schedule an existing job so that the job can be submitted again.
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 a scheduled job. The my_call parameter contains the body required to resubmit the job in its current state.
　　
10. What () process promises to reset the running command during job execution. This process receives two parameters: job and what.
　　
PROCEDURE What (job IN binary_ineger,
What in out varchar2)
The job parameter identifies an existing job. What parameter indicates the new PL/SQL code to be executed.
　　
3. View related job information
1. Related views
Dba_jobs
All_jobs
User_jobs
Dba_jobs_running contains information about running jobs.

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 16 Y　　3 rows selected.

Information about running jobs

　　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　　----- ---------- ------------- --------- --------　　12 14144 HR 24-OCT-94 17:21:24　　25 8536 QS 24-OCT-94 16:45:12　　2 rows selected.

　　
Job queue lock Information
　　

　　SELECT SID, TYPE, ID1, ID2　　FROM V$LOCK　　WHERE TYPE = 'JQ';　　　　SID TY ID1 ID2　　--------- -- --------- ---------　　12 JQ 0 14144　　1 row selected.

Iv. Simple examples

A simple example:
　　
Create test table

　　SQL> create table TEST(a date);

The table has been created.
　　
Create a custom Process

　　SQL> create or replace procedure MYPROC as　　　2　begin　　　3　insert into TEST values(sysdate);　　　4　end;　　　5　/

The process has been created.
　　
Create a JOB

SQL> variable job1 number; SQL> begin 2 dbms_job.submit (: job1, 'myproc; ', sysdate, 'sysdate + 100'); -- 1/1440 minutes a day, that is, the test process is run once every minute. 3 end; 4/

The PL/SQL process is successfully completed.
　　
Run JOB

　　SQL> begin　　　2　dbms_job.run(:job1);　　　3　end;　　　4　/

The PL/SQL process is successfully completed.

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 a JOB

　　SQL> begin　　　2　dbms_job.remove(:job1);　　　3　end;　　　4　/

The PL/SQL process is successfully completed.