TRUNCATE and NEXT_EXTENT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: TRUNCATE and NEXT_EXTENT

  1. #1
    Join Date
    Jul 2000
    Posts
    521
    Here is what happened :

    A table was mistakenly specified to have 2K next rather than 2M.
    I started a data load and got ORA-01631-Max extents reached.
    Checked the things and changed the NEXT to 2M. Successfully.
    Truncated the table and restarted the data load only to get the ORA-01631 error again !!
    Checked the NEXT for the table and it was back to 2K !!

    When I tested it (806/817), I noticed that when the table is occupying extents equal to MAXEXTENTS value and you change its NEXT and issue a TRUNCATE statement, the NEXT gets the old value again !! TRUNCATE doesn't do anything to NEXT value if the table is NOT occupying MAXEXTENTS extents.

    Checked on MetaLink and there is something related to this there (Bug#1546221).

    Accepted that this is a documented "feature" (as mentioned in the MetaLink note), but why in the world should TRUNCATE touch the storage values of a table ?
    svk

  2. #2
    Join Date
    Nov 2001
    Posts
    335

    Truncate has to touch storage settings for a table because the way it works. It wipes out information about all extents that belong to the table except the initial one, or another words resets high water mark. This is a reason why it is very fast.
    However, if you fo not want it happend you could say
    This will keep current storage settings for a table unchanged.Not sure if it takes longer than an usual truncate though.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    My theory is

    U have reached maxextents, so table cannot be extebded beyond that
    Changing next extent is in effect only for the extents beyond the last used but since in your
    case there is no extent beyond last extent , the next extent does not get into effect.

    So it is not truncate which is causing it but the fact that next extent change only affects the extents
    beyond the last extent.

    Try this
    after u reach maxextents, truncate and then change next extent and re import.
    It should work.

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    BV1963,

    I guess, the important part of you post has got truncated out....Can you post again please ?
    svk

  5. #5
    Join Date
    Nov 2001
    Posts
    335
    Truncate has to touch storage settings for a table because the way it works. It wipes out information about all extents that belong to the table except the initial one, or another words resets high water mark. This is a reason why it is very fast.
    However, if you fo not want it happend you could say

    TRUNCATE TABLE T1 REUSE STORAGE;




    This will keep current storage settings for a table unchanged.Not sure if it takes longer than an usual truncate though.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

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