按天创建分区表

1、先创建分区表，建立第一个分区

create table cmp  
(  
      transaction_id number primary key,  
      item_id number(8) not null,  
     item_description varchar2(300),  
     transaction_date date not null    
)  
  partition by range (transaction_id,transaction_date)  
  (
  partition part_01 values less than('20120101',to_date('2012-01-01','yyyy-mm-dd')) tablespace CNTV_MSS_DATA,  

 ); 

2、计算时间，脚本增加剩余分区


select to_char(to_date('2017-01-01','yyyy-mm-dd')-to_date('2011-09-08','yyyy-mm-dd')-1) from dual;


3、使用spool打印出执行脚本


set serveroutput on
set linesize 1500


spool d:\sp.sql


declare


a varchar2(20000);
b varchar2(20000):='alter table &tablename add partition PARTITION_MAXVALUE values less than (MAXVALUE,MAXVALUE)';
i int:=1;


begin
for i in 1..1095
loop
select 'alter table &tablename add partition PARTITION_' ||
           to_char(to_date('20120101','yyyy-MM-dd') + i, 'yyyy_mm_dd') ||
           ' values less than (20120101,TO_DATE(''' || ' ' ||
          to_char(to_date('20120101','yyyy-MM-dd') + i+1, 'yyyy_mm_dd') || ' ' || '00:00:00''' || ',' ||
          ' ''' || 'SYYYY-MM-DD HH24:MI:SS''' || ',' || '''' ||
           'NLS_CALENDAR=GREGORIAN''' || '))' || ';'
     into a from dual;
dbms_output.enable(200000);
dbms_output.put_line(a);
end loop;
dbms_output.put_line(b);
end;
/


spool off