Range Partition
- Create Table
CREATE TABLE TABLE_NAME
(
COLUMN_1 DATE
,COLUMN_2 VARCHAR2(X)
)
TABLESPACE XXX
PCTUSED 0
PCTFREE 0
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOLOGGING
COMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
PARTITION BY RANGE(COLUMN_1)
(
PARTITION PAR_MIN VALUES LESS THAN (TO_DATE('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
,PARTITION PAR_MAX VALUES LESS THAN (TO_DATE('9999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
- Alter table (Partition)
ADD (HASH), COALESCE (HASH), DROP,
EXCHANGE, MERGE, MOVE, SPLIT, TRUNCATE
alter table table_name exchange partition partition_name with table table_name with validation;
1. Add/Drop/Truncate Partition
alter table table_name add partition partition_name values less than ('value');
alter table table_name drop partition partition_name;
alter table table_name truncate partition partition_name;
/
2. Else
split
alter table table_name split partition partition_name at ('20090301') into (partition par_200902, partition par_max) parallel 8;
