Scheduled Oracle tasks

Today, Oracle's scheduled tasks are used for scheduled detection. So Goole made a hand and realized it after a moment of confusion. Therefore, we should keep a record as follows:

1. Make sure that the Oracle working mode allows start of the task queue manager
Background for Oracle to regularly execute "Job Queue"ProgramIt is a SNP process. To start the SNP process, first ensure that the whole system mode can start the SNP process. This requires the DBA to execute the following command:
Svrmgrl> alter system enable restricted session;
Or SQL> alter system disenable restricted session;
Use the above command to change the system session mode to disenable restricted, which creates conditions for the initiation of SNP.

(When I set this parameter, it seems that it cannot be successful, and the system prompts "invalid alter System Option", so I don't care)

2. make sure that the startup parameters of the task queue manager have been configured in Oracle.
the start parameters of the SNP are in the Oracle initialization file, which is placed in the $ ORACLE_HOME/DBS path, if the SID of Oracle is myora8, the initialization file is initmyora8.ora. The description of the SNP startup parameter in the file is as follows:
job_queue_process = n
job_queue_interval = n
the first line defines n as the number of started SNP processes. The default value is 0, and the normal definition range is 0 ~ 36. You can configure different values based on the number of tasks.
the second row defines that the system wakes up the process every n seconds. The default value is 60 seconds. The normal range is 1 ~ 3600 seconds. In fact, the process enters the sleep state after the current task is executed. After a period of sleep, the general control of the system will wake it up.
If the file does not contain the above two lines, add them according to the above configuration. After the configuration is complete, you need to restart the database to make it take effect. Note: If the task requires a short execution interval, the configuration of N should be smaller accordingly.
(the normal range here is 1 ~ 3600 seconds, I don't know if I set it to 1, that is, 24 hours)

3. Add the task to the task queue of the database.
Call the stored procedure in the dbms_job package of Oracle to add the task to the task queue:
Dbms_job.submit (job out binary_integer,
What in archar2,
Next_date in date,
Interval in varchar2,
No_parse in Boolean)
Where:
● Job: output variable, which is the number of the task in the task queue;
● What: the name of the task to be executed and its input parameters;
● Next_date: the time when the task is executed;
● Interval: The interval between tasks.
The interval parameter in dbms_job.submit is discussed in detail below. Strictly speaking, interval refers to the interval from the last execution to the next execution. When interval is set to null, the job is deleted from the queue after the execution is completed. If we need to periodically execute the job, we need to use 'sysdate + M. Here m is in the unit of (days), that is: 24 hours 1 time, M = 1;

Before adding a task to the task queue, determine the database user who executes the task. If the user is Scott, make sure that the user has the permission to run the dbms_job package. If not, grant permissions to Scott as a DBA:
Svrmgrl> grant execute on dbms_job to Scott;

For example:

SQL> Variable N number;
SQL> begin
2 dbms_job.submit (: N, 'statchangetable; ', sysdate,
3 'sysdate + 1/360 ');
4 commit;
5 end;
6/

After adding the task, you can view the task information:

SQL> select job, last_date, last_sec, next_date, next_sec, broken, failures from
2 dba_jobs;

Job last_date last_sec next_date next_sec B failures
-------------------------------------------------------------------------
1 22-8-06 16:26:57 22-8-06 16:30:57 n 13
2 2-8 months-06 16:27:17 22-8 months-06 16:31:17 N 5
3 22-8 months-06 16:27:02 22-8 months-06 16:31:02 N 0

After testing, my table change stored procedure is automatically working, and the table change is invisible...

However, when searching for information, it is worth record that there was a problem with the platform, that is, Oracle had a bug on the Solaris platform before version 9.2.0.6: A timer overflow occurs after 497 days, causing the task to stop.

 

Http://www.blogjava.net/hwpok/archive/2008/03/11/185377.html