Reclaim tablespace

Posted on 2004-11-22
Medium Priority
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
  • 2
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12648910
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12648939
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

ID: 12649056
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

ID: 12649108
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 35

Accepted Solution

Mark Geerlings earned 500 total points
ID: 12649371
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.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

840 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