Truncate Reuse storage

[samdba]

Hi,

Pl. explain me exact meaning and advantage of Reuse storage with Truncate table.

After we do truncate, does the next extent value change?

Thanks,

[grjohnson]

"REUSE STORAGE option specifies that all space currently allocated for the table or cluster remain allocated to it.
The extents are not freed and already allocated ones are used for future inserts." Oracle, Chandra Adithya

[samdba]

Thanks for your reply.

What is the Pros and Cons of using "Reuse Storage" Option.

As I asked in my original post, will it change the NEXT EXTENT of that table?

Thanks,

[gpsingh]

truncate , default is drop storage.
which lowers the HWM and releases all the extents beyond initial extent.

truncate with reuse storage ,lowers the HWM but does not release the space used , this decreases the
job for Oracle to do some dictionary or bitmap maintenance.

truncate command by default resets the next extent equal to the extent size of last used extent.

[samdba]

Thanks,

When and where to use Drop and Reuse storage. I mean how both affects the performance.

[noor]

Hello,

You will have to use the REUSE STORAGE if :

- You have to delete all records of a table in order to insert new records (same volume)... I think that SQL*LOADER use that when you opt for the option TRUNCATE in ctl file.

- You have to drop a very big table. You start by TRUNCATE command with REUSE STORAGE, then, when you have free ressources (in the night for example), you will use :
ALTER TABLE table_name deallocate unused keep xxxxM;
...
ALTER TABLE table_name deallocate unused keep xxxxM;
every time you deallocate some space without consuming all the available ressource...

Hope this helps

[jmodic]

Originally posted by samdba
Thanks,

When and where to use Drop and Reuse storage. I mean how both affects the performance.

How they affect performance? No matter which TRUNCATE option you choose, the performace of any query on that table will be more than excelent immediatelly after the trunacte .