Convert Non-Partitioned to Partitioned Table - using Online Re-definition feature (10g)

Here is a working example to convert a non-partitioned table to a partitioned table. With this example, we will not see how to convert a table, but will expose to the usage of Online Redefinition feature. There is one neat (apart from others) enhancement to the online redefinition feature, which we are going to talk about.

• We have a non-partitioned table T1 with about 2 million records
• It has a primary key, grants to various roles and public synonyms (dependencies)
• Our goal is to partition the table using the primary key column (col1 number(100))

Some restrictions of Online Redefinition:

• Cannot belong to SYS or SYSTEM Schema.
• The table to be redefined online should not be using User-defined data types
• Should not be a clustered table.
• Should not have MV Logs or MVs defined

You cannot move a table from one schema to another using Online Redefinition feature.
1. Check to make sure that the table can use the online redefintion feature:

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(’CHANDRA’,'T1′);

PL/SQL procedure successfully completed.

2. Create a partitioned table (add any new attributes you want to the new partitioned table)

Create table t1_part
( col1 number(10),
col2 varchar2(30),
col3 date default sysdate,
col4 varchar2(40))
partition by range (col1)
(Partition p1 values less than (100000),
Partition p2 values less than (200000),
Partition p3 values less than (300000),
Partition p4 values less than (400000),
Partition p5 values less than (500000),
Partition p6 values less than (600000),
Partition p7 values less than (700000),
Partition p8 values less than (800000),
Partition p9 values less than (900000),
Partition p10 values less than (1000000),
Partition p11 values less than (1100000),
Partition p12 values less than (1200000),
Partition p13 values less than (1300000),
Partition p14 values less than (1400000),
Partition p15 values less than (1500000),
Partition p16 values less than (1600000),
Partition p17 values less than (1700000),
Partition p18 values less than (1800000),
Partition p19 values less than (1900000),
Partition p20 values less than (maxvalue))
tablespace users;

3. Start the online redefinition processSQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(’CHANDRA’,'T1′,’T1_PART’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.53

4. Here is where the Oracle10g feature comes in handy. In Oracle9i, we were required create constraints, indexes, grants etc…on the staging table (t1_part in our example). If triggers, constraints, indexes and grants are defined on the table. These can be cloned on the staging table by executing DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( ) Once redefinition is complete, the referential constraints and triggers involving the stage table (created disabled) are enabled, all statistics will be copied, and all cloned objects will be renamed to their pre-definition name.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(’CHANDRA’,'T1′,’T1_PART’, 1,TRUE,TRUE,TRUE,FALSE,:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.08
SQL> PRINT NUM_ERRORS

NUM_ERRORS
———-
0

5. Resync the tables

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(’CHANDRA’,'T1′,’T1_PART’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.23

6. Complete the online redefinition:

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(’CHANDRA’,'T1′,’T1_PART’);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.18

7. Validations:

SQL> Select partition_name, high_value from user_tab_partitions where table_name=’T1′
2 ;

PARTITION_NAME HIGH_VALUE
—————————— ——————————
P1 100000
P10 1000000
P11 1100000
P12 1200000
P13 1300000
P14 1400000
P15 1500000
P16 1600000
P17 1700000
P18 1800000
P19 1900000
P2 200000
P20 MAXVALUE
P3 300000
P4 400000
P5 500000
P6 600000
P7 700000
P8 800000
P9 900000

20 rows selected.

The privileges granted were are also preserved:

SQL> select privilege, grantee from dba_tab_privs where table_name=’T1′;

PRIVILEGE GRANTEE
—————————————- ——————————
ALTER READ_WRITE
DELETE READ_WRITE
INSERT READ_WRITE
SELECT READ_WRITE
SELECT READ_ONLY
UPDATE READ_WRITE
ON COMMIT REFRESH READ_WRITE
QUERY REWRITE READ_WRITE
DEBUG READ_WRITE
FLASHBACK READ_WRITE

10 rows selected.

The only problem I see here is the index is now global and we may like to have a local prefixed indexed. For this purpose we may have to drop the existing index and create a local index;

SQL> alter table t1 disable primary key;

Table altered.

Elapsed: 00:00:00.09
SQL> drop index t1_idx;

Index dropped.

Elapsed: 00:00:00.18
SQL> create index t1_idx on t1(col1) local tablespace users;

Index created.

Elapsed: 00:00:11.91
SQL> alter table t1 enable primary key;

Table altered.

Elapsed: 00:00:13.54
SQL> Select index_name, index_type, partitioned from user_indexes where table_name=’T1′;

INDEX_NAME INDEX_TYPE PAR
—————————— ————————— —
T1_IDX NORMAL YES

Elapsed: 00:00:00.07