Oracle Interval Partitions
31 May 2020
NUMTODSINTERVAL
to partition by hour. In this case, a new partition is created with 6 hour interval.
CREATE TABLE SALES_ORDER_BY_HOUR
(
EXTRACT_TM DATE NOT NULL,
CUSTOMER_ID VARCHAR2(20) NOT NULL,
SO_ID VARCHR2(20) NOT NULL,
ORDER_DT DATE,
PRC_AMT NUMBER,
TOT_QTY NUMBER(5),
UPDATE_DT DATE
)
PARTITION BY RANGE (extract_tm)
INTERVAL(NUMTODSINTERVAL(6, 'HOUR'))
(
PARTITION SOBH_MIN_PART VALUES LESS THAN
(TO_DATE('01-07-2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
);
NUMTOYMINTERVAL
to partition by month. In this case, a new partition is created with 1 month interval.
CREATE TABLE SALES_ORDER_BY_MONTH
(
EXTRACT_TM DATE NOT NULL,
CUSTOMER_ID VARCHAR2(20) NOT NULL,
SO_ID VARCHR2(20) NOT NULL,
ORDER_DT DATE,
PRC_AMT NUMBER,
TOT_QTY NUMBER(5),
UPDATE_DT DATE
)
PARTITION BY RANGE (extract_tm)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION SOBH_MIN_PART VALUES LESS THAN
(TO_DATE('01-07-2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
);
CREATE TABLE TEST_RANGE_NUM_PARTS
(
REC_ID NUMBER (10),
COL1 NUMBER,
COL2 NUMBER,
UPDT_TS DATE DEFAULT SYSDATE
)
TABLESPACE USERS
PARTITION BY RANGE
(REC_ID)
INTERVAL ( 25000 )
( PARTITION TRNP_MIN_PART
VALUES LESS THAN (25000)
LOGGING
TABLESPACE USERS
)
MONITORING;