一，分区表的相关实验
创建一个列表分区表
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子句;