RE: Rollback Segment Problem

No..specially when pctincrease of tablespace set as 0(zero)...

Regards

Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tue, 12 Feb 2002 15:08:27 -0800

Why would you need coalesce at all?. My understanding that if Oracle cannot find contiguous free space it try coalesce itself.

Alex Hillman

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Mohammad
> Rafiq
> Sent: Tuesday, February 12, 2002 4:53 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Rollback Segment Problem
>
>
> 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: <ORACLE-L_at_fatcity.com>
> CC: <rafiq9857_at_hotmail.com>
> 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.
> 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
> >
>
>
>
>
> 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: rafiq9857_at_hotmail.com

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).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Alex Hillman
   INET: ahillman_at_erols.com

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).




MOHAMMAD RAFIQ


_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

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).