--DROP TABLE T_201712121127;
CREATE table T_201712121127
(
SUMMAR_ID NUMBER,
ITEM_ID NUMBER,
VALUE VARCHAR2(400),
RESULT VARCHAR2(20),
TESTSTARTTIME TIMESTAMP(6),
PROJECT_ID NUMBER,
FAILMESSAGE VARCHAR2(1000),
ERRORMESSAGE VARCHAR2(1000),
SPENDTIME NUMBER
)
tablespace USERS
PARTITION BY RANGE("TESTSTARTTIME") SUBPARTITION BY LIST ("PROJECT_ID")
(
PARTITION RESULT_PART_201303 VALUES LESS THAN(TIMESTAMP' 2013-04-01 00:00:00')TABLESPACE USERS
(
SUBPARTITION RESULT_PART_201303_0 VALUES (0) TABLESPACE USERS
)
);
--添加分区时不指定子分区,系统会生成一个默认的子分区(SYS_SUBP141),值为default,4月份所有的数据都会放到此子分区
ALTER table T_201712121127 ADD PARTITION RESULT_PART_201404 values less than (TIMESTAMP' 2014-05-01 00:00:00')
TABLESPACE users;
CREATE table T_201712121127
(
SUMMAR_ID NUMBER,
ITEM_ID NUMBER,
VALUE VARCHAR2(400),
RESULT VARCHAR2(20),
TESTSTARTTIME TIMESTAMP(6),
PROJECT_ID NUMBER,
FAILMESSAGE VARCHAR2(1000),
ERRORMESSAGE VARCHAR2(1000),
SPENDTIME NUMBER
)
tablespace USERS
PARTITION BY RANGE("TESTSTARTTIME") SUBPARTITION BY LIST ("PROJECT_ID")
(
PARTITION RESULT_PART_201303 VALUES LESS THAN(TIMESTAMP' 2013-04-01 00:00:00')TABLESPACE USERS
(
SUBPARTITION RESULT_PART_201303_0 VALUES (0) TABLESPACE USERS
)
);
--添加分区时不指定子分区,系统会生成一个默认的子分区(SYS_SUBP141),值为default,4月份所有的数据都会放到此子分区
ALTER table T_201712121127 ADD PARTITION RESULT_PART_201404 values less than (TIMESTAMP' 2014-05-01 00:00:00')
TABLESPACE users;