DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Truncate Reuse storage

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Smile

    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,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    "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
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    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,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    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.

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Thanks,

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

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Oct 2001
    Posts
    83
    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






  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 .
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width