oracle三种分区的方式,Oracle 分区表 总结大全(3)

(10) Horizontal sub setting of data cannot be performed during the redefinition.

在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：

Bug 7007594 - ORA-600 [12261]

http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

在线重定义的大致操作流程如下：

(1)创建基础表A，如果存在，就不需要操作。

(2)创建临时的分区表B。

(3)开始重定义，将基表A的数据导入临时分区表B。

(4)结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

下面看一个示例：

1. 创建基本表和索引

sql> conn icd/icd;

已连接。

sql> create table unpar_table (

2 id number(10) primary key,

3 create_date date

4 );

表已创建。

sql> insert into unpar_table select rownum, created from dba_objects;

已创建72288行。

sql> create index create_date_ind on unpar_table(create_date);

索引已创建。

sql> commit;

提交完成。

2. 收集表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);

pl/sql 过程已成功完成。

3. 创建临时分区表

sql> create table par_table (id number primary key, time date) partition by range (time)

2 (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')),

3 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')),

4 partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')),

5 partition p4 values less than (maxvalue));

表已创建。

4. 进行重定义操作

4.1 检查重定义的合理性

sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');

pl/sql 过程已成功完成。

4.2 如果4.1 没有问题，开始重定义，这个过程可能要等一会。

这里要注意：如果分区表和原表列名相同，可以用如下方式进行：

SQL> BEGIN

DBMS_REDEFINITION.start_redef_table(

uname => 'ICD',

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定映射关系：

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(

'ICD',

'unpar_table',

'par_table',

'ID ID, create_date TIME', -- 在这里指定新的映射关系

DBMS_REDEFINITION.CONS_USE_PK);

这一步操作结束后，数据就已经同步到这个临时的分区表里来了。

4.3 同步新表，这是可选的操作

SQL> BEGIN

2 dbms_redefinition.sync_interim_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 过程已成功完成。

4.4 创建索引，在线重定义只重定义数据，索引还需要单独建立。

sql> create index create_date_ind2 on par_table(time);

索引已创建。

4.5 收集新表的统计信息

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

pl/sql 过程已成功完成。

4.6 结束重定义

SQL> BEGIN

2 dbms_redefinition.finish_redef_table(

3 uname => 'ICD',

4 orig_table => 'unpar_table',

5 int_table => 'par_table');

6 END;

7 /

PL/SQL 过程已成功完成。

结束重定义的意义：

基表unpar_table 和临时分区表par_table 进行了交换。 此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。

我们在重定义的时候，基表unpar_table是可以进行DML操作的。 只有在2个表进行切换的时候会有短暂的锁表。

5. 删除临时表

SQL> DROP TABLE par_table;

表已删除。

6. 索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

索引已更改。

7. 验证

sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE';

par

---

yes

sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE';

partition_name

------------------------------

p1

p2

p3

p4

sql> select count(*) from unpar_table;

count(*)

----------

72288

sql> select count(*) from unpar_table partition (p4);

count(*)

----------

72288

sql>

三. 分区表的其他操作

3.1 添加新的分区

添加新的分区有2中情况：

(1)原分区里边界是maxvalue或者default。 这种情况下，我们需要把边界分区drop掉，加上新分区后，在添加上新的分区。 或者采用split，对边界分区进行拆分。

(2)没有边界分区的。 这种情况下，直接添加分区就可以了。

以边界分区添加新分区示例：

(1)分区表和索引的信息如下：

SQL> create table custaddr

2 (

3 id varchar2(15 byte) not null,

4 areacode varchar2(4 byte)

5 )

6 partition by list (areacode)

7 (

8 partition t_list556 values ('556') tablespace icd_service,

9 partition p_other values (default)tablespace icd_service

10 );

表已创建。

SQL> create index ix_custaddr_id on custaddr(id)

2 local (

3 partition t_list556 tablespace icd_service,

4 partition p_other tablespace icd_service

5 );

索引已创建。

(2)插入几条测试数据：

SQL> insert into custaddr values('1','556');

已创建 1 行。

SQL> insert into custaddr values('2','551');

已创建 1 行。

SQL> insert into custaddr values('3','555');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from custaddr;

ID AREA

--------------- ----

1 556

2 551

3 555

SQL> select * from custaddr partition(t_list556);

ID AREA

--------------- ----

1 556

SQL>

(3)删除default分区

sql> alter table custaddr drop partition p_other;

表已更改。

sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

table_name partition_name

------------------------------ ------------------------------

custaddr t_list556

(4)添加新分区

SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

CUSTADDR T_LIST556

CUSTADDR T_LIST551

(5)添加default 分区

SQL> alter table custaddr add partition p_other values (default) tablespace icd_service;

表已更改。

SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR';

TABLE_NAME PARTITION_NAME

------------------------------ ------------------------------

CUSTADDR T_LIST556

CUSTADDR T_LIST551

CUSTADDR P_OTHER

(6)对于局部索引，oracle会自动增加一个局部分区索引。验证一下：

sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id';

owner index_name table_name

---------------------- ------------------------------ ------------------

icd ix_custaddr_id custaddr

sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id';

index_owner index_name partition_name

------------------------------ ------------------------------ ------------------

icd ix_custaddr_id p_other

icd ix_custaddr_id t_list551

icd ix_custaddr_id t_list556

分区索引自动创建了。

3.2 split 分区拆分

在3.1 中，我们说明了可以使用split的方式来添加分区。 这里我们用split方法继续上面的实验。

sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);