Oracle job details

Oracle job description 1. set the initialization parameter job_queue_processes SQL> alter system set job_queue_processes = n; (n> 0) job_queue_processes. The maximum value is 1000. view the SQL> select name of the job queue background process, description from v $ bgprocess; 2. the usage of dbms_job package includes the following sub-processes: Broken () process. Change () process. Interval () process. Isubmit () process. Next_Date () process. Remove () process. Run () process. Submit () process. User_Export () process. What () process. Www.2cto.com 1. The Broken () process updates the status of a submitted job. It is typically used to mark a Broken job as a non-Broken job. This process has three parameters: job, broken, and next_date. PROCEDURE Broken (job IN binary_integer, Broken IN boolean, next_date IN date: = SYSDATE) the job parameter is the work number, which uniquely identifies the work IN the problem. The broken parameter indicates whether the job is marked as broken -- TRUE indicates that the job is marked as broken, and FLASE indicates that the job is marked as not broken. The next_date parameter indicates when the job will run again. The default value of this parameter is the current date and time. If a job fails for some reason, oracle will retry 16 times and fail to execute successfully. It will be marked as a job in broken restart status, there are two methods: a. Use dbms_job.run () to immediately execute the job SQL> begin SQL> dbms_job.run (: jobno). The jobno is the job number SQL> end returned when the submit process is submitted; SQL>/B. Use dbms_job.broken () to re-mark the broken as false SQL> begin SQL> dbms_job.broken (: job, false, next_date) SQL> end; the Change () process is used to Change the settings of a specified job. This process has four parameters: job, what, next_date, and interval. PROCEDURE Change (job IN binary_integer, What IN varchar2, next_date IN date, interval IN varchar2) This job parameter is an integer that uniquely identifies the job. The What parameter is a PL/SQL code block that runs from this job. The next_date parameter indicates when the job will be executed. The interval parameter indicates the frequency of re-execution. The bbs.bitsCN.com (China Network Management Forum) 3 and Interval () processes are used to explicitly set the time Interval between re-execution of a job. This process has two parameters: job and interval. PROCEDURE Interval (job IN binary_integer, Interval IN varchar2) the job parameter identifies a specific job. The interval parameter indicates the frequency of re-execution. 4. The ISubmit () process is used to submit a job with a specific job number. This process has five parameters: job, what, next_date, interval, and no_parse. PROCEDURE ISubmit (job IN binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean: = FALSE) This process corresponds to Submit () the only difference IN the process is that this job parameter is passed as an IN parameter and includes a working number provided by the developer. If the provided work number is used, an error is generated. The processes www.2cto.com 5 and Next_Date () are used to explicitly set the execution time of a job. This process receives two parameters: job and next_date. PROCEDURE Next_Date (job IN binary_ineger, next_date IN date) 54ne.com job identifies an existing job. The next_date parameter indicates the date and time when the job should be executed. 6. Remove () to delete a scheduled job. This process receives a parameter PROCEDURE Remove (job IN binary_ineger), which uniquely identifies a job. The value of this parameter is the value of the job parameter returned when Submit () is called for this job. A running job cannot be deleted by a called program. 7. The Run () process is used to immediately execute a specified job. This process only receives one parameter: PROCEDURE Run (job IN binary_ineger) job parameter identifier for the job to be executed immediately. 8. Use the Submit () process and the work is properly planned. This process has five parameters: job, what, next_date, interval, and no_parse. PROCEDURE Submit (job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean: = FALSE) www.2cto.com the job parameter is returned by the Submit () process. This value uniquely identifies a job. What parameter is the PL/SQL code block to be executed. The bbs.bitsCN.com next_date parameter of China Network Management Forum indicates when the job will be run. The interval parameter indicates when the job will be re-executed. The no_parse parameter indicates whether the job should perform syntax analysis at the time of submission or execution -- TRUE indicates that the PL/SQL code should perform syntax analysis during its first execution, FALSE indicates that the PL/SQL code should be analyzed immediately. 9. A command is returned during the User_Export () process. This command is used to schedule an existing job so that the job can be submitted again. This program has two parameters: job and my_call. PROCEDURE User_Export (job IN binary_ineger, my_call in out varchar2) the job parameter identifies a scheduled job. The my_call parameter contains the body required to resubmit the job in its current state. 10. What () process promises to reset the running command during job execution. This process receives two parameters: job and what. Www.2cto.com PROCEDURE What (job IN binary_ineger, What in out varchar2) the job parameter identifies an existing job. What parameter indicates the new PL/SQL code to be executed. 3. View related job information 1. View related dba_jobs all_jobs user_jobs dba_jobs_running contains information about running jobs 2. View related information SQL> SELECT job, NEXT_DATE, NEXT_SEC, FAILURES, broken SQL> FROM DBA_JOBS; JOB NEXT_DATE NEXT_SEC failures B ------- --------- ---------9125 01-JUN-01 00:00:00 4 N 14144 24--01 -01 16:35:35 0 N 9127 01-JUN-01 00:00:00 16 Y 3 rows selected. select sid, r. JOB, LOG_USER, r. THIS_DATE, r. THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r. JOB = j. JOB; sid job LOG_USER THIS_DATE THIS_SEC ----- ---------- ------------- --------- -------- 12 14144 HR 24-OCT-94 17:21:24 25 8536 QS 24-OCT-94 16:45:12 2 rows selected. select sid, TYPE, ID1, ID2 from v $ lock where type = 'jq'; sid ty ID1 ID2 --------- -- --------- 12 JQ 0 14144 1 row selected. www.2cto.com 4. Simple Example 1. 1. Example: create a TEST table SQL> create table TEST (a date); the table has been created. Create a custom process SQL> create or replace procedure MYPROC as 2 begin 3 insert into TEST values (sysdate); 4 end; 5/the process has been created. Create job SQL> variable job1 number; SQL> begin 2 dbms_job.submit (: job1, 'myproc; ', sysdate, 'sysdate + 100'); -- 1/1440 minutes a day, that is, the test process is run at a time of 3 end; 4/PL/SQL process has been completed successfully. Run job SQL> begin 2 dbms_job.run (: job1); 3 end; 4/PL/SQL. SQL> select to_char (a, 'yyyy/mm/dd hh24: mi: ss') time from TEST; time ------------------- 2001/01/07 23:51:21 2001/01/07 23:52:22 2001/01/07 23:53:24 www.2cto.com Delete job SQL> begin 2 dbms_job.remove (: job1); 3 end; 4/PL/SQL process completed successfully. 2. Example of a job executed at on the first day: declare x number; begin sys. DBMS_JOB.SUBMIT (job => X, what => 'syn _ rpt_members_relation; ', next_date => to_date ('02-07-2008 01:00:00', 'dd/mm/yyyy hh24: mi: ss'), interval => 'trunc (sysdate + 1) + (1/24) ', no_parse => FALSE); SYS. DBMS_OUTPUT.PUT_LINE ('job Number is: '| to_char (x); COMMIT; END;/www.2cto.com or above indicates that the Job is executed at every day, if you specify to execute interval at every day, you must specify it as 'trunc (sysdate) + 1 + 123'. If you only specify interval as one day, then when you manually use dbms_job.run (job) when you run the job once, the daily execution time of the job changes. If you want to execute the job at a fixed time every day, refer to the above example. description INTERVAL parameter value: www.2cto.com 08:30 every day 'trunc (SYSDATE + 1) 'every morning 'trunc (SYSDATE + 1) + (8*60 + 30) /(24*60) 'Next _ DAY (TRUNC (SYSDATE), ''tuesday') at every TUESDAY '') + 12/24 'midnight on the first day of each month 'trunc (LAST_DAY (SYSDATE) + 1) 'on the last day of each quarter 'trunc (ADD_MONTHS (SYSDATE + 2/24, 3 ), 'q')-1/24 'every SATURDAY and 06:10' TRUNC (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY "))) + (6 × 60 + 10)/(24 × 60) '1: Execute Interval => TRUNC (sysdate, 'mi') + 1/(24*60) or Interval => sysdate + 1/1440 2: regularly run www.2cto.com every day. For example, execute Interval => TRUNC (sysdate) + 1 + 1/(24) 3 at every day: run www.2cto.com on a regular basis every week. For example, run Interval => TRUNC (next_day (sysdate, 'monday') + 1/24 4: run regularly every month. Example: execute Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 1/24 5 at on the first day of each month: periodical execution on a quarterly basis. For example, Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 1/24 6: Periodical execution on www.2cto.com every six months. Example: interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 1/24 7: scheduled annual execution for example: execute Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 1/24 at every month.