partitioning table

here are the steps to partition table (e.g.QP_PRICING_ATTRIBUTE) on basis of the creation date column

1.SQL> select max(creation_date) from QP_PRICING_ATTRIBUTES;

MAX(CREATION_DA
---------------
26-JAN-11

To check the max date values in the column

2.
EXEC Dbms_Redefinition.Can_Redef_Table('QP','QP_PRICING_ATTRIBUTES');

PL/SQL procedure successfully completed.


This will confirm that there are no issues in converting the table into the partition table online.



3.
set heading off;
set echo off;
Set pages 999;
set long 90000;

spool a.log

SELECT DBMS_METADATA.GET_DDL('TABLE','QP_PRICING_ATTRIBUTES','QP') FROM DUAL;

make the changes.
· Change the name to add “_part”. This will be the interim table.
· Remove all the “not null” constraints.
· Add the partition clause

PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "APPS_TS_TX_DATA"
partition by range(creation_date)
(partition p1 values less than (TO_DATE('01-JUL-2010','DD-MON-YYYY')),
partition p2 values less than (TO_DATE('01-AUG-2010','DD-MON-YYYY')),
partition p3 values less than (TO_DATE('01-SEP-2010','DD-MON-YYYY')),
partition p4 values less than (TO_DATE('01-OCT-2010','DD-MON-YYYY')),
partition p5 values less than (TO_DATE('01-NOV-2010','DD-MON-YYYY')),
partition p6 values less than (TO_DATE('01-DEC-2010','DD-MON-YYYY')),
partition p7 values less than (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
partition p8 values less than (TO_DATE('01-FEB-2011','DD-MON-YYYY')))
/

Create the _part table

4.SQL> exec dbms_redefinition.start_redef_table('QP','QP_PRICING_ATTRIBUTES','QP_PRICING_ATTRIBUTES_PART');

PL/SQL procedure successfully completed.

This will start moving the data from the old table into the interim table while users can still access transparent.

5.SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('QP','QP_PRICING_ATTRIBUTES','QP_PRICING_ATTRIBUTES_PART',1,TRUE,TRUE,TRUE,TRUE,:NUM_ERRORS,FALSE);


PL/SQL procedure successfully completed.

SQL> SQL>
SQL> SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
0

This will move all the indexes,constraints, triggers,privs to the interim. Make sure the num_errors is 0.

6.EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QP','QP_PRICING_ATTRIBUTES','QP_PRICING_ATTRIBUTES_PART');


PL/SQL procedure successfully completed.

7.> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('QP','QP_LIST_HEADERS_B','QP_LIST_HEADERS_B_PART');

PL/SQL procedure successfully completed.

Interim table is now the new partitioned table.



Checkups
============


Select partition_name, high_value from DBA_tab_partitions where table_name='QP_PRICING_ATTRIBUTES'


select privilege, grantee from dba_tab_privs where table_name='QP_PRICING_ATTRIBUTES';


SELECT index_name, status FROM DBA_INDEXES WHERE TABLE_NAME='QP_PRICING_ATTRIBUTES';