Add Partition

1. Create separate tablespace for new partition

CREATE BIGFILE TABLESPACE CM_XT039_P2016JAN DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

2. Add partition using split operation on MAXVALUE Partition

ALTER TABLE CISADM.CI_TD_ENTRY SPLIT PARTITION PMAX AT (TO_DATE('2016-02-01 00:00:01','SYYYY-MM-DD HH24:MI:SS'))

INTO

(

PARTITION P2016JAN TABLESPACE CM_XT039_P2016JAN, PARTITION PMAX

)

UPDATE INDEXES;

In case table contains LOBS like F1_SYNC_REQ_IN, there will be additional statement in split partition DDL indicating tablespace on which LOB should go.

ALTER TABLE CISADM.F1_SYNC_REQ_IN SPLIT PARTITION PMAX AT (TO_DATE('2016-02-01 00:00:01','SYYYY-MM-DD HH24:MI:SS'))

INTO

(

PARTITION P2016JAN TABLESPACE CM_F1T191_P2016JAN

LOB(BO_DATA_AREA, POST_TRN_BO_DATA_AREA, PRE_TRN_FIN_BO_DATA_AREA, PRE_TRN_INIT_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_F1T191_P2016JAN )

PARTITION PMAX

)

UPDATE INDEXES;

3. Enable advanced compression after SPLIT partition as it will disable the compression.

ALTER TABLE CISADM.CI_TD_SRTKEY ROW STORE COMPRESS ADVANCED;

ALTER TABLE CISADM.CI_TD_MSG_PARM ROW STORE COMPRESS ADVANCED;

ALTER TABLE CISADM.CI_TD_DRLKEY ROW STORE COMPRESS ADVANCED;

ALTER TABLE CISADM.CI_TD_ENTRY_CHA ROW STORE COMPRESS ADVANCED;

ALTER TABLE CISADM.CI_TD_LOG ROW STORE COMPRESS ADVANCED;