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

当前位置：首页 > 范文|应用文 > IT技术专栏 > 数据库

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

来源：阅读：1160 次日期：2014-09-17 10:18:25

温馨提示：小编为您整理了“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

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

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

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

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

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

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

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

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

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

----------

查看下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

----------

四，一个实际应用的例子的相关实验

创建一个分区表，只保留最近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

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技术专栏

上一篇：三步添加oracle主键自增长

下一篇：Oracle 卸载注册表残余文件清理

手机网站地址：Oracle分区之四：分区维护和管理

由于各方面情况的不断调整与变化，提供的所有考试信息和咨询回复仅供参考，敬请考生以权威部门公布的正式信息和咨询为准！