Shulou(Shulou.com)06/01 Report--

Optimize it through case study-- JOB Management

Oracle mainly relies on two packages, DBMS_JOB and DBMS_SCHEDULER, to create and manage job.

[oracle@rh7 ~] $sqlplus'/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 25 17:02:15 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.11:03:43 SYS@ prod > show parameter jobNAME TYPE VALUE -job_queue_processes integer 1000Job_queue_processes = 1000 This is the number of processes used to run JOB. Of course, when JOB is greater than this value in the system, there will be queues. The minimum value is 0, which means that JOB is not running, and the maximum value is 1000.

11:26:14 SCOTT@ prod > desc dbms_job

FUNCTION BACKGROUND_PROCESS RETURNS BOOLEANPROCEDURE BROKEN Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN BROKEN BOOLEAN IN NEXT_DATE DATE IN DEFAULTPROCEDURE CHANGE Argument Name Type In/Out Default?- -JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULTPROCEDURE INSTANCE Argument Name Type In/Out Default?- -JOB BINARY_INTEGER IN INSTANCE BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULTPROCEDURE INTERVAL Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN INTERVAL VARCHAR2 INPROCEDURE ISUBMIT Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULTFUNCTION IS_JOBQ RETURNS BOOLEANPROCEDURE NEXT_DATE Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN NEXT_DATE DATE INPROCEDURE REMOVE Argument Name Type In/Out Default?-JOB BINARY_INTEGER INPROCEDURE RUN Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULTPROCEDURE SUBMIT Argument Name Type In/Out Default? -JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULTPROCEDURE USER_EXPORT Argument Name Type In/Out Default?- -JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUTPROCEDURE USER_EXPORT Argument Name Type In/Out Default?- -JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT MYINST VARCHAR2 IN/OUTPROCEDURE WHAT Argument Name Type In/Out Default?-JOB BINARY_INTEGER IN WHAT VARCHAR2 INDBA_JOBS

= =

The field (column) type describes the unique identification number of the JOB NUMBER task LOG_USER VARCHAR2 (30) the user who submitted the task PRIV_USER VARCHAR2 (30) the user with the task permission SCHEMA_USER VARCHAR2 (30) the user mode LAST_DATE DATE that parses the task the time of the last successful run of the task LAST_SEC VARCHAR2 (8) as shown in Hours of last_date date in HH24:MM:SS format Minutes and seconds the start time of the task that THIS_DATE DATE is running, if no task is running, it is nullTHIS_SEC VARCHAR2 (8) hours, minutes and seconds of this_date dates in HH24:MM:SS format, NEXT_DATE DATE time of the next scheduled run task NEXT_SEC VARCHAR2 (8) hours of next_date dates in HH24:MM:SS format Minutes and seconds TOTAL_TIME NUMBER the total time required for the task to run, in seconds BROKEN VARCHAR2 (1) flag parameter, Y indicates task interrupt The expression used to calculate the next run time will not be run in the future. The number of consecutive unsuccessful runs of the FAILURES NUMBER task WHAT VARCHAR2 (2000) the PL/SQL block of the task CURRENT_SESSION_LABEL RAW MLSLABEL the trust Oracle of the task CLEARANCE_HI RAW MLSLABEL the task's trusted Oracle maximum gap CLEARANCE_LO RAW MLSLABEL the task is trusted Any Oracle minimum gap NLS session run by the NLS_ENV VARCHAR2 (2000) task sets some other session parameters for the MISC_ENV RAW (32) task to run

Create a job:

DBMS_JOB.SUBMIT (

Job OUT BINARY_INTEGER,-- the name of the job parameter (test_job in the example above) what IN VARCHAR2 -- next_date IN DATE DEFAULT sysdate of the executed stored procedure,-- when to run the job interval IN VARCHAR2 DEFAULT 'null' -- when the job is re-executed no_parse IN BOOLEAN DEFAULT FALSE,-- whether to re-parse the instance IN BINARY_INTEGER DEFAULT any_instance -- specify which instance to execute (for RAC) force IN BOOLEAN DEFAULT FALSE-- whether it must be executed by the executing instance) Case test:

1. Set up a test table

11715 SCOTT@ prod 45 SCOTT@ prod > CREATE TABLE DATE_LOG17:18:03 2 (create_date DATE CONSTRAINT create_date_pk PRIMARY KEY); Table created.Elapsed: 0000 create_date DATE CONSTRAINT create_date_pk PRIMARY KEY 01.1517 18 SCOTT@ prod > desc date_log Name Null? Type CREATE_DATE NOT NULL DATE2 、 Create a procedure

17:18:15 SCOTT@ prod > CREATE OR REPLACE PROCEDURE create_date_log_row17:18:27 2 IS17:18:27 3 BEGIN17:18:27 4 INSERT INTO date_log (create_date) VALUES (sysdate); 17:18:27 5 END;17:18:27 6 / Procedure created.3, create a job

17:40:02 SCOTT@ prod > begin17:40:23 2 dbms_job.submit (: jobno,'create_date_log_row;',sysdate,'sysdate+1/1440'); 17:40:31 3 commit;17:40:35 4 end;17:40:36 5 / PL/SQL procedure successfully completed.4, check the test table

17:41:31 SCOTT@ prod > select * from DATE_LOG;CREATE_DA-25-AUG-1425-AUG-1425-AUG-1425-AUG-145, check the work of job

17:42:28 SCOTT@ prod > col interval for a5017 SCOTT@ prod 42 SCOTT@ prod > r 1 select last_date,last_sec,next_date,next_sec,interval Broken from user_jobs 2 * where job=24LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL B-- -25-AUG-14 17:41:41 25-AUG-14 17:42:41 sysdate+1/1440 N LAST_DATE first execution date LAST_SEC first execution time NEXT_DATE next execution date Phase NEXT_SEC next execution time INTERVAL execution frequency 6. Abort and start the execution of job

17:43:22 SCOTT@ prod > exec dbms_job.broken (24real true); PL/SQL procedure successfully completed.17:43:45 SCOTT@ prod > commit; / / Note to submit Commit complete. Check the interrupt information: 17:43:47 SCOTT@ prod > select broken from user_jobs where job = 24th Bmury launch job:17:44:00 SCOTT@ prod > exec dbms_job.broken (24Lim false); PL/SQL procedure successfully completed.17:44:35 SCOTT@ prod > select broken from user_jobs where job = 24th Blyn

7. Modify the execution time of job

11:17:40 SCOTT@ prod > select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs

JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT-- -23 SCOTT 26-AUG-14 11:17:40 26-AUG-14 11:18:40 sysdate+1/ create_dat 1440 e_log_row 24 SCOTT 26-AUG-14 11:10:39 26-AUG-14 11:18:39 sysdate+3/ create_dat (24060) eLog log row; the current next execution time is 11:17:40 SCOTT@ prod > select next_date,next_sec from user_jobs NEXT_DATE NEXT_SEC--26-AUG-14 11:20:4026-AUG-14 11:21:40 modifies the time of the next execution SQL > exec dbms_job.next_date (24herosysdatedates 2 / (24060); SQL > commit) Query next execution time SQL > select next_date,next_sec from user_jobs NEXT_DATE NEXT_SEC-- 26-AUG-14 12:05:58 the fifth step is to modify the job execution frequency SQL > exec dbms_job.interval (24recordsysdatedates 3 / (24060)'); SQL > commit; query execution frequency SQL > select interval from user_jobs INTERVAL-sysdate+3/ (24060)

8. Delete job

SQL > exec dbms_job.REMOVE (24), SQL > commit; 11:25:33 SCOTT@ prod > exec dbms_job.remove (24), PL/SQL procedure successfully completed.11:25:49 SCOTT@ prod > commit;Commit complete.

Appendix:

The setting of Interval is a difficulty. Here are several commonly used setting values:

1. Execute Interval = > TRUNC (sysdate,'mi') + 1 / (24060) 2 every minute, execute regularly every day, for example, execute Interval = > TRUNC (sysdate) + 1 + 2 / (24) 3 every day at 2 am, execute weekly regularly, for example, execute Interval = > TRUNC (next_day (sysdate) at 2 am every Monday. For example, Interval = > TRUNC (LAST_DAY (SYSDATE)) + 1pm 2amp 24 5, quarterly execution e.g. Interval = > TRUNC (ADD_MONTHS (SYSDATE,3)) at 2am on the first day of every quarter For example, Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 6) + 2Accord 24 7 every year, for example, Interval = > ADD_MONTHS (trunc (sysdate,'yyyy')) at 2 am on January 1st every year 6) + 2 TRUNC 24 8, 12:00 'TRUNC (SYSDATE + 1)' 9 every day, 08:30 TRUNC (SYSDATE + 1) + (8 o'clock 60 pm 30) / (24 pm 60)'10, 12:00 NEXT_DAY every Tuesday (TRUNC (SYSDATE)) '' TUESDAY'') + 12 TUESDAY'' 24' 11, 12:00 on the first day of every month TRUNC (LAST_DAY (SYSDATE) + 1)'12, 11:00 in the evening on the last day of each quarter TRUNC (ADD_MONTHS (SYSDATE + 2 NEXT_DAY 24, 3),'Q')-1 TRUNC 24'13, every Saturday and 06:10 every morning (NEXT_DAY (SYSDATE) '' SATURDAY "), NEXT_DAY (SYSDATE," S UNDAY ")) + (6 × 60 × 10) / (24 × 60)'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.