Monday, February 20, 2017

How to split an overflow partition using ALTER TABLE .... SPLIT PARTITION

For a range partitioned table:

ALTER TABLE SCOTT.EMPLOYEES
SPLIT PARTITION START_DATE_MAX AT (TO_DATE('2014-03-01', 'YYYY-MM-DD')) 
INTO (
      PARTITION START_DATE_FEB_2014 TABLESPACE ACTIVE_DATA,
      PARTITION START_DATE_MAX TABLESPACE TOOLS
     );

For a list-partitioned table:
ALTER TABLE received_documents
SPLIT PARTITION OTHERS
VALUES ('EAST','WEST','NORTH','SOUTH')
INTO (PARTITION NON_EU_CITIZEN,
      PARTITION OTHERS
) UPDATE INDEXES PARALLEL 8;

See this post for an example of splitting a default subpartition.

No comments:

Post a Comment