按天创建分区表

来源：互联网发布：互联网金融数据编辑：程序博客网时间：2024/04/29 14:14

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