Jerry, Thanks for sharing script. We might have better systems as timing was never be a issue. I just tested my script on 2 systems and it ran between 34.5 mseconds to 4.65 seconds...and on the basis of it is results we coalesce all such tablespaces. I prefer to keep pctincrease of tablespaces as 0(zero) and manually coalesing as and when it is required....
So have you ran your deletion job after coalesing or not? Regards Rafiq To: <[EMAIL PROTECTED]> CC: <[EMAIL PROTECTED]> Date: Tue, 12 Feb 2002 15:28:41 -0600 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 > MOHAMMAD RAFIQ _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq 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).
