Oracle 表空间按月建立-优快云博客

本文介绍如何使用Oracle SQL创建带有范围分区的临时表AD_PLAY_LOG_TMP，并将数据从原始表AD_PLAY_LOG迁移到新表中，最后设置主键约束。

create table AD_PLAY_LOG_TMP
(
  AD_PLAY_LOG_ID      NUMBER(10) not null,
  PLAY_DATE           DATE,
  ADD_TIME            DATE
)
PARTITION BY RANGE (PLAY_DATE)
(PARTITION AD_PLAY_LOG201101
VALUES LESS THAN (TO_DATE('2011-01-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201102
VALUES LESS THAN (TO_DATE('2011-02-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201103
VALUES LESS THAN (TO_DATE('2011-03-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201104
VALUES LESS THAN (TO_DATE('2011-04-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201105
VALUES LESS THAN (TO_DATE('2011-05-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201106
VALUES LESS THAN (TO_DATE('2011-06-01', 'yyyy-mm-dd'))
tablespace iimobadp,
PARTITION AD_PLAY_LOG201107
VALUES LESS THAN (TO_DATE('2011-07-01', 'yyyy-mm-dd'))
tablespace iimobadp,
PARTITION AD_PLAY_LOG201108
VALUES LESS THAN (TO_DATE('2011-08-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201109
VALUES LESS THAN (TO_DATE('2011-09-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201110
VALUES LESS THAN (TO_DATE('2011-10-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201111
VALUES LESS THAN (TO_DATE('2011-11-01', 'yyyy-mm-dd'))
tablespace t1,
PARTITION AD_PLAY_LOG201112
VALUES LESS THAN (TO_DATE('2011-12-01', 'yyyy-mm-dd'))
tablespace t1);

insert into ad_play_log_tmp select * from ad_play_log;

drop table ad_play_log;

rename ad_play_log_tmp to ad_play_log;

--最后执行
alter table AD_PLAY_LOG
      add constraint PK_AD_PLAY_LOG primary key (AD_PLAY_LOG_ID);