Partition

From: <caprikar_at_gmail.com>
Date: Wed, 13 Feb 2008 10:28:46 -0800 (PST)
Message-ID: <a247d566-0da6-4aae-9e6d-5bab65f6acc7@s8g2000prg.googlegroups.com>

Hi I have the following table which is not partitioned and I want to partition it, here is the method am using to partition that table, can anyone tell me is it the right way to do or is there anything else i need to look at before i partition?

Current status:

CREATE TABLE CHANGE_REC
(

  CHANGE_REC_ID  NUMBER(15)                  NOT NULL,
  CHANGE_LOG_ID   NUMBER(15),
  TAB_NAME        VARCHAR2(30 BYTE),
  COL_NAME          VARCHAR2(30 BYTE),
  OLD_VALUE         VARCHAR2(4000 BYTE),
  NEW_VALUE         VARCHAR2(4000 BYTE),
  CHNGE_TIME       TIMESTAMP(6)

)
TABLESPACE DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING; CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_REC_ID)

LOGGING
TABLESPACE INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (

            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL; ALTER TABLE CHANGE_REC ADD (
  CONSTRAINT PK_CHANGE_REC
 PRIMARY KEY
 (CHANGE_REC_ID)
    USING INDEX
    TABLESPACE INDEX
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
 );

---

Want to partition that table using..

CREATE TABLE CHANGE_REC
(

  CHANGE_REC_ID  NUMBER(15)                  NOT NULL,
  CHANGE_LOG_ID   NUMBER(15),
  TABLE_NAME        VARCHAR2(30 BYTE),
  COL_NAME          VARCHAR2(30 BYTE),
  OLD_VALUE         VARCHAR2(4000 BYTE),
  NEW_VALUE         VARCHAR2(4000 BYTE),
  CHNGE_TIME       TIMESTAMP(6)

)
TABLESPACE DATA
LOGGING
PARTITION BY RANGE (CHNGE_TIME)
(

  PARTITION Y08M01 VALUES LESS THAN (TIMESTAMP'2008-02-01 00:00:00')     LOGGING
    NOCOMPRESS
    TABLESPACE DATA,   PARTITION Y08M02 VALUES LESS THAN (TIMESTAMP'2008-03-01 00:00:00')     LOGGING
    NOCOMPRESS
    TABLESPACE DATA,   PARTITION Y08M03 VALUES LESS THAN (TIMESTAMP'2008-04-01 00:00:00')     LOGGING
    NOCOMPRESS
    TABLESPACE DATA
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING; CREATE UNIQUE INDEX PK_CHANGE_REC ON CHANGE_REC
(CHANGE_LOG_ID, CHNGE_TIME)

  TABLESPACE INDEX
  INITRANS 2
  MAXTRANS 255
LOGGING
LOCAL (
  PARTITION Y08M01
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX,   PARTITION Y08M02
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX,   PARTITION Y08M03
    LOGGING
    NOCOMPRESS
    TABLESPACE INDEX
)
NOPARALLEL; ALTER TABLE CHANGE_REC ADD (
  CONSTRAINT PK_CHANGE_REC
 PRIMARY KEY
 (CHANGE_REC_ID, CHNGE_TIME)
    USING INDEX
    TABLESPACE USERS
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
 ); Received on Wed Feb 13 2008 - 12:28:46 CST