使用exchange方式切换普通表到分区表

曾经终败给现在 2022-08-24 04:19 107阅读 0赞

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS\_REDEFINITION来在线重定义分区表。本文描述的是使用EXCHANGE PARTITION方式来实现，下面是具体的操作示例。

有关具体的dbms\_redefinition在线重定义表的原理及步骤可参考：[基于 dbms\_redefinition 在线重定义表][dbms_redefinition]  
      有关使用DBMS\_REDEFINITION在线重定义分区表可参考：[使用DBMS\_REDEFINITION在线切换普通表到分区表  
][DBMS_REDEFINITION_]      有关分区表的描述请参考：[Oracle 分区表][Oracle]

1、主要步骤  
    a、为新的分区表准备相应的表空间  
    b、基于源表元数据创建分区表以及相关索引、约束等  
    c、使用exchange方式将普通表切换为分区表  
    d、更正相关索引及约束名等(可省略)  
    e、使用split根据需要将分区表分割为多个不同的分区  
    f、收集统计信息

2、准备环境

--创建用户
    SQL> create user leshami identified by xxx;
    
    SQL> grant dba to leshami;
    
    --创建演示需要用到的表空间
    SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;
    
    SQL> alter user leshami default tablespace tbs_tmp;
    
    SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;
    
    SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;
    
    SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;
    
    SQL> conn leshami/xxx
    
    -- 创建一个lookup表
    CREATE TABLE lookup (
      id            NUMBER(10),
      description   VARCHAR2(50)
    );
    
    --添加主键约束
    ALTER TABLE lookup ADD (
      CONSTRAINT lookup_pk PRIMARY KEY (id)
    );
    
    --插入数据
    INSERT INTO lookup (id, description) VALUES (1, 'ONE');
    INSERT INTO lookup (id, description) VALUES (2, 'TWO');
    INSERT INTO lookup (id, description) VALUES (3, 'THREE');
    COMMIT;
    
    --创建一个用于切换到分区的大表
    CREATE TABLE big_table (
      id            NUMBER(10),
      created_date  DATE,
      lookup_id     NUMBER(10),
      data          VARCHAR2(50)
    );
    
    --填充数据到大表
    DECLARE
      l_lookup_id    lookup.id%TYPE;
      l_create_date  DATE;
    BEGIN
      FOR i IN 1 .. 10000 LOOP
        IF MOD(i, 3) = 0 THEN
          l_create_date := ADD_MONTHS(SYSDATE, -24);
          l_lookup_id   := 2;
        ELSIF MOD(i, 2) = 0 THEN
          l_create_date := ADD_MONTHS(SYSDATE, -12);
          l_lookup_id   := 1;
        ELSE
          l_create_date := SYSDATE;
          l_lookup_id   := 3;
        END IF;
        
        INSERT INTO big_table (id, created_date, lookup_id, data)
        VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
      END LOOP;
      COMMIT;
    END;
    /
    
    --为大表添加主、外键约束，索引，以及添加触发器等.
    ALTER TABLE big_table ADD (
      CONSTRAINT big_table_pk PRIMARY KEY (id)
    );
    
    CREATE INDEX bita_created_date_i ON big_table(created_date);
    
    CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
    
    ALTER TABLE big_table ADD (
      CONSTRAINT bita_look_fk
      FOREIGN KEY (lookup_id)
      REFERENCES lookup(id)
    );
    
    CREATE OR REPLACE TRIGGER tr_bf_big_table
       BEFORE UPDATE OF created_date
       ON big_table
       FOR EACH ROW
    BEGIN
       :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');
    END tr_bf_big_table;
    /
    
    --收集统计信息
    EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);
    EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);

3、创建分区表

CREATE TABLE big_table2 (   
      id            NUMBER(10),   
      created_date  DATE,   
      lookup_id     NUMBER(10),   
      data          VARCHAR2(50)   
    )   
    PARTITION BY RANGE (created_date)   
    (PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);  
    
    ALTER TABLE big_table2 ADD (
      CONSTRAINT big_table_pk2 PRIMARY KEY (id)
    );
    
    CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
    
    CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
    
    ALTER TABLE big_table2 ADD (
      CONSTRAINT bita_look_fk2
      FOREIGN KEY (lookup_id)
      REFERENCES lookup(id)
    );
    
    --触发器也需要单独添加到分区表
    CREATE OR REPLACE TRIGGER tr_bf_big_table2              --Author: Leshami
       BEFORE UPDATE OF created_date                        --Blog   : http://blog.csdn.net/leshami
       ON big_table2
       FOR EACH ROW
    BEGIN
       :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');
    END tr_bf_big_table2;
    /

4、使用exchange切换为分区表

--下面的这个命令就是通过exchange方式来直接将普通表来切换为分区表
    ALTER TABLE big_table2
      EXCHANGE PARTITION big_table_2014
      WITH TABLE big_table
      WITHOUT VALIDATION
      UPDATE GLOBAL INDEXES;
      
    SQL> select count(*) from big_table2;
    
      COUNT(*)
    ----------
         10000
    
    DROP TABLE big_table;
    RENAME big_table2 TO big_table;
    
    ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
    ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
    ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
    ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
    ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
    ALTER TRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;

5、使用split方式分割分区表

ALTER TABLE big_table
      SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
      INTO (PARTITION big_table_2012 tablespace tbs1 ,
            PARTITION big_table_2014)
      UPDATE GLOBAL INDEXES;
    
    ALTER TABLE big_table
      SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
      INTO (PARTITION big_table_2013 tablespace tbs2,
            PARTITION big_table_2014)
      UPDATE GLOBAL INDEXES;
    
    --收集统计信息，如果表很大的话，需要考虑使用并行度，采样值
    --对于上述的操作中，本地分区索引和数据存储在指定的表空间，存在混用情形；对于全局索引则保存在缺省表空间，
    --上面提到的2种情形，可以根据需要作相应调整
    EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);
    
    --验证结果
    SQL> col HIGH_VALUE format a45 wrapped
    SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions
      2  where table_name='BIG_TABLE';
    
    TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                      NUM_ROWS
    --------------- -------------------- --------------------------------------------- ----------
    BIG_TABLE       BIG_TABLE_2012       TO_DATE(' 2012-12-31 23:59:59', 'SYYYY-MM-DD        3333
                                         HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    
    BIG_TABLE       BIG_TABLE_2013       TO_DATE(' 2013-12-31 23:59:59', 'SYYYY-MM-DD        3334
                                         HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    
    BIG_TABLE       BIG_TABLE_2014       MAXVALUE                                            3333

本文参考：[Partitioning an Existing Table using EXCHANGE PARTITION  
][Partitioning an Existing Table using EXCHANGE PARTITION]

[![Oracle&nbsp;牛鹏社][Oracle_nbsp]][Oracle_nbsp_Oracle_nbsp]    [![Oracle DBsupport][Oracle_nbsp]][Oracle DBsupport_Oracle_nbsp]

更多参考

有关Oracle RAC请参考  
     [使用crs\_setperm修改RAC资源的所有者及权限  
][crs_setperm_RAC_]     [使用crs\_profile管理RAC资源配置文件  
][crs_profile_RAC_]     [RAC 数据库的启动与关闭  
][RAC _]     [再说 Oracle RAC services  
][Oracle RAC services]     [Services in Oracle Database 10g  
][Services in Oracle Database 10g]     [Migrate datbase from single instance to Oracle RAC  
][Migrate datbase from single instance to Oracle RAC]     [Oracle RAC 连接到指定实例  
][Oracle RAC _]     [Oracle RAC 负载均衡测试(结合服务器端与客户端)  
][Oracle RAC _ 1]     [Oracle RAC 服务器端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_]     [Oracle RAC 客户端连接负载均衡(Load Balance)  
][Oracle RAC _Load Balance_ 1]     [ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)][ORACLE RAC _listener.ora tnsnames.ora]   
     [ORACLE RAC 监听配置 (listener.ora tnsnames.ora)   
][ORACLE RAC _ _listener.ora tnsnames.ora_]     [配置 RAC 负载均衡与故障转移  
][RAC _ 1]     [CRS-1006 , CRS-0215 故障一例][CRS-1006 _ CRS-0215]   
     [基于Linux (RHEL 5.5) 安装Oracle 10g RAC][Linux _RHEL 5.5_ _Oracle 10g RAC]   
     [使用 runcluvfy 校验Oracle RAC安装环境][runcluvfy _Oracle RAC]

有关Oracle 网络配置相关基础以及概念性的问题请参考：  
     [配置非默认端口的动态服务注册][Link 1]   
     [配置sqlnet.ora限制IP访问Oracle   
][sqlnet.ora_IP_Oracle_]     [Oracle 监听器日志配置与管理][Oracle 1]   
     [设置 Oracle 监听器密码(LISTENER)  
][Oracle _LISTENER_]     [配置ORACLE 客户端连接到数据库][ORACLE]

有关基于用户管理的备份和备份恢复的概念请参考  
     [Oracle 冷备份  
][Oracle _]     [Oracle 热备份  
][Oracle _ 1]     [Oracle 备份恢复概念  
][Oracle _ 2]     [Oracle 实例恢复  
][Oracle _ 3]     [Oracle 基于用户管理恢复的处理  
][Oracle _ 4]     [SYSTEM 表空间管理及备份恢复  
][SYSTEM _]     [SYSAUX表空间管理及恢复  
][SYSAUX_]     [Oracle 基于备份控制文件的恢复(unsing backup controlfile)][Oracle _unsing backup controlfile]

有关RMAN的备份恢复与管理请参考  
     [RMAN 概述及其体系结构  
][RMAN _]     [RMAN 配置、监控与管理  
][RMAN _ 1]     [RMAN 备份详解  
][RMAN _ 2]     [RMAN 还原与恢复  
][RMAN _ 3]     [RMAN catalog 的创建和使用  
][RMAN catalog _]     [基于catalog 创建RMAN存储脚本  
][catalog _RMAN_]     [基于catalog 的RMAN 备份与恢复  
][catalog _RMAN _]     [RMAN 备份路径困惑  
][RMAN _ 4]     [使用RMAN实现异机备份恢复(WIN平台)  
][RMAN_WIN_]     [使用RMAN迁移文件系统数据库到ASM  
][RMAN_ASM]     [linux 下RMAN备份shell脚本  
][linux _RMAN_shell_]     [使用RMAN迁移数据库到异机][RMAN]

有关ORACLE体系结构请参考  
     [Oracle 表空间与数据文件  
][Oracle _ 5]     [Oracle 密码文件  
][Oracle _ 6]     [Oracle 参数文件  
][Oracle _ 7]     [Oracle 联机重做日志文件(ONLINE LOG FILE)  
][Oracle _ONLINE LOG FILE_]     [Oracle 控制文件(CONTROLFILE)  
][Oracle _CONTROLFILE_]     [Oracle 归档日志  
][Oracle _ 8]     [Oracle 回滚(ROLLBACK)和撤销(UNDO)  
][Oracle _ROLLBACK_UNDO_]     [Oracle 数据库实例启动关闭过程  
][Oracle _ 9]     [Oracle 10g SGA 的自动化管理  
][Oracle 10g SGA _]     [Oracle 实例和Oracle数据库(Oracle体系结构)][Oracle _Oracle_Oracle]

[dbms_redefinition]: http://blog.csdn.net/leshami/article/details/11760417
[DBMS_REDEFINITION_]: http://blog.csdn.net/leshami/article/details/30041813
[Oracle]: http://blog.csdn.net/leshami/article/details/5925572
[Partitioning an Existing Table using EXCHANGE PARTITION]: http://www.oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition.php
[Oracle_nbsp]: http://pub.idqqimg.com/wpa/images/group.png
[Oracle_nbsp_Oracle_nbsp]: http://wp.qq.com/wpa/qunwpa?idkey=63ed41c9a94b48ebc4d3d956537065ba6d18a649c353b09bfdac284518b7b8d8
[Oracle DBsupport_Oracle_nbsp]: /images/20220823/a5f0f65e1c44475ebbe292d862517aca.png
[crs_setperm_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8219242
[crs_profile_RAC_]: http://blog.csdn.net/robinson_0612/article/details/8192571
[RAC _]: http://blog.csdn.net/robinson_0612/article/details/8129506
[Oracle RAC services]: http://blog.csdn.net/robinson_0612/article/details/8124232
[Services in Oracle Database 10g]: http://blog.csdn.net/robinson_0612/article/details/8089812
[Migrate datbase from single instance to Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/8090095
[Oracle RAC _]: http://blog.csdn.net/robinson_0612/article/details/8073620
[Oracle RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/8072367
[Oracle RAC _Load Balance_]: http://blog.csdn.net/robinson_0612/article/details/8064599
[Oracle RAC _Load Balance_ 1]: http://blog.csdn.net/robinson_0612/article/details/8060634
[ORACLE RAC _listener.ora tnsnames.ora]: http://blog.csdn.net/robinson_0612/article/details/8053007
[ORACLE RAC _ _listener.ora tnsnames.ora_]: http://blog.csdn.net/robinson_0612/article/details/8020800
[RAC _ 1]: http://blog.csdn.net/robinson_0612/article/details/6791667
[CRS-1006 _ CRS-0215]: http://blog.csdn.net/robinson_0612/article/details/7926241
[Linux _RHEL 5.5_ _Oracle 10g RAC]: http://blog.csdn.net/robinson_0612/article/details/7906282
[runcluvfy _Oracle RAC]: http://blog.csdn.net/robinson_0612/article/details/6533094
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/5925526
[sqlnet.ora_IP_Oracle_]: http://blog.csdn.net/robinson_0612/article/details/6629141
[Oracle 1]: http://blog.csdn.net/robinson_0612/article/details/6629043
[Oracle _LISTENER_]: http://blog.csdn.net/robinson_0612/article/details/6629249
[ORACLE]: http://blog.csdn.net/robinson_0612/article/details/5917103
[Oracle _]: http://blog.csdn.net/robinson_0612/article/details/5791597
[Oracle _ 1]: http://blog.csdn.net/robinson_0612/article/details/5816012
[Oracle _ 2]: http://blog.csdn.net/robinson_0612/article/details/5791585
[Oracle _ 3]: http://blog.csdn.net/robinson_0612/article/details/5768233
[Oracle _ 4]: http://blog.csdn.net/robinson_0612/article/details/6041217
[SYSTEM _]: http://blog.csdn.net/robinson_0612/article/details/6041207
[SYSAUX_]: http://blog.csdn.net/robinson_0612/article/details/5810732
[Oracle _unsing backup controlfile]: http://blog.csdn.net/robinson_0612/article/details/7962187
[RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6032525
[RMAN _ 1]: http://blog.csdn.net/robinson_0612/article/details/6032597
[RMAN _ 2]: http://blog.csdn.net/robinson_0612/article/details/6032739
[RMAN _ 3]: http://blog.csdn.net/robinson_0612/article/details/6041247
[RMAN catalog _]: http://blog.csdn.net/robinson_0612/article/details/6043649
[catalog _RMAN_]: http://blog.csdn.net/robinson_0612/article/details/6073020
[catalog _RMAN _]: http://blog.csdn.net/robinson_0612/article/details/6073069
[RMAN _ 4]: http://blog.csdn.net/robinson_0612/article/details/6414338
[RMAN_WIN_]: http://blog.csdn.net/robinson_0612/article/details/6639064
[RMAN_ASM]: http://blog.csdn.net/robinson_0612/article/details/6453954
[linux _RMAN_shell_]: http://blog.csdn.net/robinson_0612/article/details/8029245
[RMAN]: http://blog.csdn.net/robinson_0612/article/details/8076841
[Oracle _ 5]: http://blog.csdn.net/robinson_0612/article/details/5611738
[Oracle _ 6]: http://blog.csdn.net/robinson_0612/article/details/5611672
[Oracle _ 7]: http://blog.csdn.net/robinson_0612/article/details/5559174
[Oracle _ONLINE LOG FILE_]: http://blog.csdn.net/robinson_0612/article/details/5749556
[Oracle _CONTROLFILE_]: http://blog.csdn.net/robinson_0612/article/details/5737527
[Oracle _ 8]: http://blog.csdn.net/robinson_0612/article/details/5784713
[Oracle _ROLLBACK_UNDO_]: http://blog.csdn.net/robinson_0612/article/details/5731158
[Oracle _ 9]: http://blog.csdn.net/robinson_0612/article/details/5542983
[Oracle 10g SGA _]: http://blog.csdn.net/robinson_0612/article/details/5534832
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/article/details/5529239