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

随着数据库数据量的不断增长，有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描述的是使用DBMS_REDEFINITION来实现，下面是具体的操作示例。有关具体的dbms_redefinition在线重定义表的原理及步骤可参考：基于 dbms_redefinition 在线重定义表 http://www.linuxidc.com/Linux/2014-06/103628.htm有关分区表的描述请参考：Oracle 分区表 http://www.linuxidc.com/Linux/2014-06/103630.htm--------------------------------------------------------------------------------Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm--------------------------------------------------------------------------------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--创建一个中间表（分区表）
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_2012 VALUES LESS THAN （TO_DATE（"01/01/2013", "DD/MM/YYYY"）） tablespace tbs1,
PARTITION big_table_2013 VALUES LESS THAN （TO_DATE（"01/01/2014", "DD/MM/YYYY"）） tablespace tbs2,
PARTITION big_table_2014 VALUES LESS THAN （MAXVALUE） tablespace tbs3）;更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-06/103629p2.htm

Oracle 基于 dbms_redefinition 在线重定义表Oracle 分区表相关资讯 dbms_redefinition

Oracle 基于 dbms_redefinition 在（06/24/2014 20:05:34）

本文评论查看全部评论（0）