Oracle中将普通表转换为分区表

mahan

1.1 普通表转换成分区表有5种方法
1）exp/imp逻辑导入导出；
2）expdp/impdp逻辑导入导出；
3）insert插入数据；
4）partition exchange交换分区表；
5）dbms_redefinition在线重定义。
本文讲解在线重定义的方式。
通过在ALTER table SQL语句中添加MODIFY子句，可以将非分区表转换为分区表。
此外，可以指定关键字ONLINE，从而在转换过程中实现并发DML操作。
1.2 以下为转换语句
ALTER TABLE xxx MODIFY
  PARTITION BY RANGE (employee_id) INTERVAL (100)
  ( PARTITION P1 VALUES LESS THAN (100),
    PARTITION P2 VALUES LESS THAN (500)
   ) ONLINE
  UPDATE INDEXES
( IDX1_SALARY LOCAL,
   IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id)
  ( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
);

二

操作过程

建议在操作前做好数据备份。
2.1 备份表数据
数据泵导出表数据：
expdp user/pwd directory=EXP_TABLE table=xxx dumpfile=xxx.dmp logfile=xxx.log

2.2 表结构备份
--直接通过工具toad脚本直接导出；
--先备份表结构，防止出问题需要提前结束在线重定义导致主键约束消失。
set long 9999
select dbms_metadata.get_ddl('TABLE','表名','用户') from dual;

2.3 检查是否有无效对象
select OWNER,index_name,table_name,status from dba_indexes a
where a.status not in('VALID','N/A') and a.table_name='xxx';

2.4 检查表是否可以在线重定义
SQL> exec dbms_redefinition.can_redef_table('用户名','表名');
PL/SQL procedure successfully completed.
通过

2.5 在线重定义为分区表
ALTER TABLE owner.table_name
    MODIFY
        --PARTITION BY range (CHANGE_TIME)
        PARTITION BY RANGE (CREATED_ON)
            INTERVAL ( NUMTOYMINTERVAL (1, 'year') ) --interval(numtoyminterval(1,'month'))
        (
            PARTITION p2019
                VALUES LESS THAN (TO_DATE ('2020-01-01', 'YYYY-MM-DD')),
            PARTITION p2020
                VALUES LESS THAN (TO_DATE ('2021-01-01', 'YYYY-MM-DD')),
            PARTITION p2021
                VALUES LESS THAN (TO_DATE ('2022-01-01', 'YYYY-MM-DD')),
            PARTITION p2022
                VALUES LESS THAN (TO_DATE ('2023-01-01', 'YYYY-MM-DD')),
            PARTITION p2023
                VALUES LESS THAN (TO_DATE ('2024-01-01', 'YYYY-MM-DD')),
            PARTITION p2024
                VALUES LESS THAN (TO_DATE ('2025-01-01', 'YYYY-MM-DD'))
                                                                       )
        ONLINE
        UPDATE INDEXES; --此处如果没有指定索引为本地分区索引，在表分区完成后要进行索引重建

2.6 收集新表统计信息（按需操作）
begin
dbms_stats.gather_table_stats(ownname => xxx,
tabname => 'xxx',
estimate_percent => 100,
method_opt => 'for all indexed columns',
cascade => true,
degree => 2);
end;
/

2.7 重建分区索引
Create index IDX_01 on xxx(work_time) local;

2.8 检查是否有无效对象
select OWNER,index_name,table_name,status from dba_indexes a
where a.status not in('VALID','N/A') and a.table_name='xxx';

2.9 检查分区及分区数据量
Select Table_Name,
Partition_Name,
High_Value,
Partition_Position,
Tablespace_Name,
Num_Rows,
last_analyzed
From dba_Tab_Partitions
where TABLE_NAME = 'xxx';

select partition_name,num_rows from user_tab_partitions where table_name=’xxx’;