How to change initial extent of an existing partition

Question

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 ?

• EdStevens: How can you change the initial value of anything once that thing is already created? Of course the solution proposed gets you to your desired end-point, but it is not by changing, but by re-creating. One also wonders what actual problem this solves. If you are using locally-managed tablespaces, you really don't have worry about extent management. That's the beauty and purpose of LMT.
• Ditto: The problem is an existing object had an initial extent of 8M already .. (don't know why, don't really care), and I'm trying to move it into a locally managed TBS of 1M uniform .. so I just want 1M initial ... and for whatever reason it's trying to keep the 8M initial , thereby 8 extents ... changing the initial allows it to recognize that, and it immediately dropped down to 1 extent .. so it's the perfect solution ...

Answer 1

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