-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
--
Please see the official
-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re
PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
--
Please see
PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mohammad
Rafiq
Sent: Wednesday, February 13, 2002 9:54 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
In many cases you will have to take RB segments offline
and drop them, then
re-create
Thanks a lot for ur help, Rafiq. I have acted as per ur recommendation
and asked the customer to try the delete job again.have not received
a feedback from them yet but sometimes they only revert back to me if they
have faced a problem again.if I do not hear anything by this evening,
I
Rafiq,
Just a small questionr the rollback segment extents in the rollback
segment tablespace de-allocated when the database is shut down ??
In that case, does the fragmentation remain when the database is restarted
or r the blocks coalesced automatically ??
Regards,
Samir
Samir Sarkar
Samir,
It is not coalesced automatically at shutdown and startup. It is to be
coalesced manually. It applies to any tablespace with pctincrease set as
0(zero). I have no idea of 9i but this is normal behaviour of ver 7.3.4 to
8.1.7...
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple
Samir,
Thanks..Due to lot of activities of rollback segment resulting in shrinkage
make rbs tablesapce fragmented hence contigous space becomes a
issue...Possible solution to set a good size of optsize of rbs to avoid
shrinkages or frequent coalesing of rbs tablespace / or any other tablespace
Thanks a lot, Rafiqthe output from ur query after running it on the
database
showed the rollback segment to be highly fragmented.the total extents
were
7490 and the extents coalesced were only 440. The initial and next extents
have been set at 409K which seems to me as quite small.
contiguous free space it try coalesce itself.
Alex Hillman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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
Hi, guys,
Just my $0.02, coalescing free space will NEVER eliminate fragmentation. In
many cases you will have to take RB segments offline and drop them, then
re-create. In the interim period you might want to have a big rollback
segment(or a few) in another tablespace.
Best,
Sergey
In many cases you will have to take RB segments offline and drop them, then
re-create
--Even in this case you have to coalesce rbs tablespace before
recreating rollback segments using that particular tablespace to make all
released extents as contigous...Besides it is a good practice to
Samir,
For a long term solution, it is better to reorg rbs tablespace and rollback
segments with correct sizing based on usage...
You may use following script to get better info about rollback segments
usage
1 set linesize 120
2 column name format A15
3 column status format A7
4
contiguous free space it try coalesce itself.
Alex Hillman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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
IN THIS CASE yes, but NOT BEFORE That was my point.
Thanks,
Best,
Sergey
-Original Message-
Sent: Wednesday, February 13, 2002 12:54 PM
To: Multiple recipients of list ORACLE-L
In many cases you will have to take RB segments offline and drop them, then
re-create
--Even in
Exactly. It depends pretty much on the application, how often it commits
transactions, segment sizes, of course, OLTP amount etc. You can't always
wait for SMON to wake up and do its job. The point is COALESCING alone won't
always help and you have to defrag. Another point is ONE thing is reading
Of Babich ,
Sergey
Sent: Wednesday, February 13, 2002 3:00 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Rollback Segment Problem
Exactly. It depends pretty much on the application, how often it commits
transactions, segment sizes, of course, OLTP amount etc. You can't always
wait
Here you are absolutely right...
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 13 Feb 2002 11:59:53 -0800
Exactly. It depends pretty much on the application, how often it commits
transactions, segment sizes, of course, OLTP
You never need to recreate the rollback segments (DROP and CREATE) unless
the rollback segment dies. THe rollback segment will die after approx 4M
(the number is not very accurate.. okay,, some number)transactions.
THis is the only case I need to drop and recreate them.
Best Regards,
K
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
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
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
26 matches
Mail list logo
