使用DBMS_REDEFINITION在线切换普通表到分区表

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然

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

有关具体的dbms_redefinition在线重定义表的原理及步骤可参考：基于 dbms_redefinition 在线重定义表

有关分区表的描述请参考：Oracle 分区表

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

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

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

1、准备环境

--创建用户
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 .. 1000000 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);

--表上的索引相关信息
SQL> @idx_info
TABLE_NAME INDEX_NAME CL_NAM STATUS PAR
------------------------- ------------------------------ -------------------- -------- ---
LOOKUP LOOKUP_PK ID VALID NO
BIG_TABLE BIG_TABLE_PK ID VALID NO
BIG_TABLE BITA_CREATED_DATE_I CREATED_DATE VALID NO
BIG_TABLE BITA_LOOK_FK_I LOOKUP_ID VALID NO