Partition

[capr...@gmail.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
);

[Michael Austin]

seems reasonable. Unless you plan on maintaining this thing monthly by
adding the next months partition - I would consider building the
tablespaces to necessary for the amount of data you want to save - say
18 months or whatever your retention policy may be... and/or make sure
you document your month-end procedures to add "next month" so that the
DBA that comes after you knows what you did...