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
You can coalesce. Actually you don't need to worry about TEMP tablspace fragmentation untill unless the CONTENTS are TEMPORARY.
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?
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.
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 .
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!!!
DBA_FREE_SPACE shall be a usefule data dictionary view for this purpose
Click Here to Expand Forum to Full Width