How to change initial extent of an existing partition

oracleoracle-11g-r2partitioning

I'm having trouble with something that should be rather simple … I'm sure I'm missing something obvious.

I'm trying to change the initial extent of an existing table partition.

For some reason, I have an existing table with initial extents of various sizes, and I'm trying to realign them as I'm just doing some reorg and moving them about (DEV environment).

I'm working on Oracle 11.2.0.3.0

I have a table, range partitioned. Residing in a locally managed tablespace.
It was originally system managed, however, I'm trying to create a new tablespace using uniform extents. I've done that, and am trying to MOVE the objects over, however, I have a couple (empty) partitions which seem to want to have multiple extents (initial 8M, despite uniform of 1M). (oddly some partitions are fine with an initial of 1M ..)

I tried:

alter table my_table modify default attributes storage ( initial 1M next 1M );

that ran, but didn't seem to do anything. So I tried this:

alter table my_table modify default attributes for partition part101 storage ( initial 1M );

but that throws an exception:

ORA-14264: table is not partitioned by Composite Range method

How do I alter the INITIAL extent of a (range) partition ?

Best Answer

Even if its an empty partition, MOVE is the way to do it:

create table t1 (c1 number, c2 varchar2(100))
segment creation immediate
partition by range (c1)
(
  partition p1 values less than (10),
  partition p2 values less than (20)
);

SQL> select partition_name, initial_extent, next_extent
     from user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                          8388608     1048576
P2                          8388608     1048576

Default 8M/1M. Now move the partition:

SQL> alter table t1 move partition p1 storage (initial 65536 next 65536);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576

Modifying default attributes will have its effect only on partitions created after setting it:

SQL> alter table t1 modify default attributes storage ( initial 1M next 1M );

Table altered.

SQL> alter table t1 add partition p3 values less than (30);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576
P3                          1048576     1048576
Related Question