RE: Rollback Segment Problem

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.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Mohammad Rafiq [SMTP:rafiq9857_at_hotmail.com]
>
> 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: Jerome.Whittle_at_scott.af.mil

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: ListGuru_at_fatcity.com (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).