Reclaim tablespace

Posted on 2004-11-22
Last Modified: 2010-08-05
What is the command that will allow me to free space after I have deleted rows from a table?
Question by:xoxomos
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Do you mean: reduce the file size?

    alter datafile '<file_name>' resize <smaller size>;

      Deleting all the rows in a table may not be enough to allow a resize.  Resize will only work if there are no allocated blocks left around in the area of the datafile that you are reducing.

    another FYI:
      truncate table will reset the high water mark where delete * from table does not.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    After re-reading I have another comment:

    Oracle is not like Access in that you need to keep reducing file sizes just for the H#ll of it.  If the datafile was at a certain size:  it probably needs to be.  If autoextend is on and someone made a 1 time mistake and loaded 100 million rows ito a table that will only ever hold 1000 rows and the datafile grew because of it, then a resize is in order.

    On the other hand:  If you removed last months 50,000 rows and expect another 50,000 rows this month:  No resize is necessary.

    Author Comment

    With this particular job there needs to be a delete instead of a truncate.
    No, I don't want to rezise, i would just like to have it show up as free space for the tablespace.

    Author Comment

    Looks like maybe I cannot:

    Dear Tom,

    I deleted millions of rows in my database. In somewhere, I read that delete will
    not release data block space, that is, the deleted data are still occupying the
    data block. We have to use export/import to relcaim the space. It that true?

    If we want to purge data older than certain date, sucha as 6 months,
    periodically, what is the best strategy to do it?

    Thanks a lot for your earlier and this help!

    and we said...

    When an extent is allocated to a table (or index, etc) it belongs to that table
    until you

    o drop the table
    o truncate the table

    When you delete the data from the table -- the blocks will go onto the freelist
    for that table (assuming the amount of space on a block that was freed fell
    below the pctused).  These blocks will be used for subsequent inserts and
    updates into this table.

    When you delete data from the index -- if the block the index entry was on is
    now "empty" -- that block will go back onto the freelist to be used anywhere in
    the index struct.  Else that block stays where it is and data that would
    naturally go onto that block (because of where it is in the b*tree) will go

    Space is effectively reused when you delete.  Your database will not show any
    new free space in dba_free_space -- it will have more blocks on freelists and
    more empty holes in index structures.
    LVL 34

    Accepted Solution

    That is correct, Oracle does not have a simple "reclaim space after delete" command.  You do have some options though to get the sapce back:
    1. Export the table, truncate the table, then import the rows from the exported file.
    2. Create a new table as "select * from [current_table]", drop the current table, then rename the new table.  If you use this technique, make sure that you check for: grants, constraints, indexes and triggers on the current table before drop it, then create them on the new table.
    3. Partition the table by date range or range of some other value, then drop a partition when it is no longer needed.  If you use this techniue, you should also partition the index(es) to match the table, otherwise you must rebuild any non-partitioned index(es) if/when you drop a table partition.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video shows how to recover a database from a user managed backup

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now