Forum Stats

  • 3,752,495 Users
  • 2,250,509 Discussions
  • 7,867,853 Comments

Discussions

re-use space from record deleted

user495715
user495715 Member Posts: 10
edited Jan 10, 2017 12:38PM in General Database Discussions

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

Tagged:

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Jan 10, 2017 9:22AM
    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
    edited Jan 10, 2017 12:08PM
    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
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jan 10, 2017 12:12PM

    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
    jgarry Member Posts: 13,842
    edited Jan 10, 2017 12:38PM

    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.

This discussion has been closed.