How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]

How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1]
	Modified28-SEP-2010TypeHOWTOStatusMODERATED

In this Document
Goal
Solution

---

Platforms: 1-914CU;

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Standard Edition - Version: 9.2.0.4 and later[Release: 9.2 and later ]
Information in this document applies to any platform.
"Checked for relevance on 29-Sep-2010"

Goal

The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

Solution

1) Create unpartitioned table with the name unpar_table

SQL>CREATETABLEunpar_table(
idNUMBER(10),
create_dateDATE,
nameVARCHAR2(100)
);

2) Apply some constraints to the table:

SQL>ALTERTABLEunpar_tableADD(
CONSTRAINTunpar_table_pkPRIMARYKEY(id)
);

SQL>CREATEINDEXcreate_date_indONunpar_table(create_date);

3) Gather statistics on the table:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);

4) Create a Partitioned Interim Table:

SQL>CREATETABLEpar_table(
idNUMBER(10),
create_dateDATE,
nameVARCHAR2(100)
)
PARTITIONBYRANGE(create_date)
(PARTITIONunpar_table_2005VALUESLESSTHAN(TO_DATE('01/01/2005','DD/MM/YYYY')),
PARTITIONunpar_table_2006VALUESLESSTHAN(TO_DATE('01/01/2006','DD/MM/YYYY')),
PARTITIONunpar_table_2007VALUESLESSTHAN(MAXVALUE));

5) Start the Redefinition Process:

a) Check the redefinition is possible using the following command:

SQL>EXECDbms_Redefinition.can_redef_table(USER,'unpar_table');

b)If no errors are reported, start the redefintion using the following command:

SQL>BEGIN
DBMS_REDEFINITION.start_redef_table(
uname=>USER,
orig_table=>'unpar_table',
int_table=>'par_table');
END;
/

Note: This operation can take quite some time to complete.

c) Optionally synchronize new table with interim name before index creation:

SQL>BEGIN
dbms_redefinition.sync_interim_table(
uname=>USER,
orig_table=>'unpar_table',
int_table=>'par_table');
END;
/

d) Create Constraints and Indexes:

SQL>ALTERTABLEpar_tableADD(
CONSTRAINTunpar_table_pk2PRIMARYKEY(id)
);

SQL>CREATEINDEXcreate_date_ind2ONpar_table(create_date);

e) Gather statistics on the new table:

SQL>EXECDBMS_STATS.gather_table_stats(USER,'par_table',cascade=>TRUE);

f) Complete the Redefintion Process:

SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'unpar_table',
int_table => 'par_table');
END;
/

At this point the interim table has become the "real" table and their names have been switched in the name dictionary.

g) Remove original table which now has the name of the interim table:

SQL>DROPTABLEpar_table;

h)Rename all the constraints and indexes to match the original names.

ALTERTABLEunpar_tableRENAMECONSTRAINTunpar_table_pk2TOunpar_table_pk;
ALTERINDEXcreate_date_ind2RENAMETOcreate_date_ind;

i) Check whether partitioning is successful or not:

SQL> SELECT partitioned
FROM user_tables
WHERE table_name = 'unpar_table';

PAR
---
YES

1 row selected.

SQL> SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'unpar_table';

PARTITION_NAME
------------------------------
unpar_table_2005
unpar_table_2006
unpar_table_2007

3 rows selected.

Please not that the 9i redefinition procedures has some restrictions:

* There must be enough space to hold two copies of the table.
* Primary key columns cannot be modified.
* Tables must have primary keys.
* Redefinition must be done within the same schema.
* New columns added cannot be made NOT NULL until after the redefinition operation.
* Tables cannot contain LONGs, BFILEs or User Defined Types.
* Clustered tables cannot be redefined.
* Tables in the SYS or SYSTEM schema cannot be redefined.
* Tables with materialized view logs or materialized views defined on them cannot be redefined.
* Horizontal sub setting of data cannot be performed during the redefinition.

---

------------------------------------------------------------------------------