| Splitting partitions [message #477236] |
Wed, 29 September 2010 11:41  |
vikramsai31
Messages: 1
Registered: September 2010
Location: CA
|
Junior Member |
|
|
I've 20gb table which has been partitioned by range (yearly basis ans quaterly basis).
And most of the data has been stored in the max partition and segment size is increasing faster.
Now I want to get rid of max partition and divide the table with more partitions which would capture data yearly.This way I can have only 2 years worth data on production and partition can be dropped easily after moving to datawarehouse .
Kindly suggest.Partitions I want to split is yearly like
"PARTITION "2008" VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00',
TABLESPACE "LLS_DATA01" NOCOMPRESS"
Below is the table desc
CREATE TABLE "LLS"."test"
( "STARTDATE" DATE NOT NULL ENABLE,
"CALLID" CHAR(9 BYTE) NOT NULL ENABLE,
"CUSTOMERLLSID" NUMBER,
"CRCLIENTLLSID" NUMBER,
"EAPLLSID" NUMBER,
"EAPCOMMROOMID" NUMBER,
"LANGID" VARCHAR2(5 BYTE),
"CRCLIENTID" VARCHAR2(20 BYTE),
"PERSONALCODE" VARCHAR2(255 BYTE),
"PIN" NUMBER,
"SPECIALPROMOCODE" VARCHAR2(4 BYTE),
"PREBILLESTIMATE" NUMBER,
CONSTRAINT "LANID" CHECK ( LANID IN ('A', 'B','C','E','P') ) ENABLE,
CONSTRAINT "TRUE_OR_FALSE_IC1" CHECK (InterpreterLunchAdjustmentMade IN ('T', 'F') ) ENABLE,
CONSTRAINT "TRUE_OR_FALSE_IC2" CHECK (DontBillCustomer IN ('T', 'F') ) ENABLE,
CONSTRAINT "TRUE_OR_FALSE_IC3" CHECK (DontPayInterpreter IN ('T', 'F') ) ENABLE,
CONSTRAINT "TRUE_OR_FALSE_IC4" CHECK (RecordChanged IN ('T', 'F') ) ENABLE,
CONSTRAINT "TRUE_OR_FALSE_IC5" CHECK (LogicalDelete IN ('T', 'F') ) ENABLE,
CONSTRAINT "XPKINTERPRETATIONCALLS" PRIMARY KEY ("INTERPRETATIONSTARTDATE", "CALLID")
TABLESPACE "LLS_INDX01" ENABLE
)
TABLESPACE "U12_DATA"
PARTITION BY RANGE ("STARTDATE")
(PARTITION "2007Q3" VALUES LESS THAN (TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "LLS_INTCALLS_DATA01" NOCOMPRESS ,
PARTITION "2007Q4" VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00',
TABLESPACE "LLS_DATA01" NOCOMPRESS ,
PARTITION "CALLSMAX" VALUES LESS THAN (MAXVALUE)
TABLESPACE "LLS_INTCALLS_DATA01" NOCOMPRESS ) ;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
