Reclaim tablespace

What is the command that will allow me to free space after I have deleted rows from a table?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
xoxomosAuthor Commented:
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.
xoxomosAuthor Commented:
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.
Mark GeerlingsDatabase AdministratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.