Oracle普通表修改为分区表的方法

DBA小记 2020-10-27

3711

一、主要方法

1.先创建空的分区表，然后insert原表中的数据进去；

2.CTAS方法；

3.使用Oracle的在线重定义工具。

二、执行方法比较

2.1 创建空的分区表，然后insert原表中数据

创建原表及数据插入

SQL> create table t_table ( timestamp date, id int );
Table created.
SQL> insert into t_table
select to_date('31-12-2004','dd-mm-yyyy')-mod(rownum,360), object_id
from all_objects;
72011 rows created.
SQL> insert into t_table
select to_date('31-12-2005','dd-mm-yyyy')-mod(rownum,360), object_id
from all_objects;
72011 rows created.
SQL> insert into t_table
select to_date('31-12-2006','dd-mm-yyyy')-mod(rownum,360), object_id
from all_objects;
72011 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t_table;
  COUNT(*)
----------
    216033

注：任何方法操作前都要做好原表备份。

新建空分区表，insert原表数据

SQL> CREATE TABLE part_table
( timestamp date,
id int
)
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 created.

插入分区表数据：

SQL> insert into part_table select * from t_table;
216033 rows created.
SQL> commit;
Commit complete.

表重命名：

SQL> rename t_table to t_table_old;
SQL> rename part_table to t_table;

验证分区及分区数据：

SQL> select t1.partition_name from DBA_TAB_PARTITIONS t1 where t1.table_name = 'T_TABLE';
PARTITION_NAME
------------------------------
PART_2004
PART_2005
PART_2006
SQL> select count(*) from T_TABLE partition(PART_2004);
  COUNT(*)
----------
     72011
SQL> select count(*) from T_TABLE partition(PART_2005);
  COUNT(*)
----------
     72011
SQL> select count(*) from T_TABLE partition(PART_2006);
  COUNT(*)
----------
     72011

这个方法是最简单易学的一种操作方法,对于小表,而且是静止状态（数据无操作，最好停机状态）下的表，可以进行。

注意：表中索引影响数据库的SQL执行，对于分区表要考虑局部索引和全局索引的使用。

2.2 CTAS方法

还是使用原来的表，重新还原表名进行操作。

SQL> drop table t_table purge;
SQL> rename t_table_old to t_table;

CTAS方法使用

SQL> CREATE TABLE part_table
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') )
)
as select * from t_table;

验证分区表：

SQL> select t1.partition_name from DBA_TAB_PARTITIONS t1 where t1.table_name = 'PART_TABLE';
SQL> select count(*) from PART_TABLE partition(PART_2004);
  COUNT(*)
----------
     72011
SQL> select count(*) from PART_TABLE partition(PART_2005);
  COUNT(*)
----------
     72011
SQL> select count(*) from PART_TABLE partition(PART_2006);
  COUNT(*)
----------
     72011

表重命名：

SQL> rename t_table to t_table_old;
SQL> rename part_table to t_table;

此方法适用于修改不频繁的表，在闲时进行操作，表的数据量较大操作效率也尚可。

优点：方法简单易用，由于采用DDL语句，很少会使用undo表空间和临时表空间，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。

缺点：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

2.3 在线重定义

Oracle从9i版本开始提供了在线重定义表功能，通过调用DBMS_REDEFINITION包，可以在修改表结构的同时允许DML操作。

SQL> desc DBMS_REDEFINITION
PROCEDURE ABORT_REDEF_TABLE
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 PART_NAME                  VARCHAR2            IN     DEFAULT
PROCEDURE CAN_REDEF_TABLE
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 TNAME                        VARCHAR2            IN
 OPTIONS_FLAG                  BINARY_INTEGER            IN     DEFAULT
 PART_NAME                  VARCHAR2            IN     DEFAULT
PROCEDURE COPY_TABLE_DEPENDENTS
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 COPY_INDEXES                  BINARY_INTEGER            IN     DEFAULT
 COPY_TRIGGERS                  BOOLEAN             IN     DEFAULT
 COPY_CONSTRAINTS            BOOLEAN             IN     DEFAULT
 COPY_PRIVILEGES            BOOLEAN             IN     DEFAULT
 IGNORE_ERRORS                  BOOLEAN             IN     DEFAULT
 NUM_ERRORS                  BINARY_INTEGER            OUT
 COPY_STATISTICS            BOOLEAN             IN     DEFAULT
 COPY_MVLOG                  BOOLEAN             IN     DEFAULT
PROCEDURE FINISH_REDEF_TABLE
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 PART_NAME                  VARCHAR2            IN     DEFAULT
PROCEDURE REGISTER_DEPENDENT_OBJECT
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 DEP_TYPE                  BINARY_INTEGER            IN
 DEP_OWNER                  VARCHAR2            IN
 DEP_ORIG_NAME                  VARCHAR2            IN
 DEP_INT_NAME                  VARCHAR2            IN
PROCEDURE START_REDEF_TABLE
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 COL_MAPPING                  VARCHAR2            IN     DEFAULT
 OPTIONS_FLAG                  BINARY_INTEGER            IN     DEFAULT
 ORDERBY_COLS                  VARCHAR2            IN     DEFAULT
 PART_NAME                  VARCHAR2            IN     DEFAULT
PROCEDURE SYNC_INTERIM_TABLE
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 PART_NAME                  VARCHAR2            IN     DEFAULT
PROCEDURE UNREGISTER_DEPENDENT_OBJECT
 Argument Name                  Type                  In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                        VARCHAR2            IN
 ORIG_TABLE                  VARCHAR2            IN
 INT_TABLE                  VARCHAR2            IN
 DEP_TYPE                  BINARY_INTEGER            IN
 DEP_OWNER                  VARCHAR2            IN
 DEP_ORIG_NAME                  VARCHAR2            IN
 DEP_INT_NAME                  VARCHAR2            IN

在线重定义表具有以下功能

（1）修改表的存储参数；

（2）可以将表转移到其他表空间；

（3）增加并行查询选项；

（4）增加或删除分区；

（5）重建表以减少碎片；

（6）将堆表改为索引组织表或相反的操作；

（7）增加或删除一个列。

调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。

在线重定义表的步骤说明

1.选择一种重定义方法：

存在两种重定义方法，一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。

2.调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程，如果表不满足重定义的条件，将会报错并给出原因。

3.在用一个方案中建立一个空的中间表，根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等。

4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程，并提供下列参数：被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。

如果映射方法没有提供，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列。如果没有给出重定义方法，则认为使用主键方式。

5.在中间表上建立触发器、索引和约束，并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。

当重定义完成时，中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。

6.（可选）如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

7.执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关。执行完FINISH_REDEF_TABLE()过程后，原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。

8.（可选）可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。

执行在线重定义

2.3.1 模拟环境

SQL> create table t_table ( timestamp date, id int , status VARCHAR2(7));
Table created.
SQL> insert into t_table
select to_date('31-12-2004','dd-mm-yyyy')-mod(rownum,360), object_id, status
from all_objects;
72011 rows created.
SQL> insert into t_table
select to_date('31-12-2005','dd-mm-yyyy')-mod(rownum,360), object_id, status
from all_objects;
72011 rows created.
SQL> insert into t_table
select to_date('31-12-2006','dd-mm-yyyy')-mod(rownum,360), object_id,status
from all_objects;
72011 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t_table;
  COUNT(*)
----------
    216033

2.3.2 操作步骤

step 1：

对在线重定义的表自行验证，看该表是否可以重定义，如果不可以则会提示错误信息

SQL> execute dbms_redefinition.can_redef_table('TEST','T_TABLE');
BEGIN dbms_redefinition.can_redef_table('TEST','T_TABLE'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "TEST"."T_TABLE" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627
ORA-06512: at line 1

提示表没有主键，无法在线重定义。需要指定以rowid重定义，示例如下：

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('test','t_table,'part_table',null,2);  
PL/SQL procedure successfully completed.

我们使用有主键方式，建立主键及索引：

SQL> alter table t_table add constraint pk_t_table primary key (timestamp,id);
Table altered.
SQL> create index idx_t_table on t_table(timestamp,status);
Index created.

再次验证：

SQL> execute dbms_redefinition.can_redef_table('TEST','T_TABLE');
PL/SQL procedure successfully completed.

step 2：

建立和源表一样的分区表。

SQL> CREATE TABLE part_table
( timestamp date,
id int,
status varchar2(7)
)
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 created.

注：

开始重定义之前在临时表上创建local索引，重定义完成后，主键对应的索引也是分区索引;

SQL> alter table PART_TABLE add constraint PK_PART_TABLE primary key (timestamp, id) using index local;
Table altered.
SQL> create index IDX_PART_TABLE on PART_TABLE (timestamp,status) local;
Index created.

step 3：

执行表的在线重定义；

SQL> execute dbms_redefinition.start_redef_table('TEST','T_TABLE','PART_TABLE');
PL/SQL procedure successfully completed.

现两个中间表RUPD$_T_TABLE和MLOG$_T_TABLE。

SQL> select * from tab where tname like '%TABLE';       




TNAME                         TABTYPE      CLUSTERID
------------------------------ ------- ----------
T_TABLE                    TABLE
RUPD$_T_TABLE                   TABLE
PART_TABLE                   TABLE
MLOG$_T_TABLE                   TABLE

ABORT_REDEF_TABLE使用

在FINISH_REDEF_TABLE之前，可以使用abort_redef_table停止重定义

SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE ('TEST','T_TABLE','PART_TABLE');
 PL/SQL procedure successfully completed.

step 4：

执行中间表和数据源表的数据同步。

SQL> execute dbms_redefinition.sync_interim_table('TEST','T_TABLE','PART_TABLE');
PL/SQL procedure successfully completed.

step 5：

执行结束在线定义过程。

SQL> execute dbms_redefinition.finish_redef_table('TEST','T_TABLE','PART_TABLE');
PL/SQL procedure successfully completed.

此时中间表消失。

SQL> select * from tab where tname like '%TABLE';
TNAME                         TABTYPE      CLUSTERID
------------------------------ ------- ----------
T_TABLE                    TABLE
PART_TABLE                   TABLE

step 6：

验证

SQL> select count(*) from t_table;
  COUNT(*)
----------
    216033
SQL> select count(*) from t_table partition(part_2004);
  COUNT(*)
----------
     72011
SQL> select t1.partition_name from DBA_TAB_PARTITIONS t1 where t1.table_name = 'T_TABLE';
PARTITION_NAME
------------------------------
PART_2004
PART_2005
PART_2006
SQL> select t1.partition_name from DBA_TAB_PARTITIONS t1 where t1.table_name = 'PART_TABLE';
no rows selected
SQL> select count(*) from part_table;
  COUNT(*)
----------
    216033

T_TABLE表已经转换成了分区表。

step 7：

删除临时表，必要情况下当时删除。

SQL> drop table part_table purge;

step 8：

如果修改索引、约束名称和原表一致（在删除临时表后才可以操作）

SQL> alter index IDX_PART_TABLE rename to IDX_T_TABLE;
SQL> alter index PK_PART_TABLE rename to PK_T_TABLE;
SQL> alter table t_table rename constraint PK_PART_TABLE to PK_T_TABLE;

2.3.3 报错

SQL> exec dbms_redefinition.can_redef_table('TEST','T_TABLE');
BEGIN dbms_redefinition.can_redef_table('TEST','T_TABLE'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "TEST"."T_TABLE" with
materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: at line 1

用这句删除materialized view 即可继续进行

drop materialized view log on;
drop materialized view log on t;
    OR drop materialized t;

2.3.4 拷贝表的属性

下边这样转换的索引不是分区索引，在开始重定义后执行。

DECLARE
 error_count pls_integer := 0;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
        uname => 'test',
        orig_table => 't_table',
        int_table => 'part_table',
        ignore_errors => TRUE,
        num_errors => error_count);
    DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

oracle

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

Oracle普通表修改为分区表的方法

评论