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?
Oracle 11g, Will droping partition reuse free space on bigfile tablespace
oracle-11gpartitioning
Related Question
- Oracle 11g r2: Cannot create partition on timestamp field
- Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)
- Oracle 11g – drop maxvalue partition, keep data
- Sql-server – the impact on the Cost-Based Optimizer (CBO) when implementing ISCSI SAN solid-state drives
- Sql-server – the best choose for PK in Partitioned Table
- MYSQL 5.7 Partition a table based on partition key of another related table
Best Answer
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.