Oracle10g系统管理16

68.分区表：范围分区、散列分区、列表分区、组合分区
范围分区
create table sales(
 customer_id number(3),
 sales_amount number(10,2),
 sales_date date
)partition by range (sales_date)(
 partition p1 values less than ('2010-04-01') tablespace user01,
 partition p2 values less than ('2010-07-01') tablespace user02,
 partition p3 values less than ('2010-10-01') tablespace user03,
 partition p4 values less than ('2011-01-01') tablespace user04
);

set autotrace on explain
select * from sales partition(p1);
select segment_name,partition_name,tablespace_name from user_segments where segment_name='SALES';

 

散列分区
create table product(
 product_id number(6),
 description varchar2(30)
) partition by hash(product_id)(
 partition p1 tablespace user01,
 partition p2 tablespace user02,
 partition p3 tablespace user03,
 partition p4 tablespace user04
);

 

列表分区
create table sales_by_region(
 deptno number,
 dname varchar2(20),
 quarterly_sales number(10,2),
 city varchar2(10)
)partition by list(city)(
 partition p1 values ('北京','天津') tablespace user01,
 partition p2 values ('重庆','成都') tablespace user02,
 partition p3 values ('上海','杭州') tablespace user03,
 partition p4 values ('广州','深圳') tablespace user04
);

select segment_name,partition_name,tablespace_name from user_segments
where segment_name='sales_by_region';

范围/散列组合分区
create table sales_order(
 order_id number,
 order_date date,
 product_id number,
 quantity number
)partition by range(order_date)
subpartition by hash(product_id) subpartitions 4
store in (user01,user02,user03,user04)(
 partition p1 values less than ('2010-04-01'),
 partition p2 values less than ('2010-07-01'),
 partition p3 values less than ('2010-10-01'),
 partition p4 values less than ('2011-01-01')
);

 

范围/列表组合分区
create table sales_region(
 deptno number,
 sales_date date,
 sales_amount number,
 city varchar2(10)
)partition by range(sale_date) subpartition by list(city)(
 partition r1 values less than ('2010-04-01') tablespace user01(
  subpartition r1_1 values ('北京','天津'),
  subpartition r1_1 values ('重庆','成都'),
  subpartition r1_1 values ('上海','杭州'),
  subpartition r1_1 values ('广州','深圳')
 ),
 partition r2 values less than ('2010-07-01') tablespace user02(
  subpartition r2_1 values ('北京','天津'),
  subpartition r2_1 values ('重庆','成都'),
  subpartition r2_1 values ('上海','杭州'),
  subpartition r2_1 values ('广州','深圳')
 ),
 partition r3 values less than ('2010-10-01') tablespace user03(
  subpartition r3_1 values ('北京','天津'),
  subpartition r3_1 values ('重庆','成都'),
  subpartition r3_1 values ('上海','杭州'),
  subpartition r3_1 values ('广州','深圳')
 ),
 partition r4 values less than ('2011-01-01') tablespace user04(
  subpartition r4_1 values ('北京','天津'),
  subpartition r4_1 values ('重庆','成都'),
  subpartition r4_1 values ('上海','杭州'),
  subpartition r4_1 values ('广州','深圳')
 )
);

 

69.修改分区表
为范围分区表增加分区
在范围分区表的尾部增加新分区
alter table sales add partition p5 values less than ('2011-04-01');
在范围分区表的顶部或中间增加分区，使用split partition选项
alter table sales split partition p3 at ('2010-08-01') into (partition p3_1,partition p3_2);

为散列分区表增加分区
alter table product add partition p5;

为列表分区表增加分区
alter table sales_by_region add partition p5 values('拉萨','乌鲁木齐');

为范围/散列分区表增加主分区和子分区
alter table sales_order add partition p5 values less than ('2011-04-01');
alter table sales_order modify partition p5 add subpartition;

为范围/列表分区表增加分区和子分区
alter table sales_reqion add partition r5
values less than ('2011-04-01')(
 subpartition r5_1 values ('北京','上海'),
 subpartition r5_2 values ('广州','重庆')
);
alter table sales_region modify partition r5 add subpartition r5_3 values ('郑州','厦门');

70.删除范围分区表、列表分区表或范围/列表组合分区表的分区
alter table sales drop partition p3_2;
删除范围/列表组合分区表的某个子分区
alter table sales_region drop subpartition r5_3;

删除散列分区或范围/散列组合分区表的分区
alter table product coalesce partition;
删除范围/散列组合分区表的子分区
alter table sales_order modify partition p1 coalesce subpartition;

交换分区数据
alter table sales exchange partition p1 with table sales_1;

截断分区
alter table sales truncate partition p1;

修改分区名
alter table sales rename partition p3_1 to p3;

 

合并分区
alter table sales merge partitions p1,p2 into partition p_half;

重组分区
使用move partition选项可以重组特定分区的所有数据。
可以将特定分区数据移动到其他表空间，或删除特定分区的行迁移。
alter table sales move partition p_half tablespace user01;

为列表分区和子分区增加值
alter table sales_by_region modify partition p1 add values ('呼和浩特');
alter table sales_region modify subpartition r4_1 add values ('呼和浩特');

从列表分区和子分区中删除值
alter table sales_by_region modify partition p1 drop values ('呼和浩特');
alter table sales_region modify subpartition r4_1 drop values ('呼和浩特');

建立全局索引和分区索引
create index glb_sales_date on sales(sales_date) global;
create index lcl_sales_date on sales(sales_date) local;

 

71.DBA_PART_TABLES
ALL_PART_TABLES
USER_PART_TABLES

 

DBA_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS

 

select table_name, partitioning_type, subpartitioning_type from dba_part_tables
where owner=’SCOTT’;

select partion_name,tablespace_name from dba_tab_partitions
where table_owner=’SCOTT’ and table_name=’SALES’;

 

DBA_IND_PARTITIONS
ALL_IND_PARTITIONS
USER_IND_PARTITIONS