Scripts - Range & Exchange Partition

Scripts:

/* Partition Table - If it is already exists then Drop the table*/
DROP TABLE RANGE_PARTITION;

/* Create range partition per fiscal year */
CREATE TABLE RANGE_PARTITION
(ROW_WID NUMBER,
MONTH_WID DATE)
PARTITION BY RANGE(MONTH_WID)
(PARTITION P_2015 VALUES LESS THAN(TO_DATE('01-06-2016','DD-MM-YYYY')),
PARTITION P_2016 VALUES LESS THAN(TO_DATE('01-06-2017','DD-MM-YYYY')),
PARTITION P_2017 VALUES LESS THAN(TO_DATE('01-06-2018','DD-MM-YYYY')),
PARTITION P_2018 VALUES LESS THAN(TO_DATE('01-06-2019','DD-MM-YYYY')),
PARTITION P_2019 VALUES LESS THAN(TO_DATE('01-06-2020','DD-MM-YYYY')),
PARTITION P_2020 VALUES LESS THAN(TO_DATE('01-06-2021','DD-MM-YYYY')),
PARTITION P_MAX VALUES LESS THAN(maxvalue));

/* Inserting records for all the partitions */
INSERT INTO RANGE_PARTITION
SELECT LEVEL ROW_WID,
TO_DATE('31-05-2015','DD-MM-YYYY')+LEVEL MONTH_WID
FROM DUAL
CONNECT BY LEVEL < = TO_DATE('31-05-2020','DD-MM-YYYY')-TO_DATE('31-05-2015','DD-MM-YYYY');


/* Retrieving partitioning records for the fiscal year 2019 */
SELECT *
FROM RANGE_PARTITION PARTITION(P_2019);


/* Truncating the Partition*/
ALTER TABLE RANGE_PARTITION TRUNCATE PARTITION P_2019;

/* Retrieving partitioning records for the fiscal year 2019 */
SELECT *
FROM RANGE_PARTITION PARTITION(P_2019);

/* Dropping table*/
DROP TABLE PARTITION_EXCHANGE;

/* Create a new table which hold all the records related to partition 2019 fiscal year*/
CREATE TABLE PARTITION_EXCHANGE
AS
SELECT LEVEL ROW_WID,
TO_DATE('31-05-2019','DD-MM-YYYY')+LEVEL MONTH_WID
FROM DUAL
CONNECT BY LEVEL < = TO_DATE('31-05-2020','DD-MM-YYYY')-TO_DATE('31-05-2019','DD-MM-YYYY');

/* Cross Check -  all records are populated as per my partition */
SELECT MIN(MONTH_WID) , MAX(MONTH_WID) FROM PARTITION_EXCHANGE;

/* Exchange Partition with table*/
ALTER TABLE RANGE_PARTITION EXCHANGE PARTITION P_2019 WITH TABLE PARTITION_EXCHANGE;

/* After exchange partition */
SELECT * FROM PARTITION_EXCHANGE;

/* Retrieving partitioning records for the fiscal year 2019 */
SELECT *
FROM RANGE_PARTITION PARTITION(P_2019);