| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Recovering space after delete
On 5 Sep 1997 10:58:23 GMT, g.r.s.deisz_at_ptt-telecom.unisource.nl (g.r.s. deisz) wrote:
>In article <5uojbu$jml$1_at_news.enterprise.net>,
> "Oscar Ssentoogo" <impala_at_enterprise.net> wrote:
>>
>>We have large Oracle database tables.
>>We delete large amounts of records regardly, but the used tablespace area
>>seems to remain constant and not decrease after these deletes.
>
>I suppose that you also insert large amounts of records regularly? The space
>freed by deleting records will be used by new inserts.
>Maybe you can tune the pct_used parameter of your table: an oracle block will
>only be available for new inserts when the amount of space used in the block
>drops below pct_used. Setting pct_used too low will result in space wastage.
>Setting it too high, however, will result in frequent swapping of blocks to
>and from the free list, which may degrade performance.
>
>>Is there a way of recovering this space (i.e. can you compact the database
>>as in MS Access).
>
>You can export the data, delete the table and import it with the compress=yes
>option. Using the Enterprise Manager, this can be done by using the defragment
>option.
I could be wrong here, but I believe that this will only create an initial extent the size of the all extents used by the table. It will not free up unused space in any extents. This unused space will simply be availabe for the same table, it won't be released for use by other segments which use the same tablespace.
>
>>Or must you drop a tablespace file, delete this and recreate it to recover
>>the disk space?
>
>This is the most profound solution, I would see it as a last resort.
The only way to free up the space for use by other segments (that I can think of right now) would be to export the table, recreate it with a small initial extent and next extent (and maybe pctincrease of 0) and import with ignore=y. It will fragment the table, but will ensure that a lot of the unused space is freed up. But I don't think this is what you'd want to do anyway if you are doing subsequent inserts. But this would be a good thing to do for any indexes on the table as deletes do not free up any space but rather retains it and hastens index stagnation.
To respond, remove BITEME_SPAMMERS_ from my address
 |
 |