记录日常工作关于系统运维,虚拟化云计算,数据库,网络安全等各方面问题。
 
0

oracle10g分区表的自动维护
发表者：admin分类：数据库2015-08-22 10:55:50 阅读[1793]

oracle10g分区表的自动维护


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

[sql] view plaincopy
  1. drop table CONFIGTABLE;  
  2. create table CONFIGTABLE  
  3. (  
  4.   name   VARCHAR2(64) not null,  
  5.   value  VARCHAR2(64) not null,  
  6.   type   VARCHAR2(64) not null,  
  7.   isrun  NUMBER(1) not null,  
  8.   remark VARCHAR2(64)  
  9. )  
  10. ;  
  11.   
  12. prompt Loading CONFIGTABLE...  
  13. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  14. values ('BI_M_VISITLOCUS', 'BI_M_VISITLOCUS', 'protocal', 1, '历史记录日志表');  
  15. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  16. values ('BI_M_VISITLOCUS', '1000', 'save', 1, '日志保存天数');  
  17. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  18. values ('BI_M_VISITLOCUS', '1', 'num_part_byday', 1, '每天要添加的分区个数');  
  19.   
  20. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  21. values ('BI_LOGFILE', 'BI_LOGFILE', 'protocal', 1, '历史记录日志表');  
  22. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  23. values ('BI_LOGFILE', '1000', 'save', 1, '日志保存天数');  
  24. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  25. values ('BI_LOGFILE', '1', 'num_part_byday', 1, '每天要添加的分区个数');  
  26. commit;  
  27.   
  28. /*insert into CONFIGTABLE (name, value, type, isrun, remark)  
  29. values ('ACCESSLOG', 'ACCESSLOG', 'protocal', 1, '历史记录日志表');  
  30. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  31. values ('ACCESSLOG', '1000', 'save', 1, '日志保存天数');  
  32. insert into CONFIGTABLE (name, value, type, isrun, remark)  
  33. values ('ACCESSLOG', '24', 'num_part_byday', 1, '每天要添加的分区个数');  
  34. commit;  
  35. */  
  36.   
  37. /*  
  38. -- Create table  
  39. drop table BI_M_VISITLOCUS;  
  40. create table BI_M_VISITLOCUS  
  41. (  
  42.   accesstime    DATE not null,  
  43.   machineid     VARCHAR2(100) not null,  
  44.   channeltype   INTEGER default 0 not null,  
  45.   channelname   VARCHAR2(200),  
  46.   pagename      VARCHAR2(100),  
  47.   categoryid    VARCHAR2(100),  
  48.   categorylevel INTEGER,  
  49.   category      VARCHAR2(200),  
  50.   productid     VARCHAR2(200),  
  51.   productname   VARCHAR2(200),  
  52.   pagetype      INTEGER,  
  53.   sessionid     VARCHAR2(100),  
  54.   producttype   INTEGER default 0,  
  55.   machinetype   VARCHAR2(10) default 'HD',  
  56.   remark        VARCHAR2(200),  
  57.   useragent     VARCHAR2(100)  
  58. )  
  59. partition by range (ACCESSTIME)  
  60. (  
  61.   partition P_20130114_23 values less than (TO_DATE('2013-01-14 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))  
  62.     --tablespace user  
  63. );  
  64. */  

2、sys_pro_AddAndDropPartition.sql
[sql] view plaincopy
  1. declare  
  2.   JOBNAME varchar2(100) := 'Job_AddAndDropPartition';  
  3.   JOB_CNT int;  
  4. begin  
  5.   select count(*) into JOB_CNT from user_scheduler_jobs uj where upper(uj.JOB_NAME) = upper(JOBNAME);  
  6.   if JOB_CNT >= 1 then  
  7.     begin dbms_scheduler.drop_job(job_name => JOBNAME); end;  
  8.   end if;  
  9. end;  
  10.   
  11. /  
  12.   
  13.    --1、创建新加的分区  
  14. CREATE OR REPLACE PROCEDURE sys_pro_AddAndDropPartition AS  
  15.   /*******************************************************  
  16.   功能：添加分区表的指定分区  
  17.   示例： exec sys_pro_AddAndDropPartition;  
  18.   说明：协议、表名称、保存天数从配置表configtable读取  
  19.   ********************************************************/  
  20.   v_sql_gettablename       long; --取协议对于的表名sql  
  21.   v_sql_gettablespacename  long; --取表对应的表空间sql  
  22.   v_tablename              dbms_sql.Varchar2_Table; --协议对于的表名  
  23.   v_tableSpaceName         varchar2(32); --表对应的表空间  
  24.   v_sql_get_partition_name long;  
  25.   v_del_partition_name     dbms_sql.Varchar2_Table;  
  26.   v_SqlExec                VARCHAR2(2000); --DDL语句变量  
  27.   v_SqlDel                 VARCHAR2(2000); --删除表分区  
  28.   v_Partwareid1            NUMBER; --创建分区的wareid  
  29.   v_err_num                NUMBER; --ORA错误号  
  30.   v_err_msg                VARCHAR2(100); --错误描述  
  31.   partNum                  NUMBER; --添加分区的个数  
  32.   interval_day             NUMBER;  
  33.   v_sql_getday             long;  
  34.   v_saveday                varchar2(32);  
  35.   v_part_wareid_nim        NUMBER; --v_tablename 表分区的最小wareid号  
  36.   v_part_wareid_max        NUMBER; --v_tablename 表分区的最大wareid号  
  37.   v_begin                  NUMBER; ----字符串的开始位置  
  38.   v_end                    NUMBER; --字符串结束的位置  
  39.   v_count                  NUMBER; ----取多少个字符串  
  40.   v_part_name              VARCHAR2(100); --要添加分区表的名称的前缀  
  41.   num_part_byday           int;--每天要添加的分区个数，从configtable中读取  
  42.   v_num_part_byday         int;  
  43. BEGIN  
  44.   --最外层，对每个协议循环  
  45.   v_sql_gettablename := 'select value from ConfigTable where type=''protocal'' and isrun=1';  
  46.   execute immediate v_sql_gettablename bulk collect  
  47.     into v_tablename;  
  48.   for k in 1 .. v_tablename.count loop  
  49.     v_sql_gettablespacename := 'select distinct TABLESPACE_NAME  from user_tab_partitions  where table_name = ''' ||  
  50.                                v_tablename(k) || '''';  
  51.     dbms_output.put_line(v_sql_gettablespacename);  
  52.     execute immediate v_sql_gettablespacename  
  53.       into v_tableSpaceName;  
  54.     dbms_output.put_line('表空间为' || v_tableSpaceName);  
  55.   
  56.     --字符串的开始位置  
  57.     select INSTR(partition_name, '_', 1, 1) + 1  
  58.       into v_begin  
  59.       from user_tab_partitions  
  60.      where table_name = UPPER(v_tablename(k))  
  61.        and rownum < 2;  
  62.     dbms_output.put_line('字符串的开始位置 ' || v_begin);  
  63.     --字符串结束的位置  
  64.     select INSTR(partition_name, '_', -1, 1) + 1  
  65.       into v_end  
  66.       from user_tab_partitions  
  67.      where table_name = UPPER(v_tablename(k))  
  68.        and rownum < 2;  
  69.     dbms_output.put_line('字符串结束的位置' || v_end);  
  70.     ----取多少个字符串  
  71.     v_count := v_end - v_begin - 1;  
  72.     dbms_output.put_line('取多少个字符串' || v_count);  
  73.     --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数  
  74.     select max(to_number(SUBSTR(partition_name, v_begin, v_count)))  
  75.       into v_part_wareid_max  
  76.       from user_tab_partitions  
  77.      WHERE table_name = UPPER(v_tablename(k));  
  78.     dbms_output.put_line('最大分区名称' || v_part_wareid_max);  
  79.     --查询分区表tablename的最小wareid值，删除分区使用  
  80.     select min(to_number(SUBSTR(partition_name, v_begin, v_count)))  
  81.       into v_part_wareid_nim  
  82.       from user_tab_partitions  
  83.      WHERE table_name = UPPER(v_tablename(k));  
  84.     dbms_output.put_line('最小分区名称' || v_part_wareid_nim);  
  85.     --计算分区表的名称  
  86.     select SUBSTR(partition_name, 1, v_begin - 1)  
  87.       into v_part_name  
  88.       from user_tab_partitions  
  89.      WHERE table_name = UPPER(v_tablename(k))  
  90.        and rownum < 2;  
  91.     dbms_output.put_line('分区开头为' || v_part_name);  
  92.     v_Partwareid1 := v_part_wareid_max;  
  93.   dbms_output.put_line('最大分区为' || v_Partwareid1 );  
  94.     partNum       := (trunc(sysdate) - to_date(v_part_wareid_max,'yyyymmdd')) + 1;  
  95.     dbms_output.put_line('应该新建' || partNum || '天的数据');  
  96.     --如果表未建好，执行建表语句  
  97.   if partNum > 0 then  
  98.       --新加分区表  
  99.       FOR i IN 1 .. partNum LOOP  
  100.         dbms_output.put_line('v_Partwareid1:' || v_Partwareid1);  
  101.         select to_number(to_char((to_date(v_Partwareid1, 'yyyymmdd') + 1),  
  102.                                  'yyyymmdd'))  
  103.           into v_Partwareid1  
  104.           from dual;  
  105.         dbms_output.put_line(v_Partwareid1); --20111125  
  106.         select b.value into num_part_byday from configtable a, configtable b where a.name = b.name and b.type = 'num_part_byday'  
  107.            and a.value = UPPER(v_tablename(k)) and a.type = 'protocal' and a.isrun = 1;  
  108.         if num_part_byday=0 or num_part_byday is null  then  
  109.            num_part_byday:=1;  
  110.         end if;  
  111.         v_num_part_byday:=0;  
  112.         loop   
  113.           v_num_part_byday := v_num_part_byday + 24/num_part_byday;  
  114.           v_SqlExec := 'ALTER TABLE ' || v_tablename(k) ||  
  115.                        ' ADD PARTITION ' || v_part_name || v_Partwareid1 || '_' ||  
  116.                        lpad((v_num_part_byday-1), 2, '0') || ' values less than(to_date( ' ||  
  117.                        v_Partwareid1 || lpad((v_num_part_byday-1), 2, '0') ||  
  118.                        '5959,''YYYYMMDDhh24miss'')  
  119.                   ) TABLESPACE ' || v_tableSpaceName;  
  120.           --          dbms_output.put_line('创建表分区' || i || '=' || v_SqlExec);  
  121.           --          dbms_output.put_line(v_SqlExec);  
  122.           DBMS_Utility.Exec_DDL_Statement(v_SqlExec);  
  123.           exit when v_num_part_byday >= 24;  
  124.         end loop;  
  125.       END LOOP;  
  126.     end if;  
  127.     --删除过期的分区表  
  128.   
  129.     --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数  
  130.     select max(to_number(SUBSTR(partition_name, v_begin, v_count)))  
  131.       into v_part_wareid_max  
  132.       from user_tab_partitions  
  133.      WHERE table_name = UPPER(v_tablename(k));  
  134.     dbms_output.put_line('最大分区名称' || v_part_wareid_max);  
  135.     --查询分区表tablename的最小wareid值，删除分区使用  
  136.     select min(to_number(SUBSTR(partition_name, v_begin, v_count)))  
  137.       into v_part_wareid_nim  
  138.       from user_tab_partitions  
  139.      WHERE table_name = UPPER(v_tablename(k));  
  140.     dbms_output.put_line('最小分区名称' || v_part_wareid_nim);  
  141.     select (to_date(v_part_wareid_max, 'YYYYMMDD') -  
  142.            to_date(v_part_wareid_nim, 'YYYYMMDD'))  
  143.       into interval_day  
  144.       from dual;  
  145.     dbms_output.put_line('已经创建分区天数为' || interval_day);  
  146.     select b.value  
  147.       into v_saveday  
  148.       from configtable a, configtable b  
  149.      where a.name = b.name  
  150.        and b.type = 'save'  
  151.        and a.value = v_tablename(k);  
  152.     dbms_output.put_line('数据保存天数为' || v_saveday);  
  153.     if interval_day > v_saveday then  
  154.       for j in 1 .. (interval_day - v_saveday) loop  
  155.         v_sql_get_partition_name := 'select partition_name from user_tab_partitions where table_name= ''' ||  
  156.                                     v_tablename(k) ||  
  157.                                     ''' and partition_name like ''%' ||  
  158.                                     v_part_wareid_nim || '%''';  
  159.         --        execute immediate v_sql_get_partition_name bulk collect  
  160.         --          into v_del_partition_name;  
  161.         dbms_output.put_line(v_sql_get_partition_name);  
  162.         execute immediate v_sql_get_partition_name bulk collect  
  163.           into v_del_partition_name;  
  164.         for l in 1 .. v_del_partition_name.count loop  
  165.           v_SqlDel := 'ALTER TABLE ' || v_tablename(k) ||  
  166.                       ' DROP PARTITION ' || v_del_partition_name(l);  
  167.           dbms_output.put_line(v_SqlDel);  
  168.           execute immediate (v_SqlDel);  
  169.         end loop;  
  170.         v_part_wareid_nim := v_part_wareid_nim + 1;  
  171.         dbms_output.put_line('已删除' || v_part_wareid_nim);  
  172.       end loop;  
  173.     end if;  
  174.   end loop;  
  175.   /*  EXCEPTION  
  176.   WHEN OTHERS THEN  
  177.       v_err_num := SQLCODE;  
  178.       v_err_msg := SUBSTR(SQLERRM, 1, 100);  
  179.       dbms_output.put_line('pro_ADD_PARTITION_wareid执行出现异常,错误码=' ||  
  180.                            v_err_num || '错误描述=' || v_err_msg);  
  181.   commit;*/  
  182. END sys_pro_AddAndDropPartition;  
  183.   
  184. /  
  185.   
  186. exec sys_pro_AddAndDropPartition;  
  187.   
  188. begin   
  189. dbms_scheduler.create_job   
  190. (   
  191. job_name => 'Job_AddAndDropPartition',   
  192. job_type => 'PLSQL_BLOCK',   
  193. job_action => 'begin sys_pro_AddAndDropPartition; end;',  
  194. repeat_interval => 'FREQ=DAILY;BYHOUR=5;byminute=30',   
  195. enabled => true  
  196. );   
  197. end;   
  198. /  
  199.   
  200. exit;  

3、sys_pro_MergeTable.sql
[sql] view plaincopy
  1. declare  
  2.   JOBNAME varchar2(100) := 'Job_MergeTable';  
  3.   JOB_CNT int;  
  4. begin  
  5.   select count(*) into JOB_CNT from user_scheduler_jobs uj where upper(uj.JOB_NAME) = upper(JOBNAME);  
  6.   if JOB_CNT >= 1 then  
  7.     begin dbms_scheduler.drop_job(job_name => JOBNAME); end;  
  8.   end if;  
  9. end;  
  10.   
  11. /  
  12.    
  13. create or replace procedure sys_pro_mergetable--(--p_pro in varchar2, --协议名称  
  14.                                             --p_date          in number,      --合并分区的日期  
  15.                                            -- tablespace_name in varchar2)  
  16.                                            is  
  17.   --数据文件合并在表空间的名称  
  18.   /*******************************************************  
  19.   功能：合并分区表  
  20.   示例： exec sys_pro_mergetable;  
  21.   表空间、表通过configtable读取配置  
  22.   ********************************************************/  
  23.   v_sql1            long; --取出哪些天分区未合并sql  
  24.   v_sql2            long; --取出每一天具体没有合并分区sql  
  25.   v_sql3            long; --合并分区sql  
  26.   v_PARTITION_NAME  dbms_sql.Varchar2_Table; --存放哪些天分区未合并  
  27.   v_PARTITION_NAME2 dbms_sql.Varchar2_Table; --存放每一天具体没有合并分区  
  28.   v_SqlInd          long; --查找失效的索引sql  
  29.   v_Ind             dbms_sql.Varchar2_Table; --存放失效的索引  
  30.   v_SqlInd2         long; --重建索引sql  
  31.   v_SqlInd3         long; --取出当天最大的分区sql  
  32.   v_PARTITION_NAME3 long; --存放当天最大的分区  
  33.   --新增部分  
  34.   v_protocal         varchar2(32);  
  35.   v_sql_gettablename long;  
  36.   v_tablename        dbms_sql.Varchar2_Table;  
  37.   v_tableSpaceName   varchar2(32);  
  38.   Sqlstr long;  
  39. begin  
  40.   --最外层循环，根据协议取出表和表空间  
  41.   --取出表  
  42.     v_sql_gettablename := 'select value from ConfigTable where type=''protocal'' and isrun=1';  
  43.   execute immediate v_sql_gettablename bulk collect  
  44.     into v_tablename;  
  45.       for l in 1 .. v_tablename.count loop  
  46.     --取出表空间  
  47.     select distinct tablespace_name into v_tableSpaceName from user_tab_partitions where table_name=v_tablename(l);  
  48.   --外层循环，取出哪些天分区未合并  
  49.   v_sql1 := 'select substr(PARTITION_NAME,0,10) from user_tab_partitions where substr(PARTITION_NAME,3,8) <= ' ||  
  50.             to_number(to_char(sysdate, 'yyyymmdd') - 1) ||  
  51.             ' and table_name='''||v_tablename(l)||''' having count(PARTITION_NAME)>=2 group by substr(PARTITION_NAME,0,10)';  
  52.   execute immediate v_sql1 bulk collect  
  53.     into v_PARTITION_NAME;  
  54.     dbms_output.put_line(v_sql1);  
  55.   dbms_output.put_line(v_PARTITION_NAME.count);  
  56.   --第一个内层循环，取出每一天具体没有合并分区  
  57.   for i in 1 .. v_PARTITION_NAME.count loop  
  58.     v_sql2 := 'select PARTITION_NAME  from user_tab_partitions where substr(PARTITION_NAME, 0, 10)= ''' ||  
  59.               v_PARTITION_NAME(i) || '''and table_name = '''|| v_tablename(l)||'''' || 'order by PARTITION_NAME';  
  60.     execute immediate v_sql2 bulk collect  
  61.       into v_PARTITION_NAME2;  
  62.     for j in 1 .. v_PARTITION_NAME2.count - 1 loop  
  63.       dbms_output.put_line(v_PARTITION_NAME2(j));  
  64.       --合并分区  
  65.       v_sql3 := 'alter table ' || v_tablename(l) || ' merge partitions ' ||  
  66.                 v_PARTITION_NAME2(j) || ',' || v_PARTITION_NAME2(j + 1) ||  
  67.                 ' into partition ' || v_PARTITION_NAME2(j + 1) ||  
  68.                 ' tablespace ' || v_tableSpaceName;  
  69.       dbms_output.put_line(v_sql3);  
  70.       execute immediate v_sql3;  
  71.     end loop;  
  72.     --第二个内层循环重建失效的索引  
  73.     dbms_output.put_line('结束了');  
  74.     --查询出失效的索引  
  75.     select NAME into v_protocal from configtable where TYPE='protocal' and VALUE=v_tablename(l);  
  76.     v_SqlInd := 'select distinct index_name  
  77.       from user_ind_partitions  
  78.      where INDEX_NAME like UPPER(''INDEX_' || v_protocal ||  
  79.                 '%'')  
  80.        and status = ''UNUSABLE''';  
  81.     dbms_output.put_line(v_SqlInd);  
  82.     --把失效索引名称赋值给v_Ind  
  83.     execute immediate v_SqlInd bulk collect  
  84.       into v_Ind;  
  85.     dbms_output.put_line(v_Ind.count);  
  86.     --取出当天最大的分区  
  87.     v_SqlInd3 := 'select PARTITION_NAME  from user_tab_partitions where substr(PARTITION_NAME, 0, 10)= ''' ||  
  88.                  v_PARTITION_NAME(i) || ''' and table_name='''||v_tablename(l)||'''' || 'order by PARTITION_NAME';  
  89. execute immediate v_SqlInd3  
  90.  into v_PARTITION_NAME3;  
  91.     dbms_output.put_line('最大分区的sql为：'||v_SqlInd3);  
  92.     --把本地索引重建  
  93.     for k in 1 .. v_Ind.count loop  
  94.       v_SqlInd2 := 'alter index ' || v_Ind(k) || ' rebuild partition ' ||  
  95.                    v_PARTITION_NAME3;  
  96.       dbms_output.put_line(v_SqlInd2);  
  97.        execute immediate v_SqlInd2;  
  98.     end loop;  
  99.   end loop;  
  100.   end loop;  
  101. end;  
  102.   
  103.   
  104. /  
  105.     
  106. begin   
  107. dbms_scheduler.create_job   
  108. (   
  109. job_name => 'Job_MergeTable',   
  110. job_type => 'PLSQL_BLOCK',   
  111. job_action => 'begin sys_pro_mergetable; end;',  
  112. repeat_interval => 'FREQ=DAILY;BYHOUR=6;byminute=0',   
  113. enabled => true  
  114. );   
  115. end;   
  116.   
  117. /  
  118.    
  119. exit; 


转载请标明出处【oracle10g分区表的自动维护】。

《www.micoder.cc》 虚拟化云计算,系统运维,安全技术服务.

Tags： [阅读全文...]
网站已经关闭评论