首页 > 代码库 > 使用DBMS_REDEFINITION在线切换普通表到分区表
使用DBMS_REDEFINITION在线切换普通表到分区表
随着数据库数据量的不断增长,有些表须要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作。诸如导出表数据,然后创建分区表再导入数据到分区表。使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描写叙述的是使用DBMS_REDEFINITION来实现,以下是详细的操作演示样例。
有关详细的dbms_redefinition在线重定义表的原理及步骤可參考:基于 dbms_redefinition 在线重定义表
有关分区表的描写叙述请參考:Oracle 分区表
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 ROWBEGIN :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_infoTABLE_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);
2、切换普通表到分区表
----校验表是否能被重定义 EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘LESHAMI‘, ‘BIG_TABLE‘);--開始重定义BEGIN DBMS_REDEFINITION.start_redef_table( uname => ‘LESHAMI‘, orig_table => ‘BIG_TABLE‘, int_table => ‘BIG_TABLE2‘);END;/--可选步骤,用于同步重定义表与暂时表 BEGIN dbms_redefinition.sync_interim_table( uname => ‘LESHAMI‘, orig_table => ‘BIG_TABLE‘, int_table => ‘BIG_TABLE2‘);END;/--为中间表加入对应的索引,约束等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 BEFORE UPDATE OF created_date ON big_table2 FOR EACH ROWBEGIN :new.created_date := TO_CHAR (SYSDATE, ‘yyyymmdd hh24:mi:ss‘);END tr_bf_big_table2;/-- Gather statistics on the new table.EXEC DBMS_STATS.gather_table_stats(‘LESHAMI‘, ‘BIG_TABLE2‘, cascade => TRUE);--完毕在线重定义BEGIN dbms_redefinition.finish_redef_table( --Author : Leshami uname => ‘LESHAMI‘, --Blog : http://blog.csdn.net/leshmai orig_table => ‘BIG_TABLE‘, int_table => ‘BIG_TABLE2‘);END;/--能够看到新的表BIG_TABLE上的索引已经成为分区索引。如最后一列所看到的SQL> @idx_infoTABLE_NAME INDEX_NAME CL_NAM STATUS PAR------------------------------ ------------------------------ ------------------------------ -------- ---BIG_TABLE BIG_TABLE_PK2 ID VALID NOBIG_TABLE BITA_LOOK_FK_I2 LOOKUP_ID N/A YESBIG_TABLE BITA_CREATED_DATE_I2 CREATED_DATE N/A YESBIG_TABLE2 BITA_CREATED_DATE_I CREATED_DATE VALID NOBIG_TABLE2 BIG_TABLE_PK ID VALID NOBIG_TABLE2 BITA_LOOK_FK_I LOOKUP_ID VALID NOLOOKUP LOOKUP_PK ID VALID NO--校验分区表SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions where table_name=‘BIG_TABLE‘;TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS------------------------- ------------------------------ ------------------------------- ----------BIG_TABLE BIG_TABLE_2012 TO_DATE(‘ 2013-01-01 00:00:00‘, 333793BIG_TABLE BIG_TABLE_2013 TO_DATE(‘ 2014-01-01 00:00:00‘, 332025BIG_TABLE BIG_TABLE_2014 MAXVALUE 333128
3、切换后的收尾工作
--移除中间表DROP TABLE big_table2;--依据须要能够重命名索引及约束名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;
4、其他
--在使用DBMS_REDEFINITION期间,假设我们使用COPY_TABLE_DEPENDENTS过程,其索引依然为非分区索引--须要删除原来的索引之后,然后又一次创建为新的本地分区索引/*SQL> set timing offSQL> variable num_errors numberBEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname=>‘LESHAMI‘, orig_table=>‘BIG_TABLE‘, int_table=>‘BIG_TABLE2‘, num_errors=>:num_errors);END;/SQL> print num_errorsNUM_ERRORS---------- 0 */ ----------------------------------------------------------例如以下为使用COPY_TABLE_DEPENDENTS依赖对象之后表BIG_TABLE2多出了几个TMP$$开头的索引,可是索引依然为普通索引。而非分区索引 SQL> @idx_info2 TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS PAR------------------------- ------------------------------ -------------------- ------ -------- ---LOOKUP LOOKUP_PK ID 1 VALID NOBIG_TABLE BIG_TABLE_PK ID 1 VALID NOBIG_TABLE BITA_CREATED_DATE_I CREATED_DATE 1 VALID NOBIG_TABLE BITA_LOOK_FK_I LOOKUP_ID 1 VALID NOBIG_TABLE2 TMP$$_BIG_TABLE_PK0 ID 1 VALID NOBIG_TABLE2 TMP$$_BITA_CREATED_DATE_I0 CREATED_DATE 1 VALID NOBIG_TABLE2 TMP$$_BITA_LOOK_FK_I0 LOOKUP_ID 1 VALID NO
5 、參考:http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php
使用DBMS_REDEFINITION在线切换普通表到分区表
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。