一，分区表的相关实验
创建一个列表分区表
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
更多信息请查看IT技术专栏
