Oracle日志定期清理存储过程

常要Oracle数据库定时的自动执行一些脚本，或做数据库备份，或做数据的提炼，或做数据库的性能优化，包括重建索引等等的工作,这时需要用到一个函数dbms_job.submit，来完成Oracle定时器Job时间的处理上。使用dbms_job.submit这个函数，我们只需要考虑两个事情：安排某一任务，和定制一个执行任务的时间点。但最重要也是最棘手的事情，我认为还是确定一个执行任务的时间点。时间点确定了，其他的事情就好办了。下面是函数dbms_job.submit使用方法： Java代码 <！--[if ！supportLists]-->1. <！--[endif]-->dbms_job.submit（ job out binary_integer,<！--[if ！supportLists]-->2. <！--[endif]-->what in archar2,<！--[if ！supportLists]-->3. <！--[endif]-->next_date in date，<！--[if ！supportLists]-->4. <！--[endif]-->interval in varchar2,<！--[if ！supportLists]-->5. <！--[endif]-->no_parse in boolean）
其中：
●job：输出变量，是此任务在任务队列中的编号；
●what：执行的任务的名称及其输入参数；
●next_date：任务执行的时间；
●interval：任务执行的时间间隔。
其中Interval这个值是决定Job何时，被重新执行的关键；当interval设置为null时，该job执行结束后，就被从队列中删除。假如我们需要该job周期性地执行，则要用‘sysdate＋m’表示。如何更好地确定执行时间的间隔需要我们掌握一个函数TRUNC。 1.TRUNC（for dates）
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date 一个日期值
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况：
1）按年截尾
select TRUNC（TO_DATE（"2008-03-01 08:23","yyyy-mm-dd hh:mi"）,"yyyy"） from dual
-----------------------------------------------------------
2008-1-1
2）按月截尾
select TRUNC（TO_DATE（"2008-03-01 08:23","yyyy-mm-dd hh:mi"）,"mm"） from dual
--------------------------------------------------------
2008-3-1
3）按日截尾
select TRUNC（TO_DATE（"2008-03-01 08:23","yyyy-mm-dd hh:mi"）,"dd"） from dual
----------------------------------------------------------------------
2008-3-1
4）按时截尾
select TRUNC（TO_DATE（"2008-03-01 08:23","yyyy-mm-dd hh:mi"）,"hh"） from dual
----------------------------------------------------------------------
2008-3-1 8:00:00
5）按分截尾
select TRUNC（TO_DATE（"2008-03-01 08:23","yyyy-mm-dd hh:mi"）,"mi"） from dual
----------------------------------------------------------------------
2008-3-1 8:23:00 2.确定执行时间间隔
1）每分钟执行
Interval => TRUNC（sysdate,"mi"） + 1 / （24*60）或Interval => sysdate+1/1440
2）每天定时执行
例如：每天的凌晨2点执行
Interval => TRUNC（sysdate） + 1 +2 / （24）
3）每周定时执行
例如：每周一凌晨2点执行
Interval => TRUNC（next_day（sysdate,2））+2/24 --星期一,一周的第二天 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"）,12）+2/24
3.实例
这里提供了一个简单的例子，主要是完成在每一个时间间隔内向一个表中插入一条记录
1）创建测试表 Java代码 <！--[if ！supportLists]-->1. <！--[endif]-->   <！--[if ！supportLists]-->2. <！--[endif]--> SQL>   create   table   test（id number,cur_time   date）;    <！--[if ！supportLists]-->3. <！--[endif]--> 表已创建。 <！--[if ！supportLists]-->4. <！--[endif]-->----建sequence <！--[if ！supportLists]-->5. <！--[endif]-->CREATE SEQUENCE test_sequence <！--[if ！supportLists]-->6. <！--[endif]-->INCREMENT BY   1 -- 每次加几个   <！--[if ！supportLists]-->7. <！--[endif]--> START WITH   1 -- 从1开始计数   <！--[if ！supportLists]-->8. <！--[endif]--> NOMAXVALUE -- 不设置最大值   <！--[if ！supportLists]-->9. <！--[endif]--> NOCYCLE -- 一直累加，不循环   <！--[if ！supportLists]-->10. <！--[endif]--> CACHE 10 ; --建触发器代码为： Java代码 <！--[if ！supportLists]-->1. <！--[endif]-->create or replace trigger tri_test_id <！--[if ！supportLists]-->2. <！--[endif]--> before insert on test   --test 是表名 <！--[if ！supportLists]-->3. <！--[endif]--> for each row <！--[if ！supportLists]-->4. <！--[endif]-->declare <！--[if ！supportLists]-->5. <！--[endif]--> nextid number; <！--[if ！supportLists]-->6. <！--[endif]-->begin <！--[if ！supportLists]-->7. <！--[endif]--> IF :new.id IS NULLor :new.id=0 THEN --id是列名 <！--[if ！supportLists]-->8. <！--[endif]-->    select test_sequence.nextval --SEQ_ID正是刚才创建的 <！--[if ！supportLists]-->9. <！--[endif]-->    into nextid <！--[if ！supportLists]-->10. <！--[endif]-->    from sys.dual; <！--[if ！supportLists]-->11. <！--[endif]-->    :new.id:=nextid; <！--[if ！supportLists]-->12. <！--[endif]--> end if; <！--[if ！supportLists]-->13. <！--[endif]-->end tri_test_id;   <！--[if ！supportLists]-->14. <！--[endif]-->

2）创建一个自定义过程 Java代码 <！--[if ！supportLists]-->1. <！--[endif]-->SQL>   create   or   replace   procedure   proc_test   as    <！--[if ！supportLists]-->2. <！--[endif]-->          begin    <！--[if ！supportLists]-->3. <！--[endif]-->          insert   into   test（cur_time）   values（sysdate）;    <！--[if ！supportLists]-->4. <！--[endif]-->          end;    <！--[if ！supportLists]-->5. <！--[endif]-->          / <！--[if ！supportLists]-->6. <！--[endif]-->

过程已创建。

3）创建JOB Java代码 <！--[if ！supportLists]-->1. <！--[endif]-->SQL> declare job1 number; <！--[if ！supportLists]-->2. <！--[endif]-->     begin <！--[if ！supportLists]-->3. <！--[endif]-->        dbms_job.submit（job1,"proc_test;",sysdate,"sysdate+1/1440"）;--每天1440分钟，即一分钟运行test过程一次 <！--[if ！supportLists]-->4. <！--[endif]-->    end;

PL/SQL   JOB已成功完成。     ----------------------------------------------
--1 、建立一个存储过程，转历史并删除，假设表名名:test，历史表：test_his（两表结构一样）：如CREATE OR REPLACE PROCEDURE delhisdata ASBEGIN INSERT INTO test_his    SELECT * FROM test WHERE ins_date < trunc（add_months（SYSDATE, -12））; DELETE FROM test t WHERE ins_date < trunc（add_months（SYSDATE, -12））; COMMIT;EXCEPTION WHEN OTHERS THEN    ROLLBACK;END;/--1、数据库中建立一个JOB对存储过程进行调用，并且每月执行一次，DECLARE jobno NUMBER;BEGIN DBMS_JOB.SUBMIT（JOB       => jobno, /*自动生成JOB_ID*/                  WHAT      => "delhisdata;", /*需要执行的过程或SQL语句*/                  NEXT_DATE => TRUNC（SYSDATE + 1） + 2 / 24, /*初次执行时间*/                  INTERVAL => "TRUNC（add_months（SYSDATE,1））+2/24"）; /*执行周期*/ COMMIT;END;/Linux下Oracle的安装全记录Oracle BI Interactive Dashboards链接错误解决方法相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）