oracle分区表创建删除增加查询问题 - 雅安论坛

select * from DBA_PART_TABLES
//查询分区详细情况
select * from DBA_TAB_PARTITIONS
SELECT * FROM dba_segments t WHERE t.segment_name ='CXSJ_DNOLD_302';
//查询分区表上的数据有哪些
select *From CXSJ_DNOLD_302 partition(DNOLD_4_302_4)
//创建范围类型的分区表
CREATE TABLE CXSJ_DNOLD_302
(
ID NUMBER NOT NULL PRIMARY KEY,
ENDTIME DATE,
OID VARCHAR2(200),
LOU VARCHAR2(50),
DIZHI VARCHAR2(100),
JG VARCHAR2(30),
FJ VARCHAR2(30),
ZHI VARCHAR2(50),
LU VARCHAR2(10)
)
PARTITION BY RANGE (ENDTIME)
(
PARTITION DNOLD_4_302_1 VALUES LESS THAN (TO_DATE('2021-09-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_302_1,
PARTITION DNOLD_4_302_2 VALUES LESS THAN (TO_DATE('2021-10-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_302_2,
PARTITION DNOLD_4_302_3 VALUES LESS THAN (TO_DATE('2021-11-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_302_3,
PARTITION DNOLD_4_302_4 VALUES LESS THAN (TO_DATE('2021-12-01','yyyy-mm-dd')) TABLESPACE CXSJ_DNOLD_4_302_4
);
//添加新分区
ALTER TABLE CXSJ_DNOLD_302 ADD PARTITION DNOLD_4_302_5 VALUES LESS THAN(TO_DATE('2022-01-01','YYYY-MM-DD')) TABLESPACE CXSJ_DNOLD_4_302_5;
//删除分区
ALTER TABLE CXSJ_DNOLD_302 DROP PARTITION DNOLD_4_302_5;

复制代码