-1

I have a table with list partitions on it. When i insert data i see that all the partitions have default initial size of 8 MB and I also noticed that when i truncate full table and check size it still shows 8MB for each partition even when they are empty.

enter image description here

Can someone help me understand what could be causing these paritions to have intial size of 8MB? Is there a way i can change this to use size based on partition size and not default to a value? And why is size not reclaimed or set back to 0 after truncating table?

I tried

TRUNCATE TABLE TABLENAME;

And

ALTER TABLE TABLENAME TRUNCATE PARTITION PNAME DROP STORAGE;
  • 1
    You have to actually resize the tablespace datafile itself, which might actually not be possible if there is data at the end of the file - you can check on DBA_EXTENTS. – Philᵀᴹ Jun 25 '14 at 14:46
0

You don't mention your version - but assuming 11.2.0.2 -> 11.2.0.4, see My Oracle Support doc ID 1295484.1

"Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later"

I won't copy and paste chunks of that doc here, but that's how it works in 11.2.0.2 and up. If you feel the need to change it, you can do so with by setting the hidden parameter _partition_large_extents to FALSE.

Though as with any hidden parameter change, I'd suggest you read up on it's consequences very carefully.

You can also override the default extent allocation per table as required by specifying the "INITIAL" clause in the "CREATE / ALTER / ADD PARTITION commands.

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.