How to change the default initial and next for new partitions in oracle

Question

This is kind of a follow up on this question : How to change initial extent of an existing partition

I have a table that was created with an initial extent of 8M for new partitions and this takes too much space. Using the answer above I am able to resize already created partitions to something that is OK, but newly created partitions still get an initial extent of 8M. Is it possible to change this without re-creating the table ?

Edit : my table looks like this :

 CREATE TABLE MY_EVENTS (
 EVENT_ID NUMBER NOT NULL,
 EVENT_DATE DATE NOT NULL,
 DESCRIPTION VARCHAR2(2000 BYTE),
 CONSTRAINT TAUDIT_TRAIL_STA_PK PRIMARY KEY (EVENT_ID) USING INDEX TABLESPACE USR_D1 STORAGE (INITIAL 64 K
 NEXT 1 M
 MAXEXTENTS UNLIMITED)
 )
 TABLESPACE USR_D1
 PARTITION BY RANGE (EVENT_DATE)
 INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
 (
 PARTITION P_FIRST VALUES LESS THAN (DATE'2013-06-01'),
 PARTITION VALUES LESS THAN (DATE'2013-10-23')
 );

Edit 2

This is what I have been trying :

ALTER TABLE MY_EVENTS
 MODIFY DEFAULT ATTRIBUTES FOR PARTITION P_FIRST INITIAL 65536;

but I keep getting this error :

ORA-14121: MODIFY DEFAULT ATTRIBUTES may not be combined with other operations

Also, I am using Oracle 12.1

Thanks !

• Michael Kutz: If 5MB/partition is too big for you, I'd question the need for Partioning. Don't forget about deffered segment creation.

Answer 1

Actually the "FOR PARTITION" part of my query was the issue.

This worked :

ALTER TABLE MY_EVENTS MODIFY DEFAULT ATTRIBUTES 
STORAGE (INITIAL 65536 NEXT 65536);