Oracle分區之四：分區維護和管理

一，分區表的相關實驗
創建一個列表分區表
create table t3(id number,city varchar2(10))
partition by list(city)
(
partition p1 values ('SH','JS','ZJ') ,
partition p2 values ('BJ','TJ','HB') ,
partition p3 values ('GZ','SZ') ,
partition p_others values (default)
);
create or replace procedure proc1
as
begin
for i in 1..1000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'SH';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 1001..2000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'JS';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 2001..3000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'ZJ';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 3001..4000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'BJ';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 4001..5000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'TJ';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 5001..6000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'GZ';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 6001..7000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'HB';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 7001..8000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'SZ';
end loop;
end;
/
exec proc1
create or replace procedure proc1
as
begin
for i in 8001..10000
loop
execute immediate
'INSERT INTO T3 values(:x,:y)' USING i,'AH';
end loop;
end;
/
exec proc1
SQL> SET linesize 200
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1 0
T3 HR P2 0
T3 HR P3 0
T3 HR P_OTHERS 0

實驗一(SPLIT 分區)
alter table t3 split partition p1 values ('JS') into
(partition p1_1,partition p1_2);
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1_1 0
T3 HR P1_2 0
T3 HR P2 0
T3 HR P3 0
T3 HR P_OTHERS 0

實驗二(merge 分區)
alter table t3 merge partitions p1_1,p1_2 into partition p1;
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1 0
T3 HR P2 0
T3 HR P3 0
T3 HR P_OTHERS 0
實驗三、
alter table t3 split partition p2 values ('BJ','TJ') into
(partition p2_1,partition p2_2);
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1 0
T3 HR P2_1 0
T3 HR P2_2 0
T3 HR P3 0
T3 HR P_OTHERS 0

實驗四、
alter table t3 merge partitions p2_1,p2_2 into partition p2;
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1 0
T3 HR P2 0
T3 HR P3 0
T3 HR P_OTHERS 0
實驗五( 向分區某個分區裡增加個分區列值)
SQL> alter table t3 modify partition p3 add values('ZQ');
Table altered.
二，分區索引的相關實驗
實驗六(創建索引分區)
create index idx_t3 on t3(id)
global partition by range(id)
(
partition p1 values less than (1000),
partition p2 values less than (maxvalue)
);
drop index idx_3
create index idx_t3 on t3(id)
global partition by hash(id)
partitions 4;

create table tt2(id number,createdate date)
partition by range(createdate)
subpartition by hash(id) subpartitions 2
(
partition p1 values less than (to_date('2010-07-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2011-01-01','yyyy-mm-dd'))
);

create table tt4(id number,name varchar2(10))
partition by range(name)
(
partition p1 values less than ('h'),
partition p2 values less than ('o')
);
create index idx_tt4 on tt4(id) local;
drop indexe idx_tt4 ;
create index idx_tt4 on tt4(id)
global partition by range(id)
(
partition p1 values less than (1000),
partition p2 values less than (maxvalue)
);
SQL> SET LINESIZE 200
SQL> select INDEX_OWNER, INDEX_NAME,PARTITION_NAME FROM dba_Ind_Partitions where index_name='IDX_TT4';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
HR IDX_TT4 P1
HR IDX_TT4 P2

alter index idx_tt4 split partition p2 at (2000) into
(partition p3,partition p_max);
SQL> select INDEX_OWNER, INDEX_NAME,PARTITION_NAME FROM dba_Ind_Partitions where index_name='IDX_TT4';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
HR IDX_TT4 P1
HR IDX_TT4 P3
HR IDX_TT4 P_MAX

三，分區表交換的相關實驗
Exchange partition提供了一種方式，讓你在表與表或分區與分區之間遷移數據，注意不是將表轉換成分區或非分區的形式，而僅只是遷移表中數據(互相遷移)，
由於其號稱是采用了更改數據字典的方式，因此效率最高(幾乎不涉及io操作)。Exchange partition適用於所有分區格式，你可以將數據從分區表遷移到非分區表，
也可以從非分區表遷移至分區表，或者從hash partition到range partition諸如此類。
其語法：alter table tbname1 exchange partition/subpartition ptname with table tbname2;
注意：在將未分區表的數據遷移到分區表中時，可能出現ora-14099的錯誤，雖然可以用without validation去解決，但是此時進入分區表的數據可能不符合分區規
則。所以without validation一定要慎用。
a,涉及交換的兩表之間表結構必須一致，除非附加with validation子句;
b,如果是從非分區表向分區表做交換，非分區表中的數據必須符合分區表中指定分區的規則，除非附加without validation子句;
c,如果從分區表向分區表做交換，被交換的分區的數據必須符合分區規則，除非附加without validation子句;
d,Global索引或涉及到數據改動了的global索引分區會被置為unusable，除非附加update indexes子句。
注意：
一旦附加了without validation子句，則表示不再驗證數據有效性，因此指定該子句時務必慎重。

創建一個交換分區的普通heap表
SQL> create table exchange_t3(id number,city varchar2(10));
Table created.
SQL> select distinct city from t3 partition (p2);
CITY
----------
TJ
BJ
HB
查看下P2分區有records
SQL> select count(*) from t3 partition (p2);
COUNT(*)
----------
3000
下面是分區表和普通HEAP表交換
alter table t3
exchange partition p2
with table exchange_t3
including indexes
without validation;
驗證下數據，和上面的P2分區數據一致。
SQL> select count(*) from exchange_t3;
COUNT(*)
----------
3000
SQL> select distinct city from exchange_t3;
CITY
----------
TJ
BJ
HB
四，一個實際應用的例子的相關實驗
創建一個分區表，只保留最近2年的財務數據。
create table ware(wareyear varchar2(4),id number)
partition by range (wareyear)
(
partition p_2005 values less than('2006'),
partition p_2006 values less than('2007'),
partition p_max values less than(maxvalue)
);
創建索引
create index idx_ware_id on ware(id)
global partition by range(id)
(
partition p_id_10000 values less than(10000),
partition p_id_max values less than(maxvalue)
);
create index idx_ware_wareyear on ware(wareyear) local;
插入測試數據
insert into ware select '2005',object_id from dba_objects;
insert into ware select '2006',object_id from dba_objects;
commit;
年終，歸檔最早的數據，並加入新財年的數據
create table ware_2007(wareyear varchar2(4),id number);
create index idx_ware_2007 on ware_2007(wareyear);
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的某些分區不可用,必須這樣做
ALTER TABLE TEST truncate Partition P_20120801 update indexes;
ALTER TABLE TEST truncate Partition P_20120801update global indexes;
ALTER TABLE TEST Drop Partition P_20120801 update indexes;
ALTER TABLE TEST Drop Partition P_20120801 update global indexes;

move partition
有子分區的分區不能move,只能move每個子分區(也就是物理分區)
Alter Table TEST Move Partition P_20120801;
由於rowid變了，會導致所有相關索引unusable,必須這樣做
Alter Table TEST Move subPartition P_20100730_P1 update indexes;
Alter Table TEST Move subPartition P_20100730_P2 update global indexes; --Local Index沒有更新
split partion
語法：
alter table <table_name>
split partition <partition_name> at (<value>)
into (partition <partition_name>, partition <partition_name>)
[update [global] indexes];
1 可以對有子分區的分區進行,自動split子分區
2 由於rowid變了，新分區和global index都變為unusable
alter table t3 merge partitions p2_1,p2_2 into partition p2;
合並range分區
ALTER TABLE Test_Tab1
Merge Partitions P_20100715, P_20100731 Into Partition P_20100730
[Update [global] Indexes];
1. 該分區有子分區
2. 有子分區，也可以單獨合並子分區merge subpartition

可以通過下面的視圖獲取分區的信息
dba_segments
dba_part_key_columns
dba_tables
dba_tab_partitions
dba_indexes
dba_ind_partitions