1

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 !

1
  • 1
    If 5MB/partition is too big for you, I'd question the need for Partioning. Don't forget about deffered segment creation. Sep 28, 2020 at 21:58

1 Answer 1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.