Partitioning A Huge Table

1-Rename the current table from TBL_SE_XREF to TBL_SE_XREF_OLD


2-Create a partitioned table:
-----------------------------
CREATE table TBL_SE_XREF
(
ID NUMBER,
G_ID NUMBER,
COM_ID NUMBER,
SE_PN VARCHAR2(256 BYTE),
SE_MAN_ID NUMBER(10),
SE_TYPE_ID NUMBER,
COMMENTS VARCHAR2(256 BYTE),
SOURCE VARCHAR2(50 BYTE),
NAN_PARTNUM VARCHAR2(256 BYTE),
INSERT_DATE DATE,
REPLACEMENT NUMBER
)
PARTITION BY HASH (SE_MAN_ID,NAN_PARTNUM)
(PARTITION P1_TBL_SE_XREF TABLESPACE XREF_PART01,
PARTITION P2_TBL_SE_XREF TABLESPACE XREF_PART02,
PARTITION P3_TBL_SE_XREF TABLESPACE XREF_PART03,
PARTITION P4_TBL_SE_XREF TABLESPACE XREF_PART04,
PARTITION P5_TBL_SE_XREF TABLESPACE XREF_PART05,
PARTITION P6_TBL_SE_XREF TABLESPACE XREF_PART06,
PARTITION P7_TBL_SE_XREF TABLESPACE XREF_PART07,
PARTITION P8_TBL_SE_XREF TABLESPACE XREF_PART08,
PARTITION P9_TBL_SE_XREF TABLESPACE XREF_PART09,
PARTITION P10_TBL_SE_XREF TABLESPACE XREF_PART10)
ENABLE ROW MOVEMENT;


3-insert data from TBL_SE_XREF_OLD to TBL_SE_XREF:
--------------------------------------------------
insert into TBL_SE_XREF
select * from TBL_SE_XREF_OLD;
commit;


4-Create Partitioned Index on the table TBL_SE_XREF:
----------------------------------------------------

CREATE INDEX MANID_NANPART_IDX ON TBL_SE_XREF
(SE_MAN_ID, NAN_PARTNUM)
NOLOGGING
LOCAL (
PARTITION P1_TBL_SE_XREF
TABLESPACE SE_PART_IDX_1,
PARTITION P2_TBL_SE_XREF
TABLESPACE SE_PART_IDX_2,
PARTITION P3_TBL_SE_XREF
TABLESPACE SE_PART_IDX_3,
PARTITION P4_TBL_SE_XREF
TABLESPACE SE_PART_IDX_4,
PARTITION P5_TBL_SE_XREF
TABLESPACE SE_PART_IDX_5,
PARTITION P6_TBL_SE_XREF
TABLESPACE SE_PART_IDX_6,
PARTITION P7_TBL_SE_XREF
TABLESPACE SE_PART_IDX_7,
PARTITION P8_TBL_SE_XREF
TABLESPACE SE_PART_IDX_8,
PARTITION P9_TBL_SE_XREF
TABLESPACE SE_PART_IDX_9,
PARTITION P10_TBL_SE_XREF
TABLESPACE SE_PART_IDX_10
)
NOPARALLEL
COMPUTE STATISTICS;

5-you can now drop TBL_SE_XREF_OLD table