Partitioning ODD_DATA table

Steps-
1. export dump
2. rename table (and rename index if necessary)
3. create new partitioned table
4. import table data into new partitioned table - Data Only
5. Add Index and Constraints the way you want it


STEP-1: Export TABLE & Data

i. Make a directory DBDUMPS on /da02, and Give permission to PRD7

ii. Export old_data
expdp prd7/*** tables=ODD_DATA DIRECTORY=DBDUMPSSS DUMPFILE=DFF01-PRD7-20100715_%U.dmp FILESIZE=10G logfile=DFF01-PRD7-20100715_LOG.log


STEP-2: Rename Table

ALTER TABLE PROD7.ODD_DATA RENAME TO ODD_DATA_OLD;


STEP-3: Create new Partitioned Table

CREATE TABLE PRD7.ODD_DATA (
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, changed_by_login_name VARCHAR2 (64)
, changed_by_first_name VARCHAR2 (20)
, changed_by_last_name VARCHAR2 (20)
, changed_by_title VARCHAR2 (64)
, individual_id NUMBER (10)
, individual_first_name VARCHAR2 (20)
, individual_last_name VARCHAR2 (20)
, form_id VARCHAR2 (30)
, version VARCHAR2 (20)
, prov_id NUMBER (10)
, prov_code VARCHAR2 (16)
, prov_name VARCHAR2 (64)
, pgm_id NUMBER (10)
, pgm_name VARCHAR2 (64)
, tz VARCHAR2 (32)
, clob_data CLOB
, form_name VARCHAR2 (150)
, related_user_login_id NUMBER (10)
, related_user_login_name VARCHAR2 (64)
, related_user_first_name VARCHAR2 (20)
, related_user_last_name VARCHAR2 (20)
, related_user_tite VARCHAR2 (64)
, test_form NUMBER (1) DEFAULT 0
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA01_16K NOCOMPRESS
);

STEP-4: Import

impdp system/***** DIRECTORY=DBDUMPSSS CONTENT=DATA_ONLY DUMPFILE=DFF01-PRD7-20100715_%U.dmp PARALLEL=3


STEP-5: ADD Primary Key and INDEXES

SQL> ALTER TABLE PRD7.ODD_DATA ADD primary key (id);

SQL> CREATE INDEX PRD7.ODD_DATA_PGM_INDV_I1 ON PRD7.ODD_DATA ( pgm_id, individual_id );

SQL> CREATE INDEX PRD7.OD_PROV_FTYPE_ID_FID_I1 ON PRD7.ODD_DATA ( prov_id, UPPER(FORM_TYPE), id, form_id );

----------------------------------- END --------------------------------------------------------------

Checking Each Partition Data:

select count(*) from prd7.odd_data;

SQL> col HIGH_VALUE format a40

SQL> SELECT partition_name PART_NAME, tablespace_name TBSP_NAME, high_value
FROM user_tab_partitions WHERE table_name = 'ODD_DATA';


SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);

SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);


AFTER Some Days....

SQL> DROP TABLE PRD7.ODD_DATA_OLD cascade constraints;