Oracle数据库的定时作业
来源地址：https://www.jianshu.com/p/033da4415cdc
--1、创建一张测试表A_RUNINFO create table A_RUNINFO (RUNTIME date); --2、创建一个自定义存储过程 create or replace procedure P_JOB_CLARE_TABLE_DEBRIS is begin --插入数据 insert into A_RUNINFO values (sysdate); --定义游标整理所有表的碎片(ED开头所有表) DECLARE CURSOR cur_stu IS select * from user_tables; --where TABLE_NAME like \'ED\_%\' ESCAPE \'\\'; -- 步骤1: 声明游标 v_stu cur_stu%ROWTYPE; BEGIN OPEN cur_stu; -- 步骤2: 打开游标 LOOP FETCH cur_stu INTO v_stu; -- 步骤3: 提取数据 EXIT WHEN cur_stu%NOTFOUND; --打印 dbms_output.put_line(\'alter table \'|| v_stu.TABLE_NAME ||\' move\'); --整理碎片 execute immediate \'alter table \'|| v_stu.TABLE_NAME ||\' move\'; --定义查找索引游标 DECLARE CURSOR cur_stu2 IS select index_name from user_indexes where table_name = v_stu.TABLE_NAME AND STATUS = \'UNUSABLE\'; v_stu2 cur_stu2%ROWTYPE; BEGIN OPEN cur_stu2; -- 步骤2: 打开游标 LOOP FETCH cur_stu2 INTO v_stu2; -- 步骤3: 提取数据 EXIT WHEN cur_stu2%NOTFOUND; execute immediate \'alter index \'||v_stu2.index_name||\' rebuild\'; END LOOP; CLOSE cur_stu2; -- 步骤4: 关闭游标 END; END LOOP; CLOSE cur_stu; -- 步骤4: 关闭游标 EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLCODE || \' : \' || SQLERRM); dbms_output.put_line(dbms_utility.format_error_backtrace); END; ----执行以下语句时，有可能权限不足 ，执行语句（给用户testuser授予）：-- grant create any table to testuser; --BEGIN -- --编译索引，否则执行move语句后索引会失效 -- execute immediate \'alter index RUNINFO_INDEX rebuild online\'; --END; end P_JOB_CLARE_TABLE_DEBRIS; --3、创建job declare JOB_CLARE_TABLE_DEBRIS number; begin dbms_job.submit(JOB_CLARE_TABLE_DEBRIS,\'P_JOB_CLARE_TABLE_DEBRIS;\',sysdate,\'SYSDATE+1\'); --注意P_JOB_CLARE_TABLE_DEBRIS后面有个“；”，这个必须带上 --每天运行一次 \'SYSDATE+1\' --每分钟一次\'sysdate+1/1440\' end; --4、查看job select * from sys.user_jobs; --查看job列表 select * from dba_jobs_running; --查看job是否还在运行 --5、运行job begin dbms_job.run(v_job); --v_job为job的编号 end; --6、停止一个job begin dbms_job.broken(v_job,true,sysdate); --sysdate(立刻停止)，next_date(某一时刻停止) end; --7、删除一个job begin dbms_job.remove(27); end; --8、修改下一次运行时间 dbms_job.next_date(v_job,sysdate); --9、创建job的另一语法 declare JOB_CLARE_TABLE_DEBRIS number; begin sys.dbms_job.submit(job =>JOB_CLARE_TABLE_DEBRIS,what=>\'P_JOB_CLARE_TABLE_DEBRIS;\',next_date=>to_date(\'30-11-2018 23:30:01\',\'dd-mm-yyyy HH24:mi:ss\'),interval=>\'sysdate+1/1440\'); --注意P_JOB_CLARE_TABLE_DEBRIS后面有个“；”，这个必须带上 commit; end; --碎片整理 alter table A_RUNINFO move; --创建索引 create index RUNINFO_INDEX on A_RUNINFO(RUNTIME); --索引状态查询 select index_name,tablespace_name ,status ,visibility from dba_indexes where table_name=\'A_RUNINFO\' --重新编译索引 alter index RUNINFO_INDEX rebuild online; --测试 select * from A_RUNINFO order by RUNTIME desc --删除表数据 delete from A_RUNINFO --执行存储过程 begin P_JOB_CLARE_TABLE_DEBRIS(); end;
————————————–
最后那一项可以参考如下： 每天午夜12点 \'TRUNC(SYSDATE + 1)\' 每天早上8点30分 \'TRUNC(SYSDATE + 1) + (8*60+30)/(24*60)\' 每星期二中午12点 \'NEXT_DAY(TRUNC(SYSDATE ), \'\'TUESDAY\'\' ) + 12/24\' 每个月第一天的午夜12点 \'TRUNC(LAST_DAY(SYSDATE ) + 1)\' 每个季度最后一天的晚上11点 \'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), \'Q\' ) -1/24\' 每星期六和日早上6点10分 \'TRUNC(LEAST(NEXT_DAY(SYSDATE, \'\'SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6*60+10)/(24*60)\' 其中li_jobno是它的ID,可以通过这个ID停掉这个任务，最后想说的是不要执行多次，你可以在里面管理起来，发现已经运行了就不SUBMIT select TRUNC(SYSDATE,\'MONTH\'),last_day(sysdate) from dual --每个月的第一天和最后一天 每天运行一次 \'SYSDATE + 1\' 每小时运行一次 \'SYSDATE + 1/24\' 每10分钟运行一次 \'SYSDATE + 10/（60*24）\' 每30秒运行一次 \'SYSDATE + 30/(60*24*60)\' 每隔一星期运行一次 \'SYSDATE + 7\' 不再运行该任务并删除它 NULL 每年1月1号零时 trunc(last_day(to_date(extract(year from sysdate)||\'12\'||\'01\',\'yyyy-mm-dd\'))+1 ------------------- Job的参数： 一：时间间隔执行（每分钟，每天，每周，:每月，每季度，每半年，每年） interval是指上一次执行结束到下一次开始执行的时间间隔，当interval设置为null时，该job执行结束后， 就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。 1:每分钟执行 Interval => TRUNC(sysdate,\'mi\') + 1/ (24*60) 或 Interval => sysdate+1/1440 2:每天定时执行 例如：每天的凌晨1点执行 Interval => TRUNC(sysdate) + 1 +1/ (24) 3:每周定时执行 例如：每周一凌晨1点执行 Interval => TRUNC(next_day(sysdate,\'星期一\'))+1/24 4:每月定时执行 例如：每月1日凌晨1点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24 5:每季度定时执行 例如每季度的第一天凌晨1点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),\'Q\') + 1/24 6:每半年定时执行 例如：每年7月1日和1月1日凌晨1点 Interval => ADD_MONTHS(trunc(sysdate,\'yyyy\'),6)+1/24 7:每年定时执行 例如：每年1月1日凌晨1点执行 Interval =>ADD_MONTHS(trunc(sysdate,\'yyyy\'),12)+1/24 二： 在一个特定的时间间隔后，重复运行该任务。 在特定的日期和时间运行任务。 任务成功完成后，下一次执行应该在一个特定的时间间隔之后。 第一种调度任务需求的日期算法比较简单，即\'SYSDATE+n\',这里n是一个以天为单位的时间间隔。表1给出了一些这种时间间隔设置的例子。 表1 一些简单的interval参数设置例子 描述 Interval参数值 每天运行一次 \'SYSDATE + 1\' 每小时运行一次 \'SYSDATE + 1/24\' 每10分钟运行一次 \'SYSDATE + 10/（60*24）\' 每30秒运行一次 \'SYSDATE + 30/(60*24*60)\' 每隔一星期运行一次 \'SYSDATE + 7\' 不再运行该任务并删除它 NULL 表1 所示的任务间隔表达式不能保证任务的下一次运行时间在一个特定的日期或者时间，仅仅能够指定一个任务两次运行之间的时间间隔。例如，如果一个任务第一次运行是在凌晨12点，interval指定为\'SYSDATE + 1\',则该任务将被计划在第二天的凌晨12点执行。但是，如果某用户在下午4点手工（DBMS_JOB.RUN）执行了该任务，那么该任务将被重新定时到第二天的下午4点。还有一个可能的原因是如果数据库关闭或者说任务队列非常的忙以至于任务不能在计划的那个时间点准时执行。在这种情况下，任务将试图尽快运行，也就是说只要数据库一打开或者是任务队列不忙就开始执行，但是这时，运行时间已经从原来的提交时间漂移到了后来真正的运行时间。这种下一次运行时间的不断“漂移”是采用简单时间间隔表达式的典型特征。 第二种调度任务需求相对于第一种就需要更复杂的时间间隔（interval）表达式，表7是一些要求在特定的时间运行任务的interval设置例子。 表 2. 定时到特定日期或时间的任务例子 描述 INTERVAL参数值 每天午夜12点 \'TRUNC(SYSDATE + 1)\' 每天早上8点30分 \'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)\' 每星期二中午12点 \'NEXT_DAY(TRUNC(SYSDATE ), \'\'TUESDAY\'\' ) + 12/24\' 每个月第一天的午夜12点 \'TRUNC(LAST_DAY(SYSDATE ) + 1)\' 每个季度最后一天的晚上11点 \'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), \'Q\' ) -1/24\' 每星期六和日早上6点10分 \'TRUNC(LEAST(NEXT_DAY(SYSDATE, \'\'SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）\' 第三种调度任务需求无论通过怎样设置interval日期表达式也不能满足要求。这时因为一个任务的下一次运行时间在任务开始时才计算，而在此时是不知道任务在何时结束的。遇到这种情况怎么办呢？当然办法肯定是有的，我们可以通过为任务队列写过程的办法来实现。这里我只是简单介绍以下，可以在前一个任务队列执行的过程中，取得任务完成的系统时间，然后加上指定的时间间隔，拿这个时间来控制下一个要执行的任务。这里有一个前提条件，就是目前运行的任务本身必须要严格遵守自己的时间计划。 将任务加入到任务队列之前，要确定执行任务的数据库用户，若用户是scott, 则需要确保该用户拥有执行包dbms_job的权限；若没有，需要以DBA的身份将权利授予scott用户： svrmgrl> grant execute on dbms_job to scott; 4．将要执行的任务写成存储过程或其他的数据库可执行的pl/sql程序段 例如，我们已经建立了一个存储过程，其名称为my_job，在sql/plus中以scott用户身份登录，执行如下命令： sql> variable n number; sql> begin dbms_job.submit(:n‘my_job;’,sysdate, ‘sysdate＋1/360’); commit; end; / 系统提示执行成功。 Sql> print :n; 系统打印此任务的编号，例如结果为300。 如上，我们创建了一个每隔4分钟执行一次的任务号为300的任务。可以通过Oracle提供的数据字典user_jobs察看该任务的执行情况： sql> select job,next_date,next_sec,failures,broken from user_jobs; 执行结果如下： job next_date next_sec failures broken 300 2000/10/10 11:45:15 0 N 这表示任务号为300的任务，下一次将在2000/10/10 11:45:15执行，此任务的执行失败记录为0次。注意：当执行job出现错误时，Oracle将其记录在日志里，失败次数每次自动加1。当执行失败次数达到16时，Oracle就将该job标志为broken。此后，Oracle不再继续执行它，直到用户调用过程dbms_job.broken，重新设置为not broken，或强制调用dbms_job.run来重新执行它。 除了以上我们讨论的submit存储过程之外，Oracle还提供了其他许多存储过程来操作任务。例如：dbms_job.change 、 dbms_job.what、dbms_job.interval可以用来修改提交的任务。要想删除该任务，只需运行dbms_job.remove(n)即可，其中n为任务号。 总结： 1、 每分钟执行 Interval => TRUNC(sysdate,’mi’) + 1 / (24*60) 2、 每天定时执行 例如：每天的凌晨2点执行 Interval => TRUNC(sysdate) + 1 +2 / (24) 3、 每周定时执行 例如：每周一凌晨2点执行 Interval => TRUNC(next_day(sysdate,\'星期一\'))+2/24 4、 每月定时执行 例如：每月1日凌晨2点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24 5、 每季度定时执行 例如每季度的第一天凌晨2点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),\'Q\') + 2/24 6、 每半年定时执行 例如：每年7月1日和1月1日凌晨2点 Interval => ADD_MONTHS(trunc(sysdate,\'yyyy\'),6)+2/24 7、 每年定时执行 例如：每年1月1日凌晨2点执行 Interval =>ADD_MONTHS(trunc(sysdate,\'yyyy\'),6)+2/24 what 参数是将被执行的PL/SQL代码块； next_date 参数指识何时将运行这个工作。写Job的时候可以不指定该值； interval 参数何时这个工作将被重执行。
版权声明：本文为zouhao原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。