Encountered a strange setup wherein a table was created as partitioned but only with a max partition resulting in basically a single partition table. Extract of the ORIGINAL DDL was as follows:
CREATE TABLE “SCHEMA”.”TABLE_NAME” ( "VERSION_NUM" VARCHAR2(5 BYTE), . . . "PARTITION_COLUMN" DATE, . . (PARTITION "PARTITION_MAX" TABLESPACE "DE_SUSP_IND_001" ) ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TABLE_DAT_001" PARTITION BY RANGE ("PARTITION_COLUMN") (PARTITION "PARTITION_MAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE ENABLE ROW MOVEMENT ;
I needed to split this into two partitions such that all ros with a value less than 2017-11-12 in a separate partition. I split the partitions as shown:
ALTER TABLE “SCHEMA”.”TABLE_NAME” SPLIT PARTITION "PARTITION_MAX" AT (TO_DATE('2017-11-12','YYYY-MM-DD')) INTO (PARTITION "PARTITION_20171111", PARTITION "PARTITION_MAX") UPDATE INDEXES PARALLEL ;
The above resulted in two partitions,
PARTITION "PARTITION_20171111" VALUES LESS THAN (TO_DATE(' 2017-11-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
And
PARTITION "PARTITION_MAX" VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE
After this was complete, I gathered statistics to check that the row count in the partitions matched the actual distribution of data.