Oracle建立按時間戳記分區表____Oracle

1 建分區表

-- Create tablecreate table PARTITION_TABLE(  field1   INTEGER,  field2   VARCHAR2(32),  field3   INTEGER,)partition by range(field3)interval (86400)(partition p1 values less than (1431964800) ) nologgingtablespace user;);

2 增加預存程序對擴充的分區改名

CREATE OR REPLACE PROCEDURE PRO_RENAME_PARTITION AS  SQLSTR VARCHAR2(4000);  val2 int;  newpart varchar2(32);BEGIN  for REC in (select table_name                from user_tables               where table_name like 'PARTITION_%') loop    for x in (select high_value, partition_name                from user_tab_partitions               where table_name = REC.TABLE_NAME                 and partition_name not like 'PART_201%') loop      val2    := to_number(x.high_value);      newpart := 'PART_' ||                 to_char(to_date('19700101080000', 'yyyymmddhh24miss') +                         (val2 - 3600) / 86400,                         'yyyymmdd');      sqlstr  := 'alter table ' || REC.TABLE_NAME || ' rename partition ' ||                 x.partition_name || ' to ' || newpart;      begin        execute immediate sqlStr;      exception        when others then          null;      end;    end loop;  end loop;END PRO_RENAME_PARTITION;

3 增加job定時調用預存程序

/begin dbms_scheduler.create_job ( job_name => 'job_pro_rename_partition', job_type => 'PLSQL_BLOCK', job_action => 'begin PRO_RENAME_PARTITION ; end;',repeat_interval => 'FREQ=DAILY;BYHOUR=1;byminute=0', enabled => true); end; /