Oracle develops timed tasks

This section summarizes: This section describes the use of Oracle's own jobs to enable Oracle custom timed execution tasks.

 1. Introduction Custom timed tasks have two forms, system level and database level, from the operating system level, the Windows system can be implemented using a task plan, for the WinXP system, set the steps below, start---setup---Control Panel---task plan, Then add the task schedule, follow the wizard to complete the configuration;   for Win7 system, set the steps below, click Start, then enter [Task Scheduler] in the Search Programs and files box, then click Out the Task Scheduler, create a basic task, Linux system we can use the crontab command to be implemented, About the use of the crontab command can be seen in the previous blog (http://www.2cto.com/os/201209/155632.html);  from the database level, we can use the database job to implement This section focuses on customizing a simple scheduled execution task with the Oracle database job. This section uses Oracle to customize a timer to insert statements into a table.   Here to talk about the reason why I do this Oracle Timing task:  project to synchronize the data of some tables in a database to another database at a fixed time, obviously I can't do it myself every time, so how do I do it? At this point, Oracle's scheduled job assignment is definitely my best choice. I put the synchronized script in a stored procedure, and then execute the stored procedure at a fixed time to be OK.   Note: All of the following actions are performed under the sytem user. Client login with PL/SQL.  2. Project environment install ORACLE10G, and then install PL/SQL, and then log in with the Administrator user system, open the SQL statement in the Windows input graph, the operating environment details as follows:  3. Environment Preparation (1) Create a new table, The command is test and the table statement is as follows: test09161 CREATE TABLE system.test0916 2 (3   seq number (2) PRIMARY KEY, 4   seqtime&n Bsp DATE 5) Executes the above statement in the SQL window, and (2) creates a new stored procedure named Test_proc with the following statement: test_proc 1 create OR REPLACE PROCEDURE test_proc  is 2 BEGIN 3   INSERT into System.test0916 (seq, seqtime)   VALUES (NVL ((SELECT MAX (seq) from SYSTEM. TEST0916) + 1, 0), sysdate); 4   COMMIT; 5 EXCEPTION 6   When OTHERS then 7     dbms_output. Put_Line (' Exception happened,data was rollback '); 8     ROLLBACK; 9 end;  Execute the above stored procedure in the SQL Windows window, after execution, look at the procedures menu under Objects to see the new stored procedure test_proc the previous icon has no red flag indicating that the stored procedure is normal ; (3) Check Oracle's job run environment all I have to do is check the number of Oracle's job queue processes, and if 0 means not running the job, though the web says it's generally default to 10, I don't know why 0 is the case when I install Oracle. To view the number of processes: Modify the number of processes (if you are 0, you need to modify the following statement): I have changed to 10 here, if I want to change 11来 to simulate the following operation; in the PL/SQL command window, enter the following statement: Modify the number of processes alter system set JOB_ Queue_processes=10 Scope=both; Run as follows:   After the revision we look at this: see, I this is the change to 11. Add: Some of the online said to modify the number of processes can be modified Oracle initial file, the first I was to find the location of the initial file, and then open with notepad++ to modify, save I restart the Oracle service and computer, but found that this time with pl/ When SQL landed on Oracle, the specified string was not recognized, and it was not possible to do so. Here's a question, how do we find the location of the Oracle initial file? The following statements can be executed in the Command Window window of PL/SQL to see: I was using notepad++ to modify the contents of the shaded part: *.job_queue_processes=11 4. Customizing scheduled execution Tasks Suppose we want to insert a record every two minutes into the table test0916, where we only need to execute the stored procedure every 2 minutes Test_proC can. In PL/SQL, Command window, enter the following statement: JOB1 var job_num number; 2 Begin 3    Dbms_job.submit (: Job_num, ' Test_proc; ', sysdate, ' sysdate+2/24/60 '); 4 end;  The execution process is as follows: Remember to click the submit button of PL/SQL when the execution is complete  5. View execution Results (1) See if our new job is successful by viewing the Dba_ The What field in the jobs table can see the last record even though we just newly built the scheduled task; (2) See if the test0916 table has data  6 inserted. Other related instructions (1) How do I delete a job? Execute the following statement in the PL/SQL command window: exec dbms_job.remove (81); Description: The number 81 here is the value of the job field corresponding to the row of the job record currently being deleted in the Dba_jobs table;  (2) What do the various parameters of the submit mean?   using the Submit () process, the work is properly planned.   This process has five parameters: job, what, next_date, interval, and No_parse.   PROCEDURE Submit (Job out binary_ineger, , varchar2,  next_date in date,  interval in varcha r2,  No_parse in Booean:=false)  
The job parameter is the Binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job. There is a double quotation mark in front, which is not shown in SQL Windwo, and the Copy to command window can be displayed; What parameter is the PL/SQL code block that will be executed. Here is the name of the stored procedure, followed by a semicolon in the English state can not be dropped; The Next_date parameter indicates when the job will run.
Interval parameter when this work will be re-executed. Here the plus and the parentheses must be entered in the English state; The No_parse parameter indicates whether this work should be parsed at the time of submission or execution--true indicate that this PL/SQL code should be parsed when it first executes, and false to indicate that this PL/SQL code should be parsed immediately. (3) How is the time interval of timed execution set? Here everyone can go to Baidu, online search generally can find to meet your needs, mainly results trunc function to achieve.

Oracle develops timed tasks