一:分区SQL
CREATE TABLE TSPCTLMCSMDT_PARTITIONED (
MODULE VARCHAR2(64) DEFAULT '' COMMENT '模组',
JOB VARCHAR2(1024) DEFAULT '' COMMENT 'Job Name',
EDC_PLAN VARCHAR2(80) COMMENT 'EDC PLAN',
EDC_ITEM VARCHAR2(80) COMMENT 'EDC ITEM',
PRO_GRO VARCHAR2(40) DEFAULT '' COMMENT 'PRODUCT GROUP',
PRODUCT VARCHAR2(40) DEFAULT '' COMMENT '产品',
PRODUCT_TYPE VARCHAR2(2) DEFAULT '' COMMENT '产品类型',
DATA_CREATE_TIME DATE COMMENT '数据创建日期',
ID SERIAL COMMENT '主键列 ID',
CONSTRAINT TSPCTLMCSMDT_PARTITIONED_PK2 PRIMARY KEY (ID)
......
)
PARTITION BY RANGE (DATA_CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2024-01-01', 'yyyy-mm-dd')));
二:分区验证
1.查找表分区
SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES
WHERE NAME='TSPCTLMCSMDT');
2.查找分区数据
select * from TSPCTLMCSMDT partition(_SYS_P4)