oracle定期生成和删除表分区

在项目中我们有个一个表是秒数据表，这个表每天插入的数据量都有800多万条，所以我们只存储一段时间的数据，半个月之前的数据就会删除掉，这样当我们查询当前即时的数据来绘制图表的时候就会出现问题：Oralce的高水位线的问题。

至于什么是高水位线问题，大家可以百度下，这里摘抄一段：

在oracle里,使用delete删除数据以后，数据库的存储容量不会减少，而且使用delete删除某个表的数据以后，查询这张表的速度和删除之前一样，不会发生变化。 

    因为oralce有一个HWM高水位，它是oracle的一个表使用空间最高水位线。当插入了数据以后，高水位线就会上涨，但是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。除非使用truncate删除数据。那么，这条高水位线在日常的增删操作中只会上涨，不会下跌，所以数据库容量也只会上升，不会下降。而使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用delete删除数据以后，查询的速度还是和delete以前一样。

    在网上查了下，决定用表分区来解决这个问题，方案就是每天定期新建表分区，同时删除旧的表分区。

首先，新建表和分区

  CREATE TABLE "DATASERVER"."SVRSENSORDATA" 
   ( "ID" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
"SSID" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
"TYPE" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE, 
"VALUE" BINARY_DOUBLE DEFAULT 0, 
"TIME" TIMESTAMP (3) NOT NULL ENABLE, 
   )PARTITION BY RANGE (TIME)  --以TIME字段做分区条件
  (
            partition SDP_20140302 values less than (to_date('2014/03/02','YYYY/MM/DD')),
            partition SDP_20140303 values less than (to_date('2014/03/03','YYYY/MM/DD')),
            partition SDP_20500101 values less than (maxvalue)
  );

这里新建了表和表分区，然后要做的就是每天都新建一个新的表分区。  

建立添加新表分区的存储过程

create or replace
PROCEDURE SVRSENSORDATA_ADDPART(
  DaysAmount NUMBER  --要添加多少天之后的分区
)
AS
 v_SqlExec VARCHAR2(2000); --DDL语句变量
 v_PartName VARCHAR2(20);--创建分区的日期(YYYYMMDD)
 v_PartDateStr VARCHAR2(20);--分区时间的字符串形式
 begin
 v_PartName:= 'SDP_' +to_char(sysdate +  DaysAmount,'YYYYMMDD');
 v_partdatestr:= to_char(sysdate +  DaysAmount, 'YYYY-MM-DD');
 v_SqlExec:='alter table SVRSENSORDATA SPLIT partition SDP_20500101 at (to_date('||v_PartDateStr||',''YYYY-MM-DD'')) INTO  (PARTITION '||v_PartName||', PARTITION SDP_20500101)';
 DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
 END SVRSENSORDATA_ADDPART;

建立删除分区的存储过程

create or replace
PROCEDURE SVRSENSORDATA_DROPPART(
  DaysAmount NUMBER --删除间隔日期
) 
AS
 v_SqlExec VARCHAR2(2000); --DDL语句变量
 --先查找DaysAmount之前的表分区
 cursor cursor_part is
 select partition_name from user_tab_partitions
 WHERE table_name= 'SVRSENSORDATA' AND to_date(SUBSTR(partition_name,5,8),'YYYYMMDD')< sysdate- daysamount order by partition_name;
 cursor_oldpart cursor_part%rowType;
 begin
 open cursor_part;
   loop
   fetch cursor_part into cursor_oldpart;
   exit when cursor_part%notfound;
   v_sqlexec:='ALTER TABLE SVRSENSORDATA DROP PARTITION '||cursor_oldpart.partition_name;
   DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
   end loop;
   close cursor_part;
 END SVRSENSORDATA_DROPPART;

然后使用Oracle job 每天定期执行一次这两个过程。