Oracle分区之四：分区维护和管理

核心提示：insert into ware_2007 select '2007',object_id from dba_objects;commit;alter table ware split partition p_maxat ('2008') into (partition p_2007,

insert into ware_2007 select '2007',object_id from dba_objects;
commit;
alter table ware split partition p_max
at ('2008') into (partition p_2007, partition p_max);
将p_2007分区放入ware_2007表里
alter table ware exchange partition p_2007
with table ware_2007
including indexes
without validation;
create table ware_2005(wareyear varchar2(4),id number);
create index idx_ware_2005 on ware_2005(wareyear);
alter table ware exchange partition p_2005
with table ware_2005
including indexes
without validation;
删除p_2005分区
alter table ware drop partition p_2005;
导出做归档
[oracle@even admin]$ exp hr/hr@test file=/home/oracle/ware_2005.dmp tables=ware_2005 compress=n
Export: Release 10.2.0.1.0 - Production on Fri Jan 18 05:10:42 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table WARE_2005 50439 rows exported
Export terminated successfully without warnings.
然后删除表
drop table ware_2005;

五，表和索引的维护的常见SQL语句及注意事项
对于分区索引，不能整体进行重建，只能对单个分区进行重建(也就是物理存在的分区)。语法如下：
Alter index idx_name rebuild partition index_partition_name [online nologging]
Alter Index IndexName Rebuild Partition P_Name;
有子分区的本地索引，不能重建某分区，只能对每个子分区进行重建
Alter Index Index_Name Rebuild subPartition P_Sub_Name;
脚本，重建所有unUsable的索引
Select 'alter index ' || Index_Name ||' rebuild;' From User_Indexes Where Status ='UNUSABLE' union
Select 'alter index ' || Index_Name ||' rebuild Partition '||Partition_Name ||';' From User_Ind_Partitions Where Status ='UNUSABLE' union
Select 'alter index ' || Index_Name ||' rebuild subPartition '||subPartition_Name ||';' From User_Ind_subPartitions Where Status ='UNUSABLE';

add parttion
Alter Table TestTab1 Add Partition P1 Values Less Than (20120801);
1, 如果有子分区，且定义了子分区模板，所有的子分区会自动添加
2, 新加分区后，该区没有统计信息，全是空，如果表级不是global_satus,则表级的统计信息也会空
3, 新加分区后，如果表级统计是global_satus，还会出现out of range的问题（CBO估算的选择率很低）
4, 解决2,3问题的方法是：copy_table_stats
exec dbms_stats.copy_table_stats(user, tabname => 'TEST_TAB1', srcpartname =>'P_20120801', dstpartname => 'P_20100208');

tuncate and drop partition
truncate和drop可对有子分区的分区进行
ALTER TABLE TEST truncate Partition P_20120801;
ALTER TABLE TEST Drop Partition P_20120801;
它们会导致globl index的某些分区不可用,必须这样做