oracle10g分区表的自动维护（原创）

原创类容，如需转载请标明出处：http://blog.csdn.net/rulev5/article/details/8505955

oracle 10g分区表不支持自动化管理，一般都要手动创建分区，手动删除。今天给大家带来了一个自动化管理表空间的脚本。
本脚本主要由3个部分组成：sys_ConfigTable.sql、sys_pro_AddAndDropPartition.sql、sys_pro_MergeTable.sql
1、sys_ConfigTable.sql 主要创建了一张配置表：这里填写具体要自动维护的表名、保存天数、每天分区的个数等等；
2、sys_pro_AddAndDropPartition.sql 这个用来自动增加表分区，删除过期分区；
3、sys_pro_MergeTable.sql 等每天的分区大于1时，我们合并旧的分区，并重建失效的索引。

1、sys_ConfigTable.sql

drop table CONFIGTABLE;create table CONFIGTABLE(  name   VARCHAR2(64) not null,  value  VARCHAR2(64) not null,  type   VARCHAR2(64) not null,  isrun  NUMBER(1) not null,  remark VARCHAR2(64));prompt Loading CONFIGTABLE...insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_M_VISITLOCUS', 'BI_M_VISITLOCUS', 'protocal', 1, '历史记录日志表');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_M_VISITLOCUS', '1000', 'save', 1, '日志保存天数');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_M_VISITLOCUS', '1', 'num_part_byday', 1, '每天要添加的分区个数');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_LOGFILE', 'BI_LOGFILE', 'protocal', 1, '历史记录日志表');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_LOGFILE', '1000', 'save', 1, '日志保存天数');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('BI_LOGFILE', '1', 'num_part_byday', 1, '每天要添加的分区个数');commit;/*insert into CONFIGTABLE (name, value, type, isrun, remark)values ('ACCESSLOG', 'ACCESSLOG', 'protocal', 1, '历史记录日志表');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('ACCESSLOG', '1000', 'save', 1, '日志保存天数');insert into CONFIGTABLE (name, value, type, isrun, remark)values ('ACCESSLOG', '24', 'num_part_byday', 1, '每天要添加的分区个数');commit;*//*-- Create tabledrop table BI_M_VISITLOCUS;create table BI_M_VISITLOCUS(  accesstime    DATE not null,  machineid     VARCHAR2(100) not null,  channeltype   INTEGER default 0 not null,  channelname   VARCHAR2(200),  pagename      VARCHAR2(100),  categoryid    VARCHAR2(100),  categorylevel INTEGER,  category      VARCHAR2(200),  productid     VARCHAR2(200),  productname   VARCHAR2(200),  pagetype      INTEGER,  sessionid     VARCHAR2(100),  producttype   INTEGER default 0,  machinetype   VARCHAR2(10) default 'HD',  remark        VARCHAR2(200),  useragent     VARCHAR2(100))partition by range (ACCESSTIME)(  partition P_20130114_23 values less than (TO_DATE('2013-01-14 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))    --tablespace user);*/

2、sys_pro_AddAndDropPartition.sql

declare  JOBNAME varchar2(100) := 'Job_AddAndDropPartition';  JOB_CNT int;begin  select count(*) into JOB_CNT from user_scheduler_jobs uj where upper(uj.JOB_NAME) = upper(JOBNAME);  if JOB_CNT >= 1 then    begin dbms_scheduler.drop_job(job_name => JOBNAME); end;  end if;end;/   --1、创建新加的分区CREATE OR REPLACE PROCEDURE sys_pro_AddAndDropPartition AS  /*******************************************************  功能：添加分区表的指定分区  示例： exec sys_pro_AddAndDropPartition;  说明：协议、表名称、保存天数从配置表configtable读取  ********************************************************/  v_sql_gettablename       long; --取协议对于的表名sql  v_sql_gettablespacename  long; --取表对应的表空间sql  v_tablename              dbms_sql.Varchar2_Table; --协议对于的表名  v_tableSpaceName         varchar2(32); --表对应的表空间  v_sql_get_partition_name long;  v_del_partition_name     dbms_sql.Varchar2_Table;  v_SqlExec                VARCHAR2(2000); --DDL语句变量  v_SqlDel                 VARCHAR2(2000); --删除表分区  v_Partwareid1            NUMBER; --创建分区的wareid  v_err_num                NUMBER; --ORA错误号  v_err_msg                VARCHAR2(100); --错误描述  partNum                  NUMBER; --添加分区的个数  interval_day             NUMBER;  v_sql_getday             long;  v_saveday                varchar2(32);  v_part_wareid_nim        NUMBER; --v_tablename 表分区的最小wareid号  v_part_wareid_max        NUMBER; --v_tablename 表分区的最大wareid号  v_begin                  NUMBER; ----字符串的开始位置  v_end                    NUMBER; --字符串结束的位置  v_count                  NUMBER; ----取多少个字符串  v_part_name              VARCHAR2(100); --要添加分区表的名称的前缀  num_part_byday           int;--每天要添加的分区个数，从configtable中读取  v_num_part_byday         int;BEGIN  --最外层，对每个协议循环  v_sql_gettablename := 'select value from ConfigTable where type=''protocal'' and isrun=1';  execute immediate v_sql_gettablename bulk collect    into v_tablename;  for k in 1 .. v_tablename.count loop    v_sql_gettablespacename := 'select distinct TABLESPACE_NAME  from user_tab_partitions  where table_name = ''' ||                               v_tablename(k) || '''';    dbms_output.put_line(v_sql_gettablespacename);    execute immediate v_sql_gettablespacename      into v_tableSpaceName;    dbms_output.put_line('表空间为' || v_tableSpaceName);    --字符串的开始位置    select INSTR(partition_name, '_', 1, 1) + 1      into v_begin      from user_tab_partitions     where table_name = UPPER(v_tablename(k))       and rownum < 2;    dbms_output.put_line('字符串的开始位置 ' || v_begin);    --字符串结束的位置    select INSTR(partition_name, '_', -1, 1) + 1      into v_end      from user_tab_partitions     where table_name = UPPER(v_tablename(k))       and rownum < 2;    dbms_output.put_line('字符串结束的位置' || v_end);    ----取多少个字符串    v_count := v_end - v_begin - 1;    dbms_output.put_line('取多少个字符串' || v_count);    --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数    select max(to_number(SUBSTR(partition_name, v_begin, v_count)))      into v_part_wareid_max      from user_tab_partitions     WHERE table_name = UPPER(v_tablename(k));    dbms_output.put_line('最大分区名称' || v_part_wareid_max);    --查询分区表tablename的最小wareid值，删除分区使用    select min(to_number(SUBSTR(partition_name, v_begin, v_count)))      into v_part_wareid_nim      from user_tab_partitions     WHERE table_name = UPPER(v_tablename(k));    dbms_output.put_line('最小分区名称' || v_part_wareid_nim);    --计算分区表的名称    select SUBSTR(partition_name, 1, v_begin - 1)      into v_part_name      from user_tab_partitions     WHERE table_name = UPPER(v_tablename(k))       and rownum < 2;    dbms_output.put_line('分区开头为' || v_part_name);    v_Partwareid1 := v_part_wareid_max;  dbms_output.put_line('最大分区为' || v_Partwareid1 );    partNum       := (trunc(sysdate) - to_date(v_part_wareid_max,'yyyymmdd')) + 1;    dbms_output.put_line('应该新建' || partNum || '天的数据');    --如果表未建好，执行建表语句  if partNum > 0 then      --新加分区表      FOR i IN 1 .. partNum LOOP        dbms_output.put_line('v_Partwareid1:' || v_Partwareid1);        select to_number(to_char((to_date(v_Partwareid1, 'yyyymmdd') + 1),                                 'yyyymmdd'))          into v_Partwareid1          from dual;        dbms_output.put_line(v_Partwareid1); --20111125select b.value into num_part_byday from configtable a, configtable b where a.name = b.name and b.type = 'num_part_byday'           and a.value = UPPER(v_tablename(k)) and a.type = 'protocal' and a.isrun = 1;if num_part_byday=0 or num_part_byday is null  then           num_part_byday:=1;        end if;v_num_part_byday:=0;loop           v_num_part_byday := v_num_part_byday + 24/num_part_byday;          v_SqlExec := 'ALTER TABLE ' || v_tablename(k) ||                       ' ADD PARTITION ' || v_part_name || v_Partwareid1 || '_' ||                       lpad((v_num_part_byday-1), 2, '0') || ' values less than(to_date( ' ||                       v_Partwareid1 || lpad((v_num_part_byday-1), 2, '0') ||                       '5959,''YYYYMMDDhh24miss'')                  ) TABLESPACE ' || v_tableSpaceName;          --          dbms_output.put_line('创建表分区' || i || '=' || v_SqlExec);          --          dbms_output.put_line(v_SqlExec);          DBMS_Utility.Exec_DDL_Statement(v_SqlExec);  exit when v_num_part_byday >= 24;        end loop;      END LOOP;    end if;    --删除过期的分区表    --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数    select max(to_number(SUBSTR(partition_name, v_begin, v_count)))      into v_part_wareid_max      from user_tab_partitions     WHERE table_name = UPPER(v_tablename(k));    dbms_output.put_line('最大分区名称' || v_part_wareid_max);    --查询分区表tablename的最小wareid值，删除分区使用    select min(to_number(SUBSTR(partition_name, v_begin, v_count)))      into v_part_wareid_nim      from user_tab_partitions     WHERE table_name = UPPER(v_tablename(k));    dbms_output.put_line('最小分区名称' || v_part_wareid_nim);    select (to_date(v_part_wareid_max, 'YYYYMMDD') -           to_date(v_part_wareid_nim, 'YYYYMMDD'))      into interval_day      from dual;    dbms_output.put_line('已经创建分区天数为' || interval_day);    select b.value      into v_saveday      from configtable a, configtable b     where a.name = b.name       and b.type = 'save'       and a.value = v_tablename(k);    dbms_output.put_line('数据保存天数为' || v_saveday);    if interval_day > v_saveday then      for j in 1 .. (interval_day - v_saveday) loop        v_sql_get_partition_name := 'select partition_name from user_tab_partitions where table_name= ''' ||                                    v_tablename(k) ||                                    ''' and partition_name like ''%' ||                                    v_part_wareid_nim || '%''';        --        execute immediate v_sql_get_partition_name bulk collect        --          into v_del_partition_name;        dbms_output.put_line(v_sql_get_partition_name);        execute immediate v_sql_get_partition_name bulk collect          into v_del_partition_name;        for l in 1 .. v_del_partition_name.count loop          v_SqlDel := 'ALTER TABLE ' || v_tablename(k) ||                      ' DROP PARTITION ' || v_del_partition_name(l);          dbms_output.put_line(v_SqlDel);          execute immediate (v_SqlDel);        end loop;        v_part_wareid_nim := v_part_wareid_nim + 1;        dbms_output.put_line('已删除' || v_part_wareid_nim);      end loop;    end if;  end loop;  /*  EXCEPTION  WHEN OTHERS THEN      v_err_num := SQLCODE;      v_err_msg := SUBSTR(SQLERRM, 1, 100);      dbms_output.put_line('pro_ADD_PARTITION_wareid执行出现异常,错误码=' ||                           v_err_num || '错误描述=' || v_err_msg);  commit;*/END sys_pro_AddAndDropPartition;/exec sys_pro_AddAndDropPartition;begin dbms_scheduler.create_job ( job_name => 'Job_AddAndDropPartition', job_type => 'PLSQL_BLOCK', job_action => 'begin sys_pro_AddAndDropPartition; end;',repeat_interval => 'FREQ=DAILY;BYHOUR=5;byminute=30', enabled => true); end; /exit;

3、sys_pro_MergeTable.sql

declare  JOBNAME varchar2(100) := 'Job_MergeTable';  JOB_CNT int;begin  select count(*) into JOB_CNT from user_scheduler_jobs uj where upper(uj.JOB_NAME) = upper(JOBNAME);  if JOB_CNT >= 1 then    begin dbms_scheduler.drop_job(job_name => JOBNAME); end;  end if;end;/ create or replace procedure sys_pro_mergetable--(--p_pro in varchar2, --协议名称                                            --p_date          in number,      --合并分区的日期                                           -- tablespace_name in varchar2)   is  --数据文件合并在表空间的名称  /*******************************************************  功能：合并分区表  示例： exec sys_pro_mergetable;  表空间、表通过configtable读取配置  ********************************************************/  v_sql1            long; --取出哪些天分区未合并sql  v_sql2            long; --取出每一天具体没有合并分区sql  v_sql3            long; --合并分区sql  v_PARTITION_NAME  dbms_sql.Varchar2_Table; --存放哪些天分区未合并  v_PARTITION_NAME2 dbms_sql.Varchar2_Table; --存放每一天具体没有合并分区  v_SqlInd          long; --查找失效的索引sql  v_Ind             dbms_sql.Varchar2_Table; --存放失效的索引  v_SqlInd2         long; --重建索引sql  v_SqlInd3         long; --取出当天最大的分区sql  v_PARTITION_NAME3 long; --存放当天最大的分区  --新增部分  v_protocal         varchar2(32);  v_sql_gettablename long;  v_tablename        dbms_sql.Varchar2_Table;  v_tableSpaceName   varchar2(32);  Sqlstr long;begin  --最外层循环，根据协议取出表和表空间  --取出表    v_sql_gettablename := 'select value from ConfigTable where type=''protocal'' and isrun=1';  execute immediate v_sql_gettablename bulk collect    into v_tablename;  for l in 1 .. v_tablename.count loop--取出表空间select distinct tablespace_name into v_tableSpaceName from user_tab_partitions where table_name=v_tablename(l);  --外层循环，取出哪些天分区未合并  v_sql1 := 'select substr(PARTITION_NAME,0,10) from user_tab_partitions where substr(PARTITION_NAME,3,8) <= ' ||            to_number(to_char(sysdate, 'yyyymmdd') - 1) ||            ' and table_name='''||v_tablename(l)||''' having count(PARTITION_NAME)>=2 group by substr(PARTITION_NAME,0,10)';  execute immediate v_sql1 bulk collect    into v_PARTITION_NAME;dbms_output.put_line(v_sql1);  dbms_output.put_line(v_PARTITION_NAME.count);  --第一个内层循环，取出每一天具体没有合并分区  for i in 1 .. v_PARTITION_NAME.count loop    v_sql2 := 'select PARTITION_NAME  from user_tab_partitions where substr(PARTITION_NAME, 0, 10)= ''' ||              v_PARTITION_NAME(i) || '''and table_name = '''|| v_tablename(l)||'''' || 'order by PARTITION_NAME';    execute immediate v_sql2 bulk collect      into v_PARTITION_NAME2;    for j in 1 .. v_PARTITION_NAME2.count - 1 loop      dbms_output.put_line(v_PARTITION_NAME2(j));      --合并分区      v_sql3 := 'alter table ' || v_tablename(l) || ' merge partitions ' ||                v_PARTITION_NAME2(j) || ',' || v_PARTITION_NAME2(j + 1) ||                ' into partition ' || v_PARTITION_NAME2(j + 1) ||                ' tablespace ' || v_tableSpaceName;      dbms_output.put_line(v_sql3);      execute immediate v_sql3;    end loop;    --第二个内层循环重建失效的索引    dbms_output.put_line('结束了');    --查询出失效的索引select NAME into v_protocal from configtable where TYPE='protocal' and VALUE=v_tablename(l);    v_SqlInd := 'select distinct index_name      from user_ind_partitions     where INDEX_NAME like UPPER(''INDEX_' || v_protocal ||                '%'')       and status = ''UNUSABLE''';    dbms_output.put_line(v_SqlInd);    --把失效索引名称赋值给v_Ind    execute immediate v_SqlInd bulk collect      into v_Ind;    dbms_output.put_line(v_Ind.count);    --取出当天最大的分区    v_SqlInd3 := 'select PARTITION_NAME  from user_tab_partitions where substr(PARTITION_NAME, 0, 10)= ''' ||                 v_PARTITION_NAME(i) || ''' and table_name='''||v_tablename(l)||'''' || 'order by PARTITION_NAME';execute immediate v_SqlInd3 into v_PARTITION_NAME3;dbms_output.put_line('最大分区的sql为：'||v_SqlInd3);    --把本地索引重建    for k in 1 .. v_Ind.count loop      v_SqlInd2 := 'alter index ' || v_Ind(k) || ' rebuild partition ' ||                   v_PARTITION_NAME3;      dbms_output.put_line(v_SqlInd2);       execute immediate v_SqlInd2;    end loop;  end loop;  end loop;end;/  begin dbms_scheduler.create_job ( job_name => 'Job_MergeTable', job_type => 'PLSQL_BLOCK', job_action => 'begin sys_pro_mergetable; end;',repeat_interval => 'FREQ=DAILY;BYHOUR=6;byminute=0', enabled => true); end; / exit;