re-use space from record deleted

user495715

Hi all,

I've a general question on how Oracle manage segmente free after a deletion.

If I delete all records from an historical partition from a table without truncating or dropping the partition, the table can re-use that free space for new records that will belong to a new partition?

My database version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Thanks in advance,

Samuel

John Thorton

user495715 wrote:Hi all,I've a general question on how Oracle manage segmente free after a deletion.If I delete all records from an historical partition from a table without truncating or dropping the partition, the table can re-use that free space for new records that will belong to a new partition?My database version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0Thanks in advance,Samuel

Yes, no, it depends upon how table is partitioned,  nature of new data, and/or what DDL you issue against table.

Unknown

I've a general question on how Oracle manage segmente free after a deletion.

There won't be any free 'segments' after a deletion. Segments are objects related to a table/partition - not rows.

If I delete all records from an historical partition from a table without truncating or dropping the partition, the table can re-use that free space for new records that will belong to a new partition?

Correct - the space will still be allocated to that table. So any new partition that uses the same tablespace CAN reuse that space. Note the word 'can' - if you do a direct load the data will be loaded above the high water mark to newly allocated space and NONE of that empty, free, existing space will be used at all.

That is often the cause of significant growth in ETL environments.

1. delete existing data

2. direct load new data

3. repeat the above every day/week/ etc

For that above scenario each days load will use new allocations and will NEVER reuse the old space. You need to truncate the table/partition (using the default DROP STORAGE clause) to deallocate the space.

And new partitions can have their own storage clause and use a different tablespace so that other spaced won't be used either.

Mark D Powell

Samuel, the answer is 'no' in your description.  Once allocated to a partition the extent blocks can only be used for rows assigned to that specific partition.  Space for a new partition will have to be allocated from the tablespace the partition is assigned to.  Now, if the partition was dropped or truncated then space returned to the tablespace can be used to allocate the new partition or for new extents required by normal insert extent management.  But as you stated your situation the space is not available for use by a new partition.

- -

HTH -- Mark D Powell --

jgarry

Samuel:

Please remember that tables have rows.  The word "record" may refer to something different, as a "customer record" may, for example, refer to a parent/child relationship of customer attributes, plus multiple address rows.  This distinction can become important when loading flat data into a relational system, or attempting to translate business rules into a relational database.

When you ask about "delete all" you might want to give specific example SQL commands, as there could be some different ways to delete everything from a partition, causing an ambiguity not quite resolved by specifying truncate or drop partition.  I read your question the same way Mark did, but John's answer could spotlight this ambiguity, for example, deleting data via an exchange partition.  And no one mentioned global index space.