oracle自动删除6个月前的分区并修复失效的索引-CSDN博客

作者：sylar版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

一、思路说明：
通过oracle的scheduler制定周期性的定时任务，并利用scheduler调用后面的存储过程来实现：删除大表6个月前的分区
【注意】由于删除分区后，索引会失效，需要删除后修复下失效的索引

二、具体实现

2.1、scheduler定义：

点击(此处)折叠或打开

begin
  sys.dbms_scheduler.create_job(job_name => 'job_auto_drop_sixmonth', -- JOB名称
                                job_type => 'PLSQL_BLOCK', -- JOB动作类型
                                job_action => 'begin
                                                       pkg_con_content_his_move.proc_create_tab;
                                                       pkg_con_content_his_move.proc_drop_partition("usr_WMS_CITY","con_CONTENT_HISTORY");
                                                       pkg_con_content_his_move.proc_recompile_invalid_index;
                                                    end;', --存储过程
                                start_date => to_date('01-08-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval => 'FREQ=monthly; INTERVAL=1; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
                                end_date => to_date(null), --无结束时间
                                job_class => 'DEFAULT_JOB_CLASS',
                                enabled => true, -->直接启动job..默认是false 需要手动dbms_scheduler.enable
                                auto_drop => true,
                                comments => '自动删除con_CONTENT_HISTORY该表6个月前分区的任务，每月第一天凌晨执行');
end

【参数详解】
JOB_NAME ：指定任务的名称，必选值，注意要确保指定的名称唯一。 JOB_TYPE ：任务执行的操作类型，必选值，有下列几个可选值：
   PLSQL_BLOCK ：表示任务执行的是一个PL/SQL匿名块。
STORED_PROCEDURE ：表示任务执行的是Oracle过程(含PL/SQL PROCEDURE和JAVA PROCEDURE)，本例中正是指定这一参数值。
EXECUTABLE ：表示任务执行的是一个外部程序，比如说操作系统命令。
   CHAIN ：表示任务执行的是一个CHAIN。
JOB_ACTION ：任务执行的操作，必选值，应与JOB_TYPE类型中指定的参数相匹配。比如说对于PL/SQL匿名块，此处就可以放置PL/SQL块的具体代表，类似DECLARE .. BEGIN ..END这类；如果是ORACLE过程，那么此处应该指定具体的过程名，注意由于任务执行，即使过程中有OUT之类参数，实际执行时也不会有输出的。
START_DATE ：指定任务初次执行的时间，本参数可为空，当为空时，表示任务立刻执行，效果等同于指定该参数值为SYSDATE。
REPEAT_INTERVAL ：指定任务执行的频率，比如多长时间会被触发再次执行。本参数也可以为空，如果为空的话，就表示当前设定的任务只执行一次。
  REPEAT_INTERVAL参数的最重要的是FREQ和INTERVAL两个关键字：
  FREQ 用来指定间隔的周期，可选参数有：YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY，分别表示年、月、周、日、时、分、秒等单位。
       INTERVAL 关键字用来指定间隔的频繁，可指定的值的范围从1-99。Freq=Hourly;interval=2相当于每两小时执行一次
       BYDAY：周几用英文缩写的大写表示
       例如：REPEAT_INTERVAL=>'FREQ=DAILY;INTERVAL=1';表示每天执行一次，如果将INTERVAL改为7就表示每7天执行一次，效果等同FREQ=WEEKLY;INTERVAL=1。
END_DATE ：指定任务的过期时间，默认值为NULL。任务过期后，任务的STATE将自动被修改为COMPLETED，ENABLED被置为FALSE。如果该参数设置为空的话，表示该任务永不过期，将一直按照REPEAT_INTERVAL参数设置的周期重复执行，直到达到设置的MAX_RUNS或MAX_FAILURES值。
JOB_CLASS ：指定任务关联的CLASS，默认值为DEFAULT_JOB_CLASS。
ENABLED ：指定任务是否启用，默认值为FALSE。FALSE状态表示该任务并不会被执行，除非被用户手动调用，或者用户将该任务的状态修改为TRUE。 dbms_scheduler.enable
AUTO_DROP ：当该标志被置为TRUE时，ORACLE会在满足条件时自动删除创建的任务
COMMENTS ：设置任务的注释信息，默认值为NULL。

2.2、存储过程展示

点击(此处)折叠或打开

create or replace package pkg_con_content_his_move Authid Current_User is
procedure proc_create_tab;
procedure proc_drop_partition(in_owner in varchar2,in_table_name in varchar2);
procedure proc_recompile_invalid_index;
end pkg_con_content_his_move;
/
create or replace package body pkg_con_content_his_move is
--创建自动编译失效索引和删除6个月前分区事务的记录表
procedure proc_create_tab
is
con_his_move integer ; --将从视图查出表是否存在结果输入该变量
con_his_TAB_PARTITIONS integer;
begin
---记录自动编译失效对象
execute immediate 'select count(*) from user_tables where table_name=upper(''con_content_his_log'') ' into con_his_move;
execute immediate 'select count(*) from user_tables where table_name=upper(''cyt_TAB_PARTITIONS'') ' into con_his_TAB_PARTITIONS;
if con_his_move = 0 then
execute immediate 'create table con_content_his_log(rdate date, errmsg varchar2(100))';
end if;
if con_his_TAB_PARTITIONS = 0 then
execute immediate 'create global temporary table CYT_TAB_PARTITIONS(TABLE_OWNER VARCHAR2(30),table_name VARCHAR2(30),partition_name VARCHAR2 (30),high_value CLOB,partition_position number)';
end if;
end proc_create_tab;
--创建自动删除6个月前数据所在的分区
procedure proc_drop_partition
(in_owner in varchar2,
in_table_name in varchar2 ) --由于执行的表建立得在proc_create_tab执行完后才有，需要用动态SQL实现，故下面用动态游标来实现该方式动态sql循环遍历
is
drop_sql varchar2(200); --中间用到的sql语句
drop_errm varchar2(200); --中间错误信息
TYPE ref_cursor_type IS REF CURSOR;
cur_drop_partition ref_cursor_type;
drop_partition varchar2(200);
sql_1 varchar2(500);
tablename varchar2(20) default 'CYT_TAB_PARTITIONS';
begin
--插入开始操作的信息
execute immediate 'insert into con_content_his_log(rdate,errmsg) values(sysdate,''time to drop the partition six months ago'')';
--处理系统视图将long字段变为可识别的clob字段
execute immediate 'insert into cyt_TAB_PARTITIONS
select p.table_owner,
p.table_name,
p.partition_name,
to_lob(p.high_value),
p.partition_position
from DBA_TAB_PARTITIONS p
WHERE TABLE_NAME = upper(:1) and TABLE_OWNER= upper(:2) ' using in_table_name , in_owner;
--开始删除6月前数据所在的分区
sql_1 :='select p.partition_name from (
select table_owner,table_name,partition_name,to_char(substr(high_value,11,10)) d_high_value,partition_position
from '||tablename||'
where to_date(to_char(substr(high_value,11,10)),''yyyy-mm-dd'')<=to_date(trunc((add_months(sysdate,-6)),''mm'' ))
and partition_position<>1)p
order by p.partition_position';
open cur_drop_partition for sql_1;
loop
fetch cur_drop_partition into drop_partition;
if cur_drop_partition%NOTFOUND THEN
exit;
else
drop_sql := 'alter table '||in_owner||'.'||in_table_name||' drop partition '||drop_partition;
begin
execute immediate drop_sql;
exception
when others then
begin
drop_errm := 'error by obj:'||drop_partition||' '||sqlerrm;
execute immediate 'insert into con_content_his_log(rdate, errmsg) values(sysdate,drop_errm)';
end;
end;
end if;
end loop;
commit;
close cur_drop_partition;
end proc_drop_partition ;
--创建编译失效对象的存储过程
procedure proc_recompile_invalid_index
is
str_sql varchar2(200); --中间用到的sql语句
errm varchar2(200); --中间错误信息
cursor cur_invalid_index is select idx.owner,idx.index_name,idx.PARTITION_NAME from (
select i.owner, i.index_name, status,'非分区索引' PARTITION_NAME
from dba_indexes i --统计失效的非分区索引
where i.status NOT IN ( 'N/A','VALID'）
and i.owner not in ('SYS', 'SYSTEM')
union
select i_p1.index_owner owner, i_p1.index_name, i_p1.status,i_p1.PARTITION_NAME
from dba_ind_partitions i_p1 --统计失效的分区索引
where i_p1.status <> 'USABLE'
and i_p1.index_owner not in ('SYS', 'SYSTEM')
) idx;
invalid_index cur_invalid_index%ROWTYPE;
begin
--插入开始操作的信息
execute immediate 'insert into con_content_his_log(rdate, errmsg) values(sysdate,''time to recompile invalid index'')';
--编译失效索引
open cur_invalid_index ;
loop
fetch cur_invalid_index into invalid_index;
if cur_invalid_index%NOTFOUND THEN
exit;
else str_sql := 'ALTER INDEX '||invalid_index.owner||'.'||invalid_index.index_name ||
(case when invalid_index.PARTITION_NAME<>'非分区索引'
then ' REBUILD PARTITION '||invalid_index.PARTITION_NAME
ELSE ' REBUILD '
END) || ' online PARALLEL 8';
begin
execute immediate str_sql;
exception
when others then
begin
errm := 'error by obj:'||invalid_index.partition_name||' '||sqlerrm;
execute immediate 'insert into con_content_his_log(rdate, errmsg) values(sysdate,errm)';
end;
end;
end if;
end loop;
close cur_invalid_index;
end proc_recompile_invalid_index;
end pkg_con_content_his_move;
/

【功能说明】
1、名为pkg_con_content_his_move的包体里面有三个package，分别代表的是
procedure proc_create_tab; --创建记录调用package的日志和处理系统视图long字段的表（详见说明2）
procedure proc_drop_partition(in_owner in varchar2,in_table_name in varchar2); --传入用户名和对象名，删除传入对象的6个月前分区，忽视大小写问题，包里面有upper()函数进行处理大小写问题；
procedure proc_recompile_invalid_index;--恢复失效的索引，在线开8个并行修复
2、在body里第一个package创建CYT_TAB_PARTITIONS原因是： DBA_TAB_PARTITIONS记录分区条件的high_value的这个字段是long字段，得特殊处理成可以识别的 CLOB字段，然后插入的时候 insert into CYT_TAB_PARTITIONS(high_value,....) select (high_value,..) to_lob(p.high_value) from DBA_TAB_PARTITIONS where ....这样子就能找到满足6个月前分区
3、在body的第二个package，由于部分表是由第一个package提供创建，故这里全部写成动态sql，动态游标方式；
其中，在动态SQL里面传入两个参数用的using in_table_name , in_owner方式。
4、删除分区后，需要编辑下失效的索引，索引分为分区索引和非分区索引，故在编辑失效的时候，需要分情况编译，具体SQL详见下面代码

点击(此处)折叠或打开