1、分区表存储 —— 建立分区表
create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace tbspart01,
partition t_range_p2 values less than (20) tablespace tbspart02,
partition t_range_pmax values less than (maxvalue) tablespace tbspart04 );
user_tab_partitions:记录表的分区的信息。
例1:select table_name,partition_count From user_part_tables where table_name='T_PARTITION_RANGE';
例2:select partition_name,high_value,tablespace_name From user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
create table t_partition_range (id number,name varchar2(50))
partition by range(id)(
partition t_range_p1 values less than (10) tablespace tbspart01,
partition t_range_p2 values less than (20) tablespace tbspart02,
partition t_range_pmax values less than (maxvalue) tablespace tbspart04 );
2、分区表查询
user_part_tables:记录分区的表的信息;user_tab_partitions:记录表的分区的信息。
例1:select table_name,partition_count From user_part_tables where table_name='T_PARTITION_RANGE';
例2:select partition_name,high_value,tablespace_name From user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
查询某一分区表的数据:
例3:select * from t_partition_range partition(t_range_p1 );
删除一个有表分区,同时些表分区的数据也将删除
ALTER TABLE t_partition_rangeDROP PARTITION t_range_p1 ;
插入一个表分区,插入时必须是在最大的表分区住下插入
alter table t_partition_range add t_range_p2 values less than (30) tablespace myTableSpace_3;