Initial extent size when converting to partitioned table

Question

Working in an Oracle 19c database on Linux x86/64 trying to convert non-partitioned table to partitioned table.

Since Oracle12, alter table modify partition has been available to convert non-partitioned tables to partitioned tables. I have a non-partitioned table that has the initial_extent size set to 544MB. This is way too large. The actual first extent is 8MB.

When I use the alter table modify partition, I end up with 90 partitions all with an initial extent of 544MB. this results in the tripling of the size of the table. Many partitions are 90% plus empty.

Question is this. How to set initial partition extent size to a reasonable value during this process?

Using alter table move to recreate the table with new storage is not really a useful step as some of these tables are terabyte plus in size. Perhaps I'm missing it but I can't find a way to change or specify the initial extent size. I can alter table move partition after the fact and rebuild all the indexes but that has the same issues as alter table move prior to the partitioning.

ALTER TABLE mytable MODIFY
Partition by range (mydatecol)
interval ( NUMTOYMINTERVAL(1,'MONTH') )
(
partition p1308 values less than (to_date('01-SEP-2013','dd-mon-yyyy'))
)
-- Below line will raise "ORA-02203: INITIAL storage options not allowed"
-- storage( initial 1m)
UPDATE INDEXES ONLINE;

Spotlight

Answer 1

You can specify the storage clause for each partition:

create table t (
  c1 int
) storage ( initial 10m );

insert into t 
with rws as (
  select level x from dual
  connect by level <= 5
)
  select x from rws;
commit;

alter table t modify 
  partition by range ( c1 ) 
  interval ( 1 )  (
    partition p1 values less than ( 2 ) 
      storage ( initial 8k ),
    partition p2 values less than ( 3 ) 
      storage ( initial 64k ),
    partition p3 values less than ( 4 ) 
      storage ( initial 128k )
  );
  
select partition_name, initial_extent 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME   INITIAL_EXTENT   
P1                        16384 
P2                        65536 
P3                       131072 
SYS_P42018             10485760 
SYS_P42019             10485760 

Note you have to do this for all the partitions. The auto-created interval partitions will have the same initial as the original table. Any new partitions will also have this same initial size. You can change this with the modify default attributes clause:

insert into t values ( 6 );

alter table t modify 
  default attributes storage ( initial 16k );

insert into t values ( 7 );  
  
select partition_name, initial_extent 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME   INITIAL_EXTENT   
P1                        16384 
P2                        65536 
P3                       131072 
SYS_P42018             10485760 
SYS_P42019             10485760 
SYS_P42020             10485760 
SYS_P42021                16384 

Delitescent

Interesting, this might work. requires all partitions to be noted in the modify command is the only drawback. – Spotlight

Correct; you could always write a script to generate alter table statement – Delitescent