Partitioning an Existing Table

http://www.conacent.com

Partitioning an Existing Table using DBMS_REDEFINITION

•Create a Partitioned Interim Table
•Start the Redefintion Process
•Create Constraints and Indexes
•Complete the Redefintion Process


Create a Partitioned Interim Table

Next we create a new table with the appropriate partition structure to act as an interim table.

-- Create partitioned 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_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));

With this interim table in place we can start the online redefinition.

Start the Redefintion ProcessFirst we check the redefinition is possible using the following command.

EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');

If no errors are reported it is safe to start the redefintion using the following command.

BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
Depending on the size of the table, this operation can take quite some time to complete.

Create Constraints and IndexesIf there is delay between the completion of the previous operation and moving on to finish the redefinition, it may be sensible to resynchronize the interim table before building any constraints and indexes. The resynchronization of the interim table is initiated using the following command.

-- Optionally synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/

The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors. The indexes should be created with the appropriate partitioning scheme to suit their purpose.

Complete the Redefintion ProcessOnce the constraints and indexes have been created the redefinition can be completed using the following command.

BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/

At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform some cleanup operations.

-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;