Skip to Main Content
  • Questions
  • Default storage settings for partitions

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Harry.

Asked: October 10, 2022 - 6:27 am UTC

Last updated: February 18, 2026 - 3:24 pm UTC

Version: 19.14

Viewed 10K+ times! This question is

You Asked

How can you make an oracle table, index or partition 'lose' its storage definition and revert to tablespace defaults...

I have a 'legacy' table in production that was built with storage parameters inherited from the 'consolidate storage' option on the old export utility (exp). It has INITIAL set to 2 GB.
The tablespace has Segment Management set to Auto and Extent Management set to Local with Auto Allocate.

I want to convert the table to partitions, but all partitions get built with INITIAL set to 2 GB.
My solution, so far, is to first MOVE the table with a STORAGE clause with INITIAL set to 10M.
However, I prefer to build all new objects, by using the defaults from the tablespace...

Is there way to do this?

Or is the only option to re-create the object with the INITIAl size set to the same value as the tablespace default?

Thanks in advance...

and Chris said...

I don't know of a command to reset these to the defaults.

Note that the default initial for partitions is 8Mb, regardless of the tablespace settings. You can override this for new partitions by changing the table's default attributes.

I'm also struggling to understand what the issue is here. It's easy enough to write a script to move all tables/partitions, setting the initial value as needed. I don't see how re-creating these is better as there's the risk of losing indexes, triggers, grants, etc. in the process.

That said, as you're on 19c there is a workaround of sorts. You can change the partitioning scheme for a table. So you could alter the table to use a different partition method, then back to the one you want. This will pick up the default (8Mb unless you've overridden it) in the process.

For example:

create table t (
  c1 int
) partition by range ( c1 ) (
    partition p1 values less than ( 10 ) storage ( initial 1k ),
    partition p2 values less than ( 20 ) storage ( initial 10k ),
    partition p3 values less than ( 30 ) storage ( initial 100k ),
    partition p4 values less than ( 40 ) storage ( initial 1000k )
  );
  
select partition_name, initial_extent
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME       INITIAL_EXTENT    
P1                            16384 
P2                            16384 
P3                           106496 
P4                          1024000 
  
alter table t 
  modify partition by hash ( c1 )
  partitions 1;

select partition_name, initial_extent
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME       INITIAL_EXTENT    
SYS_P45145                  8388608 

alter table t 
  modify default attributes
  storage ( initial 10m );

alter table t 
  modify partition by range ( c1 ) (
    partition p1 values less than ( 10 ),
    partition p2 values less than ( 20 ),
    partition p3 values less than ( 30 ),
    partition p4 values less than ( 40 )
  );
  
select partition_name, initial_extent
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME       INITIAL_EXTENT    
P1                         10485760 
P2                         10485760 
P3                         10485760 
P4                         10485760 


Again, I don't see how this is any better/easier than moving the partitions as needed.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Amend STORAGE config in composite partitioned table

Narendra, February 10, 2026 - 2:09 pm UTC

Hello Chris/Connor,

How would we achieve any changes to STORAGE clause for a composite partitioned table but in a gradual manner instead of a "Big Bang" approach described above that deals with ALL partitions in one go?
Ideally, I would like to apply the STORAGE clause changes to oner or few sub partitions at a time.

In a non-composite partitioned table, I am able to use ALTER TABLE...MOVE PARTITION to amend STORAGE clause attributes for individual partitions but that doesn't work for sub partitions.
It fails with "ORA-14160: this physical attribute may not be specified for a table subpartition" if I try to specify STORAGE attributes while using ALTER TABLE...MOVE SUBPARTITION and it fails with "ORA-14257: cannot move a partition which is a composite partition" if I try to move a partition in a composite partitioned table.

Thanks in advance
Chris Saxon
February 17, 2026 - 5:17 pm UTC

There is another trick you can use: exchange the subpartition with a table that has the extent sizes you want:

create table t (
  c1 int, c2 int
) partition by range ( c1 ) 
  subpartition by list ( c2 ) 
  subpartition template ( 
    subpartition sp1 values ( 1 ),
    subpartition sp2 values ( 2 )
  ) (
    partition p1 values less than ( 10 ) storage ( initial 32k ),
    partition p2 values less than ( 20 ) storage ( initial 32k )
  );

create table exch  (
  c1 int, c2 int
) storage ( initial 128k );

select partition_name, subpartition_name, initial_extent
from   user_tab_subpartitions;

PARTITION_NAME    SUBPARTITION_NAME    INITIAL_EXTENT    
P1                P1_SP1                           32768 
P1                P1_SP2                           32768 
P2                P2_SP1                           32768 
P2                P2_SP2                           32768 

alter table t 
  exchange subpartition p1_sp1 
  with table exch;

select partition_name, subpartition_name, initial_extent
from   user_tab_subpartitions;

PARTITION_NAME    SUBPARTITION_NAME    INITIAL_EXTENT    
P1                P1_SP1                          131072 
P1                P1_SP2                           32768 
P2                P2_SP1                           32768 
P2                P2_SP2                           32768 


Of course, this will also swap the data. So this only works with empty or static subpartitions so you can load the data into the other table for exchange first.

Re: Amend STORAGE config in composite partitioned table

Narendra, February 18, 2026 - 12:49 pm UTC

Thank you Chris for your response.
Yes EXCHANGE PARTITION is a way forward.
However, as you have mentioned, it means there is no ONLINE option.
Would you know if there is a logical reason why amending STORAGE clause for sub partitions, is not available just like it is available with a simple ALTER TABLE...MOVE and ALTER TABLE...MOVE PARTITION in case of non-partitioned tables and partitioned tables respectively?
Is that just a limitation or sub-optimal feature?

Thanks in advance
Chris Saxon
February 18, 2026 - 3:24 pm UTC

I don't know, I suspect it's just down to priorities. If this is important to you, raise an ER.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database