Simple oracle timer operation

Oracle timer simple operation first create a test table: SQL code create table JOBTEST (ID VARCHAR2 (50) primary key, NAME VARCHAR2 (20), AGE NUMBER (3 )) then write the stored procedure to be called by the Timer: SQL code create or replace procedure myprocedure is begin insert into jobtest values (to_char (sysdate, 'yyyy-MM-dd hh: mm: ss '), 'july', 22); commit; end; a timer is set up next: SQL code var myjob number; begin dbms_job.submit (: myjob, 'myprocessure;', sysdate, 'sysdate + 1/24/60/60 '); end; last startup Timer Tool: SQL code begin dbms_job.run (: myjob); end; after the start, the timer (theoretically) will insert a record into the data table jobtest for one second. But it is actually executed every five seconds (unknown reason ). If the timer does not run after the preceding SQL statement is created, run select * from v $ parameter t where t as a dba role. name like 'job % '; check the value. If it is equal to 0, set it to a value greater than 0: \ oracle \ admin \ July \ pfile \ init. modify the file in ora but restart the database (July indicates the Instance name): job_queue_interval = 10 job_queue_processes = 4; if you do not want to restart the database alter system set job_queue_processes = 5; view the Timer: log On As A dba and execute select * from dba_jobs; or select * from user_jobs as the current user; Delete the timer begin dbms_job.remove (jobno); end; commit; j Obno is the number in the job query list. After deletion, you must submit a transaction to delete it completely. You can also Log On As A dba and delete it from the dba_jobs table. Occasionally, some timers take a long time to run. To stop them during execution, you must kill the timer and wait for it to release resources. related statements: SQL code select * from V $ session t order by t. USERNAME, t. SID; SQL code select sid, serial #, username, program, machine, status from v $ session; select * from v $ access t where t. OWNER = 'july'; SQL code alter system kill session 'x, y'; Description of timer parameters: the myjob parameter is the binary_ineger returned by the Submit () process. This value is used to uniquely identify a job. what parameter is the PL/SQL code block to be executed. Here it refers to a stored procedure. Pay attention to the semicolon after the name; the next_date parameter specifies when the job will be run. You do not need to specify this value when writing a Job. The interval parameter is used to re-execute the Job. For more information about interval Settings, see the following example: 1. Execute Interval => TRUNC (sysdate, 'mi') + 1/(24*60) per minute. 2. Execute regular intervals every day. For example: execute Interval => TRUNC (sysdate) + 1 + 2/(24) at every day. 3. Perform scheduled execution on a weekly basis, for example: execute Interval => TRUNC (next_day (sysdate, 2) + 2/24 at every Monday -- Monday, the second day of the week 4. scheduled monthly execution for example: execute Interval => TRUNC (LAST_DAY (SYSDATE) at on the first day of every month )) + 1 + 2/245. Periodical execution on a quarterly basis. For example, Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') is executed at on the first day of each quarter ') + 2/246. Periodical execution every six months. For example, the annual scheduled execution is performed at Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 2/247. For example: execute Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 2/24 at every year.