Toad World® Forums

physical attributes for parttions


#1

Hi,

I have a table with some partitions with a default storage size and I want to increase the storage size of the partition for the future partitions.

How can I do that. Can you suggest.

Thanks,

Tulasi


#2

Hi,

In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment. According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless. As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.

Hidden parameter _partition_large_extents was introduced in 11.2.0.2 to enable large extent allocation for partitioned tables if created in auto allocate locally managed tablespaces with default extent size. The default value for the parameter is TRUE since it was introduced (up to 12.1.0.2, the latest checked). This hidden parameter supersedes parameter cell_partition_large_extents which was introduced in 11.1 and was covering both tables and indexes.

How to Achieve the Pre-11.2.0.2 Behaviour

If the user explicitly sets the parameter _partition_large_extents to FALSE (either at session or at instance level), then the pre-11.2.0.2 behavior (of starting from 64k extent size) will be restored.

The user can also override the default extent allocation on a per statement basis by specifying INITIAL extent in the CREATE TABLE … / ALTER TABLE … ADD PARTITION … command.

Hope this helps.
Cheers
Damir Vadas