Menu
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?
360
The simplest way to reclaim space from the undo tablespace is to create a new undo tablespace, make it the database undo tablespace and drop the old tablespace. In the following example I've used autoextend, but you may wish to remove this if you want manual control over the datafile size.
When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is default), it will release the space back to tablespace.
Answer: You can reclaim the space reserved for a LOB segment in several ways, the nost comon beuibng a reorganization of the table (using dbms_redefinition or data pump). If your in-line LOB columns are frequently deleted, consider using BFILE.
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?
200
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...
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
