Why Does Oracle job not run? (

From: http://blog.csdn.net/compard/archive/2007/09/17/1788244.aspx

I. Job running frequency settings

1. Run at a fixed time every day, such as am: trunc (sysdate + 1) + (8*60 + 10)/24*60

2. Provided in Toad:

Daily: trunc (sysdate + 1)

Weekly: trunc (sysdate + 7)

Monthly: trunc (sysdate + 30)

Every Sunday: next_day (trunc (sysdate), 'sunday ')

Every day: trunc (sysdate + 1) + 6/24

30-hour: sysdate + 30/1440

3. 15th minutes of operation per hour, such as: 8: 15, 9: 15, 10: 15...: trunc (sysdate, 'hh') + 75/1440. Originally, I set trunc (sysdate, 'hh') + 15/1440, and found that it was not working.

II. Why not run a job?

1. first, let's take a look at the job parameter description: One of the job-related parameters is job_queue_processes, which is the number of processes used to run the job. Of course, after the job in the system is greater than this value, there will be waiting queues. The minimum value is 0, indicating that the job is not running and the maximum value is 36. snpn is used for the corresponding process on the OS, and cjqn is used for Job Management on the OS after 9i. 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.

2. Diagnosis: first determine whether the above two parameters are set correctly. In particular, if the first parameter is set to 0, all jobs will not run. After confirmation, we will continue down.

3. use the following SQL statement to check the broken, last_date, and next_date of the 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 at the previous time and whether the next 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. Then 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:

Declare

Begin

Dbms_job.broken (<job_id>, false );

End;

4. 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, job running locks related resources. You can check the view v $ access and V $ locked_object. If other processes lock the job-related objects, including PKG/function/procedure/table and other resources, you need to delete other processes. If necessary, delete the job process and run it again to check the results.

5. 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 -- close the job process and wait for 5-10 seconds.

Alter system set job_quene_processes = 5 -- restore the original value

6. Oracle bug

There is a bug in Oracle9i. When the counter reaches its maximum value in 497 days, the count will change to-1, and the count will change to 0, and the counter will no longer run. If this happens, you have to restart the database. If we have a production database version of 9205 on our side, this problem has occurred. It will be okay after you restart the database in an appointment. However, this problem was not found in other oracle7345 and Oracle8i databases.

7. Check the database. if the job is still running, check whether the job is running.ProgramFor example, if a large amount of data is processed or the network speed is slow, resulting in a long running time, you need to analyze the specific situation. We can use the following SQL statement to manually execute the job:

Declare

Begin

Dbms_job.run (<job> _ id)

End;

if you find that the job is not running properly, analyze it according to the specific program.