Switch normal tables to partitioned tables online using dbms_redefinition

As the amount of database data increases, some tables need to be converted from ordinary heap tables to partitioned table patterns. There are several different ways to do this. such as exporting table data, and then creating a partitioned table and then importing the data into the partition table. Use the Exchange partition method to convert to a partitioned table and use Dbms_redefinition to redefine the partitioned table online. Described in this article is the use of dbms_redefinition to achieve, the following is a detailed demonstration of the operation of the sample.

For detailed dbms_redefinition on-line redefinition table the principle and the procedure can refer to: Based on the dbms_redefinition online redefinition table
For a descriptive description of the partition table, please refer to: Oracle partition table

1. Preparation environment

--Creating user sql> Create users leshami identified by XXX; Sql> Grant DBA to leshami;--create a table space to demonstrate the required 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--Creating a Lookup Table CREATE table lookup (ID number, description VARCHAR2 (50));--Join the primary key Constraint alter TABLE lookup ADD (CONSTRAINT lookup_pk PRIMARY KEY (ID));--insert data inserts into lookup (ID, description) VALUES (1, ' One '); INSERT into lookup (ID, description) VALUES (2, ' both '); INSERT into lookup (ID, description) VALUES (3, ' three '); COMM it;--creates a large table to switch to the partition CREATE table big_table (ID number (10), Created_date date, lookup_id number, data VARCHAR2 (50));--fill data to large table declare l_lookup_id lookup.id%ty  PE; 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;/--joins the primary and foreign key constraints for large tables. Index. And adding triggers, and so on. 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;/--collects statistics exec dbms_stats.gather_table_stats (' Leshami ', ' LOOKUP ', cascade = TRUE); EXEC dbms_stats.gather_table_stats (' Leshami ', ' big_table ', cascade = TRUE);--index-related information on the table 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 an intermediate table (partition table) CREATE TABLE Big_table2 (ID N      Umber, created_date date, lookup_id number (ten), data    VARCHAR2 () 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, Toggle normal table to partition table

----Whether the check table can be redefined by EXEC dbms_redefinition.           Can_redef_table (' Leshami ', ' big_table ');--Start redefining begin dbms_redefinition.start_redef_table (uname = Leshami '), orig_table = ' big_table ', int_table = ' big_table2 '); end;/--Optional steps for synchronizing a redefined table with a temporary table BEGIN dbms_redefinition.sync_interim_table (uname = ' Leshami ', orig_tabl E = ' big_table ', int_table = ' big_table2 '); end;/--Add the corresponding index to the intermediate table, the constraint such as 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 trigger for intermediate table create OR REPLACE TRIGGER tr_bf_big_table2 before UPDATE of Created_date on big_table2 for each ROWBEGIN:new.created_dat E: = 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);--Complete online redefinition begin dbms_redefinition.finish_redef_table (-- Author:leshami uname = ' Leshami ',--blog:http://blog.csdn.net/leshmai orig_table =& Gt ' Big_table ', int_table = ' big_table2 '); end;/--can see that the index on the new table big_table has become a partitioned index. As seen in the last column sql> @idx_infoTABLE_NAME index_name cl_nam STA TUS PAR-----------------------------------------------------------------------------------------------------BIG                      _table big_table_pk2 ID VALID nobig_table BITA_LOOK_FK_I2 lookup_id N/a yesbig_table BITA_CR            EATED_DATE_I2 created_date N/a yesbig_table2 bita_created_date_i     Created_date VALID Nobig_table2                BIG_TABLE_PK ID VALID nobig_table2 BI                      Ta_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-----------------------                 -------------------------------------------------------------------------big_table big_table_2012 To_date (' 2013-01-01 00:00:00 ', 333793big_table big_table_2013 to_date (' 20                            14-01-01 00:00:00 ', 332025big_table big_table_2014 MAXVALUE 333128

3, after the handover of the finishing work

--Remove the middle table drop table big_table2;--based on the need to be able to rename the index and constraint name 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. Other

--During the use of dbms_redefinition, suppose we use the copy_table_dependents process, whose index remains a non-partitioned index--the need to delete the original index, and then create it again as a new local partition index/*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 */------------------------------------------------- ---------For example, the following table Big_table2 uses copy_table_dependents dependent objects after a few tmp$$, but the index remains a normal index. Instead of a partitioned index sql> @idx_info2 table_name index_name cl_nam cl_pos STATUS P                    AR--------------------------------------------------------------------------------------------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_tabl E2 tmp$$_big_table_pk0 ID 1 VALID nobig_table2 Tmp$$_bi Ta_created_date_i0 created_date 1 VALID nobig_table2 tmp$$_bita_look_fk_i0 LOO kup_id 1 VALID NO

5. References:http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php

Switch normal tables to partitioned tables online using dbms_redefinition