CODEWITHSUNDEEP
oracle-database
user1127214
user1127214

Reputation: 3177

reclaim the space after a large delete in oracle

In my application we are uploaded huge data.if any wrongly uploaded data then we can delete the data.So,whatever data deleted that space when will reclaim the data?.is there any impact on performance Is there anyway to reclaim the space after a large delete in oracle?.How to reclaim the space?

Upvotes: 14

Views: 97873

Answers (4)

Ollie
Ollie

Reputation: 17568

You need to research the High Water Mark (HWM).

Basically it is the maximum number of blocks that has ever been used by a specific table, if you load a large volume of data them you may well increase the HWM, deleting those records does not then reduce the HWM.

Here is a great article on how to adjust the HWM and if, once you understand it, you think it may be affecting your environment then use the tips included to reduce your HWM.

Hope it helps...

Upvotes: 3

Eagle
Eagle

Reputation: 1074

I know it is an old question, but for future references:

  • DELETE empty the table but space is already "locked" for future fillings
  • TRUNCATE TABLE do the trick (you need to disable all foreign keys referencing the table in order to work).

Upvotes: 0

anon
anon

Reputation:

In Oracle, deleting data does not automatically reclaim disk space. The database will retain its storage until you do something administrative to the tablespace. The expectation of this behavior is that if you needed the storage at one time, you will likely need it again and it would therefore be more efficient to simply keep the allocation.

As far as performance impact, less data to process will generally make queries go faster. :)

To reclaim the space, you have a few choices. This article from ORACLE-BASE has a pretty comprehensive look at this situation.

Also, why would you insert data, then determine it is "bad" to then delete it? Wouldn't you be better off avoiding putting the data in from the beginning?

Upvotes: 17

SNAG
SNAG

Reputation: 2113

There is no way to reclaim space after delete. but if after the delete the size of the remaining data is relatively small then create a new table with the data, drop the old table and rename the new table to the old name

Upvotes: 0

Related Questions