Use DBMS_REDEFINITION to switch a common table to a partition table online

As the database data volume continues to grow, some tables need to be converted from normal heap tables to partition tables. There are several different methods for this operation, such as exporting table data, creating a partition table, and then importing data to the partition table; use the exchange partition method to convert to a PARTITION table and use DBMS_REDEFINITION to redefine a PARTITION table online. This document describes how to use DBMS_REDEFINITION. The following is an example.

For more information about the principles and steps of online redefinition of dbms_redefinition tables, see online redefinition of tables based on dbms_redefinition.
For more information about partition tables, see Oracle partition tables.

 

1. Prepare the environment

-- Create user SQL> create user leshami identified by xxx; SQL> grant dba to leshami; -- create a table space SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10 m autoextend on; SQL> alter user leshami default tablespace tbs_tmp; SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10 m autoextend on; SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10 m autoextend on; SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10 m autoextend on; SQL> conn leshami/xxx -- CREATE a lookup TABLE CREATE TABLE lookup (id NUMBER (10), description VARCHAR2 (50 )); -- ADD the primary key constraint 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 )); -- fill in the data to the Big Table 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;/-- add primary and foreign key constraints for large tables, indexes, and add triggers. 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;/-- collect statistics EXEC DBMS_STATS.gather_table_stats ('leshami ', 'lookup ', cascade => TRUE); EXEC DBMS_STATS.gather_table_stats ('lesham', 'Big _ table', cascade => TRUE ); -- index-related information on a table SQL> @ idx_infoTABLE_NAME INDEX_NAME CL_NAM STATUS PAR has been created -------- --- LOOKUP LOOKUP_PK ID VALID NO BIG_TABLE has CREATED_DATE VALID NO BIG_TABLE has LOOKUP_ID VALID NO -- create an intermediate table (partition table) 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 ('1970/123', 'dd/MM/yyyy') tablespace tbs1, PARTITION big_table_2013 values less than (TO_DATE ('1970/123', 'dd/MM/YYYY ') tablespace tbs2, PARTITION big_table_2014 values less than (MAXVALUE) tablespace tbs3 );

2. Switch A common table to a partitioned table

---- Check whether the TABLE can be redefined EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('lesham', 'Big _ table'); -- start to redefine BEGIN DBMS_REDEFINITION.start_redef_table (uname => 'lesham ', orig_table => 'Big _ table', int_table => 'Big _ table2'); END;/-- optional steps, used to synchronize the redefinition TABLE and temporary table begin dbms_redefinition.sync_interim_table (uname => 'lesham', orig_table => 'Big _ table', int_table => 'Big _ table2'); END; /-- ADD indexes and constraints to the intermediate TABLE. 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 )); -- add the trigger 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 ('lesham', 'Big _ table2', cascade => TRUE); -- redefines BEGIN commit (-- Author: LESHAMI uname => 'lesham ', -- Blog: http://blog.csdn.net/leshmai orig_table => 'Big _ table', int_table => 'Big _ table2'); END; /-- we can see that the index on the new table BIG_TABLE has become a partition index, as shown in the last column, SQL> @ idx_infoTABLE_NAME INDEX_NAME CL_NAM STATUS PAR has been created before -------- --- BIG_TABLE Region ID VALID NOBIG_TABLE has LOOKUP_ID N/A YESBIG_TABLE has CREATED_DATE N/A YESBIG_TABLE2 has CREATED_DATE id valid NOBIG_TABLE2 BITA_LOOK_FK_ I LOOKUP_ID valid nolookup LOOKUP_PK id valid no -- check Partition Table 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 partition partitions ---------- BIG_TABLE BIG_TABLE_2012 TO_DATE ('00:00:00 ', 33100003big_table BIG_TABLE_2013 TO_DATE ('00:00:00 ', 332025BIG_TABLE BIG_TABLE_2014 MAXVALUE 333128

3. Final work after switching

-- Remove the intermediate table drop table big_table2; -- you can RENAME the index and the CONSTRAINT name alter table big_table rename constraint big_table_pk2 TO big_table_pk as needed; 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. Others

-- When DBMS_REDEFINITION is used, if COPY_TABLE_DEPENDENTS is used, its index is still non-partitioned. -- after the original index needs to be deleted, then create a new local partition index/* SQL> set timing offSQL> variable num_errors numberBEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname => 'lesham', orig_table => 'Big _ table ', int_table => 'Big _ TABLE2 ', num_errors =>: num_errors); END; /SQL> print num_errorsNUM_ERRORS ---------- 0 */dependencies: The table BIG_TABLE2 has several more indexes starting with TMP $ after the dependency object COPY_TABLE_DEPENDENTS is used, but the index is still a common index, instead of Partitioned index SQL> @ idx_info2 TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS PAR has been created ------ -------- --- LOOKUP Region ID 1 VALID NOBIG_TABLE Region ID 1 VALID NOBIG_TABLE region CREATED_DATE 1 VALID NOBIG_TABLE region 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. Reference: http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php