Tuesday, March 14, 2006

move partitions LOBs

My projects stored LOB data.
and i created table bt use partitions for LOB

CREATE TABLE AttachData (
attachmentID INTEGER NOT NULL,
attachmentData BLOB NOT NULL,
CONSTRAINT AttachData_pk PRIMARY KEY (attachmentID)
USING INDEX TABLESPACE MWJIVE_INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
))
TABLESPACE MWJIVE
PCTFREE 10
PCTUSED 0
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
LOGGING
LOB (attachmentData)
STORE AS JIVEAD_SEG_LOB (TABLESPACE MWJIVE_LOB
STORAGE (
INITIAL 256K
NEXT 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELIST GROUPS 1
)
DISABLE STORAGE IN ROW
NOCACHE
CHUNK 8192
PCTVERSION 20)
PARTITION BY HASH (attachmentID)
(PARTITION ATTACHID_PART1 TABLESPACE MWJIVE
LOB(attachmentData) STORE AS JIVEAD_SEG_LOB_PART1 (TABLESPACE MWJIVE_LOB),
PARTITION ATTACHID_PART2 TABLESPACE MWJIVE
LOB(attachmentData) STORE AS JIVEAD_SEG_LOB_PART2 (TABLESPACE MWJIVE_LOB),
PARTITION ATTACHID_PART3 TABLESPACE MWJIVE
LOB(attachmentData) STORE AS JIVEAD_SEG_LOB_PART3 (TABLESPACE MWJIVE_LOB),
PARTITION ATTACHID_PART4 TABLESPACE MWJIVE
LOB(attachmentData) STORE AS JIVEAD_SEG_LOB_PART4 (TABLESPACE MWJIVE_LOB)
);

Oh but i used only one tablespace (MWJIVE_LOB).
I think and think
How i move partitions,because i want to more tablespaces .

So

I create MWJIVE_LOB1, MWJIVE_LOB2,MWJIVE_LOB3, MWJIVE_LOB4, MWJIVE_LOB5 tablespaces

and

alter TABLE AttachData
move
PARTITION ATTACHID_PART1
lob(attachmentData)
STORE AS JIVEAD_SEG_LOB_PART5 (TABLESPACE MWJIVE_LOB1);

alter TABLE AttachData
move
PARTITION ATTACHID_PART2
lob(attachmentData)
STORE AS JIVEAD_SEG_LOB_PART6 (TABLESPACE MWJIVE_LOB2);

alter TABLE AttachData
move
PARTITION ATTACHID_PART3
lob(attachmentData)
STORE AS JIVEAD_SEG_LOB_PART7 (TABLESPACE MWJIVE_LOB3);

alter TABLE AttachData
move
PARTITION ATTACHID_PART4
lob(attachmentData)
STORE AS JIVEAD_SEG_LOB_PART8 (TABLESPACE MWJIVE_LOB4);

-----------------

No comments: