1

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 ?

  • 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. – EdStevens Mar 11 '16 at 23:31
  • 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 ... – Ditto Mar 12 '16 at 21:56
3

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
| improve this answer | |

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.