Rafiq, While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table.
select a.tablespace_name, count(a.tablespace_name) "ContinguousFreeBlocks" from dba_free_space a, dba_free_space b where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id and a.block_id = b.block_id + b.Blocks group by a.tablespace_name; If ContinguousFreeBlocks is > 10, I coalesce. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 > -----Original Message----- > From: Mohammad Rafiq [SMTP:[EMAIL PROTECTED]] > > Is your tablespace fragmented as it is not finding contingous extent .. > TRy to coalesce your subject tablespace and try... > > you can use following script to check whether coalesing is required or not.. > If percent is < 100 then coalesce it.. > > select substr(tablespace_name,1,10)TS_NAME,total_extents > "Total_Extnts",extents_coalesced,round(percent_extents_coalesced,0) > from dba_free_space_coalesced > order by tablespace_name > / > > HTH > Regards > Rafiq > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Whittle Jerome Contr NCI INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
