I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace. My partitioning range is one day and I have stored procedure that drops partitions older than 30 days fro each table. As for now (15 days from start of gathering data) my datafiles (both data and index) are growing steadily as expected. My question is when stored procedure drops first partition older than 30 days will that space be avaliable to new partitions or datafile wil continue to grow?

1 Answer 1


It will be available. Depending on your space management settings it can nearly fit the new partition entirely in the space or there might be some gaps, but the majority of space will be reused.

I would not expect that the grow stops immediately after 30 days depending on the fragmentations but it will eventually. Using uniform extends or at least a big minimal size for extends will help, especially if you expect large partitions.

When using LOBs retention might come into play as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.