viernes, 21 de agosto de 2009

TABLAS PARTICIONADAS

CREATE TABLE "DBO"."TEM_SEG_OFERTA" ("HGO_ID" NUMBER NOT NULL,
"OFT_ID" NUMBER(9) NOT NULL, "FSO_ID" NUMBER(2) NOT NULL,
"HGO_FCH_REGISTRO" DATE NOT NULL, "FUN_ID" NUMBER(4),
"HGO_DESCRIPCION" VARCHAR2(300 byte)
)
PARTITION BY RANGE (HGO_FCH_REGISTRO)
(PARTITION HIS_SEG_OFERTA_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2005 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2006 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2007 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2008 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2009 VALUES LESS THAN (TO_DATE('01/01/2010', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2011 VALUES LESS THAN (TO_DATE('01/01/2012', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2019 VALUES LESS THAN (TO_DATE('01/01/2020', 'DD/MM/YYYY')),
PARTITION HIS_SEG_OFERTA_2020 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')),
PARTITION MAX_VALUE VALUES LESS THAN (MAXVALUE)
TABLESPACE "USERS")
/

ALTER TABLE big_table
SPLIT PARTITION MAX_VALUE AT (TO_DATE('01-01-2021','DD-MM-YYYY'))
INTO (PARTITION big_table_2021, PARTITION MAX_VALUE )


ALTER TABLE BIG_TABLE
ADD PARTITION big_table_2010 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY'));

ALTER TABLE BIG_TABLE DROP PARTITION BIG_TABLE_2010;

ALTER TABLE BIG_TABLE ADD PARTITION big_table_2009 VALUES LESS THAN (MAXVALUE);