oracle12c分区表重命名,Oracle12c新特性：在线将非分区表转换为分区表及在线迁移...

一、在线将非分区表转换为分区表

前一篇文章提到Oracle11G可以使用在线重定义进行非分区表转换为分区表(Online Redefinition)，在Oracle 12C的新特性中，可在线将非分区表转换为分区表，语句如下：

ALTER TABLE table_name MODIFY table_partitioning_clauses

[ filter_condition ]

[ ONLINE ]

[ UPDATE INDEXES [ ( index { local_partitioned_index | global_partitioned_index | GLOBAL }

[, index { local_partitioned_index | global_partitioned_index | GLOBAL } ]... )

]

]

在线将非分区表转换为分区表，索引作为该操作的一部分被维护，也可以被分区。转换对正在进行的DML操作没有影响。

将非分区表的在线转换到分区表可以使任何应用程序在不使用应用程序停机的情况下采用分区。客户可以通过对任何系统进行分区，并根据需要对表进行演化，以从大型表的分区中获益。

限制：

不能被用来分割一个索引有序的表(索引组织表IOT)。

如果表有域索引，就不能使用它，只能在脱机模式下将表转换为reference-partitioned子表。

实验：

1、创建表并插入数据SQL> create table test ( timestamp date, id int , status VARCHAR2(7), CONSTRAINT test_pk PRIMARY KEY (id));

Table created.

2、插入数据时分配在三年中SQL> insert into test

select to_date('31-12-2004','dd-mm-yyyy')-mod(rownum,360), object_id, status

from all_objects where object_id <20000< span="">;

19257 rows created.

SQL> insert into test

select to_date('31-12-2005','dd-mm-yyyy')-mod(rownum,360), object_id, status

from all_objects where object_id between 20000 and 45000;

25001 rows created.

SQL> insert into test

select to_date('31-12-2006','dd-mm-yyyy')-mod(rownum,360), object_id,status

from all_objects where object_id >45000;

27766 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

COUNT(*)

----------

72024

3、创建索引SQL> CREATE INDEX idx_test ON test(timestamp);

Index created.

4、转换为分区表(12C后才可以使用)

我们可以使用ALTER TABLE ... MODIFY将表转换为分区表。

4.1 基本的离线操作。SQL> ALTER TABLE TEST MODIFY

PARTITION BY RANGE (timestamp)

(

PARTITION part_2004 VALUES LESS THAN

( to_date('01-jan-2005','dd-mon-yyyy') ) ,

PARTITION part_2005 VALUES LESS THAN

( to_date('01-jan-2006','dd-mon-yyyy') ),

PARTITION part_2006 VALUES LESS THAN

( to_date('01-jan-2007','dd-mon-yyyy') )

);

Table altered.

SQL> SELECT table_name, partition_name

FROM user_tab_partitions where TABLE_NAME ='TEST'

ORDER BY 1,2;

TABLE_NAME PARTITION_NAME

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

TEST PART_2004

TEST PART_2005

TEST PART_2006

SQL> select count(*) from TSET partition(PART_2004);

COUNT(*)

----------

19257

4.2在线分区表SQL> ALTER TABLE TEST MODIFY

PARTITION BY RANGE (timestamp)

(

PARTITION part_2004 VALUES LESS THAN

( to_date('01-jan-2005','dd-mon-yyyy') ) ,

PARTITION part_2005 VALUES LESS THAN

( to_date('01-jan-2006','dd-mon-yyyy') ),

PARTITION part_2006 VALUES LESS THAN

( to_date('01-jan-2007','dd-mon-yyyy') )

) online;

Table altered.

SQL> SELECT table_name, partition_name

FROM user_tab_partitions where TABLE_NAME ='TEST'

ORDER BY 1,2;

TABLE_NAME PARTITION_NAME

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

TEST PART_2004

TEST PART_2005

TEST PART_2006

4.3在线操作，修改索引分区SQL> ALTER TABLE TEST MODIFY

PARTITION BY RANGE (timestamp)

(

PARTITION part_2004 VALUES LESS THAN

( to_date('01-jan-2005','dd-mon-yyyy') ) ,

PARTITION part_2005 VALUES LESS THAN

( to_date('01-jan-2006','dd-mon-yyyy') ),

PARTITION part_2006 VALUES LESS THAN

( to_date('01-jan-2007','dd-mon-yyyy') )

) online

UPDATE INDEXES

(

test_pk GLOBAL,

idx_test LOCAL

);

Table altered.

在运行完成后，可以看到表和分区索引的新分区SQL> SELECT table_name, partition_name

FROM user_tab_partitions where TABLE_NAME ='TEST'

ORDER BY 1,2;

TABLE_NAME PARTITION_NAME

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

TEST PART_2004

TEST PART_2005

TEST PART_2006

SQL> SELECT index_name, partition_name, status

FROM user_ind_partitions where INDEX_NAME='IDX_TEST'

ORDER BY 1,2;

INDEX_NAME PARTITION_NAME STATUS

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

IDX_TEST PART_2004 USABLE

IDX_TEST PART_2005 USABLE

IDX_TEST PART_2006 USABLE

5、组合分区表

原始表也可以使用ALTER table进行组合分区…修改命令。SQL> ALTER TABLE TEST MODIFY

PARTITION BY RANGE (timestamp)

SUBPARTITION BY HASH (id)

(

PARTITION part_2004 VALUES LESS THAN

( to_date('01-jan-2005','dd-mon-yyyy') ) (

SUBPARTITION sub_part_2004_1,

SUBPARTITION sub_part_2004_2,

SUBPARTITION sub_part_2004_3,

SUBPARTITION sub_part_2004_4

),

PARTITION part_2005 VALUES LESS THAN

( to_date('01-jan-2006','dd-mon-yyyy') ) (

SUBPARTITION sub_part_2005_1,

SUBPARTITION sub_part_2005_2,

SUBPARTITION sub_part_2005_3,

SUBPARTITION sub_part_2005_4

),

PARTITION part_2006 VALUES LESS THAN

( to_date('01-jan-2007','dd-mon-yyyy') ) (

SUBPARTITION sub_part_2006_1,

SUBPARTITION sub_part_2006_2,

SUBPARTITION sub_part_2006_3,

SUBPARTITION sub_part_2006_4

)

) online

UPDATE INDEXES

(

test_pk GLOBAL,

idx_test LOCAL

);

Table altered.

可以使用以下查询显示表和分区索引的子分区。

SQL> SELECT table_name, partition_name, subpartition_name

FROM user_tab_subpartitions where TABLE_NAME ='TEST'

ORDER BY 1,2, 3;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME

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

TEST PART_2004 SUB_PART_2004_1

TEST PART_2004 SUB_PART_2004_2

TEST PART_2004 SUB_PART_2004_3

TEST PART_2004 SUB_PART_2004_4

TEST PART_2005 SUB_PART_2005_1

TEST PART_2005 SUB_PART_2005_2

TEST PART_2005 SUB_PART_2005_3

TEST PART_2005 SUB_PART_2005_4

TEST PART_2006 SUB_PART_2006_1

TEST PART_2006 SUB_PART_2006_2

TEST PART_2006 SUB_PART_2006_3

TEST PART_2006 SUB_PART_2006_4

12 rows selected.

SQL> SELECT index_name, partition_name, subpartition_name, status

FROM user_ind_subpartitions where INDEX_NAME='IDX_TEST'

ORDER BY 1,2;

INDEX_NAME      PARTITION_NAME      SUBPARTITION_NAME    STATUS

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

IDX_TEST PART_2004 SUB_PART_2004_1 USABLE

IDX_TEST PART_2004 SUB_PART_2004_2 USABLE

IDX_TEST PART_2004 SUB_PART_2004_3 USABLE

IDX_TEST PART_2004 SUB_PART_2004_4 USABLE

IDX_TEST PART_2005 SUB_PART_2005_1 USABLE

IDX_TEST PART_2005 SUB_PART_2005_2 USABLE

IDX_TEST PART_2005 SUB_PART_2005_3 USABLE

IDX_TEST PART_2005 SUB_PART_2005_4 USABLE

IDX_TEST PART_2006 SUB_PART_2006_1 USABLE

IDX_TEST PART_2006 SUB_PART_2006_2 USABLE

IDX_TEST PART_2006 SUB_PART_2006_3 USABLE

IDX_TEST PART_2006 SUB_PART_2006_4 USABLE

二、表分区或子分区的在线迁移

在Oracle 12c R1中迁移表分区或子分区到不同的表空间不再需要复杂的过程。与之前版本中未分区表进行在线迁移类似，表分区或子分区可以在线或是离线迁移至一个不同的表空间。当指定了ONLINE语句，在参与这一过程的分区或子分区上执行，所有的DML操作可以在没有任何中断的情况下。与此相反，分区或子分区迁移如果是在离线情况下进行的，DML操作是不被允许的。

1、在离线状况下将一个表分区或子分区迁移至一个新的表空间

SQL>ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;

2、在线迁移表分区或子分区并维护表上任何本地或全局的索引

SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;

重要提示：

参数 UPDATE INDEXES迁移表分区或子分区时维护表上本地或全局的索引。此外，当使ONLINE语句时，DML 操作是不会中断的。引入加锁机制来完成这一过程，当然它也会导致性能下降并会产生大量的redo，这取决于分区和子分区的大小。如果不带UPDATE INDEXES 参数，索引会失效，需要手工rebulid。