Oracle job procedure

Oracle jobs have the scheduled execution function. You can execute tasks at a specified time point or a certain time point every day.

 

1. query the view for jobs in the query system

        
         
          
           
            
             
              
               
                
                 
                  
                   
                    
                     
                      
                       
                        
                         
                          
                           
                            
                             
                              
                               
                                
                                 
                                  
                                   
                                    
                                 
  
The most important field is the job value, which is the ID number of the job we operate on. what is the name of the stored procedure, next_date execution time, and interval execution interval?

Ii. Execution interval operation frequency

Parameter Value of INTERVAL
TRUNC (SYSDATE + 1) at midnight every day)
TRUNC (SYSDATE + 1) + (8*60 + 30)/(24*60) at 08:30 every day)
NEXT_DAY (TRUNC (SYSDATE), ''tuesday'') + 12/24
TRUNC (LAST_DAY (SYSDATE) + 1) at midnight on the first day of each month)
TRUNC (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-1/24 at on the last day of each quarter
TRUNC (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY ") + (6 × 60 + 10) /(24 × 60)

Executed per second

Interval => sysdate + 1/(24*60*60)

If it is changed to sysdate + 10/(24*60*60), it will be executed in 10 seconds.

Executed per minute
Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

If it is changed to TRUNC (sysdate, 'mi') + 10/(24*60), it is executed every 10 minutes.

Daily scheduled execution
Example: Execute at every day
Interval => TRUNC (sysdate) + 1 + 1/(24)

Weekly scheduled execution
Example: Execute at every Monday
Interval => TRUNC (next_day (sysdate, 'monday') + 1/24

Scheduled monthly execution
Example: Execute at on the first day of every month
Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24

Quarterly scheduled execution
For example, the statement is executed at on the first day of each quarter.
Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24

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

Annual scheduled execution
Example: Execute at on January 1, January 1 every year.
Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24

3. Create a job

Create a job,
Basic Syntax:

        
         
          
           
            
             
              
               
            
  
Use the dbms_job.submit method. This process has five parameters: job, what, next_date, interval, and no_parse.
  
Dbms_job.submit (
Job OUT binary_ineger,
What IN varchar2,
Next_date IN date,
Interval IN varchar2,
No_parse IN booean: = FALSE) 
  
The job parameter is an output parameter returned by the submit () process, which uniquely identifies a job. Generally, a variable is defined and can be queried in the user_jobs view.
What parameter is the PL/SQL code block to be executed, the name of the stored procedure, and so on.
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 syntax analysis should be performed for this job at the time of submission or execution -- true; default value: false. Indicates that this PL/SQL code should perform syntax analysis when it is executed for the first time, and FALSE indicates that this PL/SQL code should perform syntax analysis immediately.

 

4. other job-related stored procedures

There are other processes in the package dbms_job: broken, change, interval, isubmit, next_date, remove, run, submit, user_export, And what;

The following describes these processes:

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 false indicates that the job is marked as not broken.
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 to be executed for some reason, oracle will retry 16 times before it is successfully executed. It will be marked as a broken and restarted as a broken job, there are two methods;
A. Use dbms_job.run () to execute the job immediately.
Begin
Dbms_job.run (: job) -- this job is the number of the job returned when the submit process is submitted or the number of the corresponding job is queried by dba_jobs.
End;

B. Use dbms_job.broken () to mark the broken as false again.
Begin
Dbms_job.broken (: job, false, next_date)
End;

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.
What parameter is a PL/SQL code block run by the job.
The next_date parameter indicates when the job will be executed.
The interval parameter indicates the frequency of re-execution of a job.

3. The interval () process is used to explicitly set the time interval between repeated jobs.
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 parameter and includes a job number provided by the developer. If the provided job 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.

7. run () 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. When the submit () process is used, the job is scheduled normally. As described above

9. A command is returned during the user_export () process. This command is used to arrange 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. Function: automatically inserts the current system time into the getSysDate table every minute.

 

V. Example

 

6. Set the number of job tasks and control concurrency

Initialize related parameters job_queue_processes
Alter system set job_queue_processes = 39 scope = spfile; // The maximum value cannot exceed 1000;
Job_queue_interval = 10; // The refresh frequency of the scheduling job is measured in seconds.

Job_queue_process indicates the number of jobs that can be concurrently run by oracle. sqlplus can use statements
Show parameter job_queue_process; to view the value of job_queue_process in oracle.

Select * from v $ parameter;

Select name, description from v $ bgprocess;

When job_queue_process is set to 0, all oracle jobs are stopped.
Alter system set job_queue_processes = 10;
To adjust the oracle startup job.

If the job_queue_processes value is set to 1, it is a serial operation, that is, a quick switch to execute a job.

 

VII. general reasons for job not running

(1) the job parameters are described as follows: job_queue_processes is a job-related parameter, which is the number of processes used to run the job. Of course, after the job value is greater than this value in the system, there will be waiting queues. The minimum value is 0, indicating that the job is not running. The maximum value is 1000. SNPn is used for the corresponding process on the OS. After 9i, the process managing the job on the OS is called CJQn. You can use the following SQL statement to determine that several SNP/CJQ instances are currently running.
Select * from v $ bgprocess. The snp/cjq process that paddr is not empty is currently idle, and some are working processes.
The other is job_queue_interval, which ranges from 1 to 36 00 and is measured in seconds. This is the process of the wake-up JOB, because every time the snp is run, he is rested and needs to wake up regularly, this value cannot be too small, which will affect the database performance.

First, check whether the preceding two parameters are correctly set. In particular, if the first parameter is set to 0, all jobs will not run automatically.

(2) Use the following SQL statement to view the broken, last_date, and next_date of a job. last_date indicates the end time of the last successful job, next_date is the next execution time calculated based on the set frequency. Based on this information, you can determine whether the job is normal last time and whether the next execution time is correct. The SQL statement is as follows:
Select * from dba_jobs;
Sometimes we find that his next_date is January 1, 4000, indicating that the job is running or the status is break (broken = Y). If we find that the job's broken value is Y, ask the user to check whether the job can be broken. If the broken is not available, change the broken value to N and use the preceding SQL statement to check whether the last_date has changed, job can run normally. The SQL statement for modifying the broken status is as follows:

Begin
DBMS_JOB.BROKEN (<JOB_ID>, FALSE );
End;

(3) Use the following SQL statement to check whether the job is still running
Select * from dba_jobs_running;
If you find that the job has been Run for a long time and has not ended, you need to check the cause. Generally, related resources are locked during job running. You can check the two views v $ access and v $ locked_object. If other processes lock job-related objects, including package, function, procedure, and table Resources, delete other processes. If necessary, delete the job process, run the command again to check the result.

(4) What should I do if the job is normal but not run? We should consider restarting the job once to prevent the job from running because the SNP process is dead. The command is as follows:
Alter system set job_queue_processes = 0; -- disable the job process and wait for 5-10 seconds.
Alter system set job_quene_processes = 5; -- restore the original value

(5) Oracle BUG: There is a BUG in Oracle9i. When the counter reaches the maximum value of 497 days, the count will become-1, when the count continues, it becomes 0, and the counter will no longer run. In this case, you have to restart the database, but other Oracle7345 and Oracle8i databases did not find this problem.

(6) database check is basically the case. If there is still a problem with job running, check whether it is a problem with the program itself. For example, a large amount of data is processed, or the network speed is slow, which causes the running time to be too long. You need to analyze the specific situation. We can use the following SQL statement to manually execute the job:

Begin
Dbms_job.run (<job> _ ID)
End;
If you find that the job is not running properly, you need to analyze it according to the specific program.