Reclaiming unused space 3

[Ant0]

Hi there

Could you tell me the best way to reclaim unused space in tables. Currently our process is to BCP the table out, drop the table, create the table, BCP the data in and create the indexes?

I have tried searching for an artical on this, but am struggling to find anything concrete.

Thanks

 

[SantaMufasa]

Yes, Ant, I can confirm that the fastest, bestest, coolest way to reclaim space for a table is:

alter table <owner>.<tablename> move parallel nologging;

This recreates that table (in the same tablespace), squeezing out all "Swiss Cheese" in the table. It is lightning fast...much faster than any alternative I have ever seen in my 18 years using Oracle. (BTW, this technique won't work for tables with LONG columns...for that you will still need to export/import.)

For indexes, you can do something similar:

alter index <owner>.<indexname> rebuild parallel;

Let us know of your satisfaction with these suggestions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[LKBrwnDBA]

Alternatively, if your table is not to much "fragmented", you can use:

ALTER TABLE MyTable DEALLOCATE UNUSED KEEP <x>M;

Hint: Set the value of <x>M less than initial extent to release space up to high water mark.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Ant0]

My tables are not parallel, but the deallocate is good for me. Is this as good as exporting, dropping, creating, and importing the table?

 

[LKBrwnDBA]

No, deallocate is not as good as exp/imp because empty blocks under the HWM are not released.

exp/imp will also reorganize the table (as will move and re-build).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

My tables are not parallel

...and neither are anyone else's. The "PARALLEL" option in the MOVE and REBUILD commands does not refer to a characteristic about your table or index, but rather that if you have multiple processors, that you permit Oracle to use them in PARALLEL to improve the speed of the activity.

So, your fastest way to eliminate Swiss Cheese from your objects is to MOVE (tables) or REBUILD (indexes).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[dbalearner]

Also as I understand you need to have enough space in the tablesapce to do table <owner>.<tablename> move parallel nologging;

The parallel basically refers to the number of worker proceses configured to allow this table move to be done faster. However, a precaution (IMO) would be that if the table is large and a large number of worker processes have been configured, there could be I/O saturation at the disk level so it is a good idea to do this maintenance work at a quiet time.

Additionally for indexes you can use

ALTER INDEX owner.index_name COALESCE;

This will defrag and free up unused index blocks. Both parallel operations and partitioned indexes are supported. This does not require additional space in the tablespace.

Hope this helps

 

[LKBrwnDBA]

Warning: When executing a CREATE TABLE, ALTER..MOVE or REBUILD in parallel, Oracle will allocate a full initial extent for each parallel process.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb