How to defrag temp tablespace? any one knows??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to defrag temp tablespace? any one knows??

  1. #1
    Join Date
    Oct 2000
    Posts
    90

    Talking

    If the FSFI is low (i.e.10) in the TEMP tablespace, that means there is fragmentation.
    My question is how do you fix fragmentation in TEMP tablespace


    dorothy.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You can coalesce. Actually you don't need to worry about TEMP tablspace fragmentation untill unless the CONTENTS are TEMPORARY.

  3. #3
    Join Date
    Oct 2000
    Posts
    90
    thanks sreddy. What do you mean "Actually you don't need to worry about TEMP tablspace fragmentation untill unless the CONTENTS are TEMPORARY".

    I thought Temp tablespace always contain temporary stuff? Could explain you point again?

  4. #4
    Join Date
    Oct 2000
    Posts
    80
    1.) You can and should assign every user's default temporary tablespace to a specific tablespace used only for temporary segments, tables, sorts, etc. If you do not do this for every user, sort operations from will occur in the system tablespace. Fragmentation is really a problem when mixing temporary and permanent objects in the same tablespace. Otherwise fragmentation should not be a concern for temporary tablespaces, as I hope will become clear below.
    2.) Calling a tablespace temporary does not restrict it from holding permanent objects. You must specifiy the tablespace type as either temporary or permanent when creating or altering the tablespace. The default is permanent.
    SELECT CONTENTS FROM DBA_TABLESPACES; to determine what type your temp tablespace is. You can not convert from perm. to temp. if it already contains any permanent segment.
    3.) If it ain't already of type temporary, move all permanent objects elswhere and
    ALTER TABLESPACE tablespace_name TEMPORARY;
    4.) It's best if INITIAL=NEXT and PCT_INCREASE=0 for the tablespace. Drop and recreate if necessary. This relates to the limited duration of temporary objects. Once their usefulness is ended, temporary objects are dropped and the extents allocated to them are returned to the tablespace for later use by other sorts or temporary operations. They are not deallocated. The next time any operation goes looking for space it will find an extent ready and waiting.
    5.) Set extent sizes to a value in the 2 to the n series. Also make sure they're an integer multiple of DB_FILE_MULTIBLOCK_READ_COUNT.
    5.) Set SORT_AREA_SIZE such that an integer multiple of this value will fit neatly into one extent plus one block for the segment header.
    6.) Forget fragmentation in temporary tablespace. The above actions should help considerably.
    John Doyle

  5. #5
    Join Date
    Jul 2000
    Posts
    119
    i have one more query related to the fragmentation !! . now if i want to find whether a tablespace is a fragmented on or not ,is there any other way than querying the dba_extents view ? i know from dba_extents view we can find tablespace wise fragmentation , just wanted to know any other means of doing the same !!!
    thanx in advance .

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi

    In this forum, last week, there was a very interesting discussion about fragmentation.i think you should look at it.in one of the replys was a site (i lost the url) where you could find some very interestin rading ,aterial!!!

  7. #7
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    DBA_FREE_SPACE shall be a usefule data dictionary view for this purpose

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