How to schedule jobs in Oracle

How to schedule a job in Oracle --

 

... Currently, popular mainstream databases all have this function, most representative of which are Microsoft SQL Server 7.0, Oracle8i/9i, etc. However, to make the job work, we still need to configure it. All these configurations have GUI operations. This article introduces how to configure a job through the command line in Oracle9i ......

As we all know, generally, the operating system provides a method to regularly execute tasks. For example, the UNIX platform provides the crontab command for the system to periodically execute tasks. However, for some requirements, such as database table operations, the most typical is the settlement after the stock exchange closes every day. It involves a large number of database table operations, if you still use the operating system for scheduled execution, not only requires a lot of programming work, but also causes user inconsistency and other running errors, and even causes program execution to fail.
In fact, we can use the Job Queue (task queue manager) function of the database to implement the above requirements. A job allows you to schedule and schedule a task in advance so that it can be automatically executed once or multiple times at a specified time point or period. because the task is executed in the database, therefore, the execution efficiency is very high.
Job allows us to customize the task execution time and provide a flexible processing method. You can also configure and schedule the task to be executed within a period of time when the number of system users is small, this greatly improves work efficiency. For example, for routine backup, update, deletion, and replication of databases, time-consuming and repetitive work, and timed push in value-added text message services of China Telecom, we can use jobs for automatic execution to reduce the workload.
Currently, popular mainstream databases all have this function, most representative of which are Microsoft SQL Server 7.0 and Oracle8i/9i. However, to make the job work, we still need to configure it. All these configurations have GUI operations. This article describes how to configure a job in Oracle9i through a command line.
Prerequisite: the stored procedure to be periodically executed [parameters are not allowed].
When defining a job, execution interval is an important task. Sysdate + 1/24 is the interval stored in the dba_jobs view. It can generate snapshots once every hour. This data can be changed to different sampling times, with 24*60 = 1440 minutes in a day. You can use this number to adjust the number of executions. For example, to obtain a snapshot every 10 minutes, run the following command:
Execute dbms_job.submit (
: Jobno, -- job ID
'SP; ', -- Execution Process
Trunc (sysdate + 10/1440, 'mi'), -- next execution time
'Trunc (sysdate + 10/1440, ''mi'') ', -- interval
True, -- no_parse
: Instno );
1. Create a job
Create a task every five minutes.
Variable v_sn number;
Begin
Dbms_job.submit (: v_sn,
'P _ push_send ;',
Trunc (sysdate + 5/1440, 'mi '),
'Trunc (sysdate + 5/1440, ''mi '')');
Commit;
End;
/
Delete a task:
Execute dbms_job.remove (jobno );
2. Query Task statements
Two tables are involved: user_jobs and dba_jobs_running [view dba_jobs]
Select * From user_jobs;
Select * From dba_jobs_running;
Select * From dba_jobs;
3. necessary parameters [modify the initsid. ora parameter]
Job_queue_processes = 4 -- number of executable jobs
Job_queue_interval = 10 -- default interval delay of 10 s
Job_queue_keep_connections = true -- the job maintains a normal connection.
Modify the number of executable jobs to 20:
Alter system set job_queue_processes = 2; 0
Modify the unlimited' mode:
Alter system disable restricted session;
4. Related job operations
Delete job: dbms_job.remove (jobno );
Modify the operation to be executed: dbms_job.what (jobno, what );
Modify the next execution time: dbms_job.next_date (job, next_date );
Interval of modification: dbms_job.interval (job, interval );
Stop job: DBMS. Broken (job, broken, nextdate );
Start Job: dbms_job.run (jobno );
5. Job latency
Jobs have different degrees of latency. to completely eliminate this error, you 'd better use the crontab or at timer of the operating system. Haha, just kidding.
A. overlapping time [comments]:
For example, if I have two jobs that run at a.m., if one of them runs at a.m., the other must wait until the first job is completed before execution. Especially for jobs like sysdate + 1/24, so that jobs run in one hour per grid are more likely to conflict. Even if there is no time overlap, Oracle also presses job_queue_internal (usually 1 minute) check the job queue at the interval, so that the job at one o'clock will only be checked at 3:00:45, then the job will be executed at 3:00:45. we know that if an oracle job fails to be executed for the first time, it will start the job again at a certain interval until it is successful. If the job still fails until 16 times, the job will be interrupted, therefore, the actual running time will be postponed.
B uses the "precise timing" function (as we can see earlier, it is difficult to implement precise and scheduled job execution)
We can use trunc (sysdate) + (1 + 24)/25 or trunc (sysdate) + 25/24. Indicates that the job is executed at every day, so that it will not be affected by the delay of the previous job. (Not tested ).
C. The five-minute job I run on my own does not seem to have any latency. It may be that the execution time is short and the interval is small, so I am not aware of it.