Oracle12c新特性：在线将非分区表转换为分区表及在线迁移

DBA小记 2020-10-27

1653

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

前一篇文章提到Oracle 11G可以使用在线重定义进行非分区表转换为分区表（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。

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle12c新特性：在线将非分区表转换为分区表及在线迁移

评论