oracle，split 分区 sql

--DROP TABLE T_201712120939;
CREATE table T_201712120939
(
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
)
);

ALTER TABLE T_201712120939 MODIFY PARTITION RESULT_PART_201303 ADD SUBPARTITION RESULT_PART_201303_22 VALUES (22);
ALTER TABLE T_201712120939 MODIFY PARTITION RESULT_PART_201303 ADD SUBPARTITION RESULT_PART_201303_61 VALUES (61);

--拆分出一个分区RESULT_PART_201302，less than 2013-03-01 00:00:00
ALTER TABLE T_201712120939
SPLIT PARTITION RESULT_PART_201303 AT (TO_DATE('2013-03-01 00:00:00', 'yyyy-mm-dd HH24:mi:ss'))
INTO (PARTITION RESULT_PART_201302 TABLESPACE USERS, PARTITION RESULT_PART_201303 TABLESPACE USERS);


SELECT * FROM user_tab_partitions WHERE table_name='T_201712120939'
SELECT * FROM user_tab_subpartitions WHERE table_name='T_201712120939'

--查看split分区后表dll

SELECT dbms_metadata.get_ddl('TABLE','T_201712120939') FROM dual;