ST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860010','全味食品商贸有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860010','全味食品商贸有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860010','全味食品商贸有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
?
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507240001','3512860005','新干线贸易有限公司','100.78',TO_DATE('2015-07-24','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507250001','3512860005','新干线贸易有限公司','100.78',TO_DATE('2015-07-25','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507260001','3512860005','新干线贸易有限公司','1132.23',TO_DATE('2015-07-26','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201507290001','3512860005','新干线贸易有限公司','1132.23',TO_DATE('2015-07-29','YYYY-MM-DD'),NULL);
?
INSERT INTO LST_RNG_LIST(AUTO_ID,SALE_NO,POS_CODE,POS_NAME,TOTAL_AMOUNT,SALE_DATE,REMARK)
VALUES(SYS_GUID(),'SN201508010001','3512860005','新干线贸易有限公司','1132.23',TO_DATE('2015-08-01','YYYY-MM-DD'),NULL);
?
commit;
3.2.4、查看数据
?
查看分区数据
select * from LST_RNG_LIST partition(P_3512860010);
select * from LST_RNG_LIST partition(P_3512860005);
select * from LST_RNG_LIST subpartition(P_3512860010_SP_20150726);
select * from LST_RNG_LIST subpartition(P_3512834993_SP_20150802);
收集分区统计信息
begin
dbms_stats.gather_table_stats(ownname=>'WKOD_VERIFY',granularity =>'all',tabname=>'LST_RNG_LIST',cascade=>true);
end;
?
查看主分组
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
num_rows=5，LST_PNG_LIST表分别在两个表空间中有5条数据
?
查看子分区
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
![\]()
![\]()
3.2.5、追加分区
?
方式一：追加主分区
alter table LST_RNG_LIST add partitionP_3512834993 values ('3512834993') tablespace TS_3512834993
查看分区
select partition_position,table_name,partition_name,high_value,tablespace_name,num_rows
from user_tab_partitions
where table_name='LST_RNG_LIST';
![\]()
![\]()
?
查看子分区
select subpartition_position,table_name,partition_name,subpartition_name,high_value,tablespace_name,num_rows
from user_tab_subpartitions
where table_name='LST_RNG_LIST';
![\]()
![\]()
默认按照表创建时子分区的的分区规则，自动生成两个子分区（红色框内）
?
删除添加的List分区
alter table LST_RNG_LIST drop partitionP_3512834993;
对应的子分区会自动被drop掉。
?
方式二：追加主分区及其子分区
alter table LST_RNG_LIST add partition P_3512834993 values ('3512