Re: Rollback error? (ScreenShot Included) - Oracle archive error.jpg (0/1)

I'd second everything that Mark posted, with one small exception: I never set, nor would I recommend a full-time DBA to set, OPTIMAL. Optimal cause rollback segment shrinkage automatically, as transactions cross extent boundaries. That means the shrinkage takes place as new transactions are seeking to acquire new rollback blocks.... ie, it happens just exactly when you *don't* want it to happen.

Instead, I'd recommend creating rollbacks just as Mark says, but without OPTIMAL. Then create a cron job (or an AT script on NT) which fires up server manager and issues the command 'alter rollback segment BLAH shrink to 6M' (or whatever starting size you've decided on). Manual shrinking of the segments in this way can be scheduled for the quietest part of the day, and won't interfere with any OLTP-type activity going on, as a result.

It does mean you have to sit back and accept rollback segments ballooning in size during the working day (because of blocking transactions -they should of course already be big enough to accomodate the normal size of transactions). And that does mean additional storage requirements. But disk space is cheap, and performance is hard to come by.

Ultimately, it's whatever strategy suits you and your environment best, so Mark's not wrong... it's just that I wouldn't do it.

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================


<xmark.powell_at_eds.com.x> wrote in message
news:9e38i6$4eq$1_at_news.netmar.com...


> In article <9hu7gto41fre5incpcunn7eo69e5tr2i3f_at_4ax.com>, Alex Skolnick

> <ak_at_e-brag.com> writes:

> >Hi All,

> >

> >I am receiving the following error when I attempt to ADD columns to a

> >table (the table is NOT being accessed):

> >

> >ORA-01562 failed to extend rollback segment number

> >

> >technet.oracle.com mentions that this error message is usually

> >followed by another, but I don't receive a followup message.

> >

> >I am using SQL Plus to issue an  ALTER command and after a few seconds

> >NT opens a does window with a warning messages about my needing to

> >archive. (View the  ATTACHED .JPG for the actual message).  After

> >about 3 minutes I receive the ORA-01562 error.

> >

> >I tried creating a rollback segment for the tablespace, but that

> >didn't fix the problem.  I am really in need of some help on this one.

> >

> >Operating environment:

> >Winnt SP5

> >Oracle 8

> >Over 4 gigs of drive space available.

> >

> >Any and all help would be greatly appreciated.

>

> The jpeg file is not avaiable through my news service and even if it was I

> wouldn't open it.  You should just cut and paste the error messages.

>

> The 1562 says that Oracle was unable to find the space to extend the

 indicated


> rollback segment.  This normally happens when the rbs tablespace is full,

> which in turn most often happens when the optimal setting has not been set

 on


> the rbs segments so that unused but allocated extents are freed to make

 them


> available to other segments.

>

> 1 - All rbs segments, except the system rbs segment, should have been

 created


> in a tablespace dedicated to holding rollback segments and nothing else.

> They should not be in the system tablespace.

>

> select segment_name, tablespace_name, status

> from   sys.dba_rollback_segs;

>

> Alter any rbs segment that does not meet these rules offline and drop it

 then


> recreate it in the correct tablespace.  When you create the segments set

 the


> initial = next and set minextents greater than the default of 2.  You

 should


> set optimal to minextents * next.

>

> create rollback segment roll01

> tablespace rbs

> storage (initial 512k next 512k minextents 10 maxextents 50 optimal 1M)

>

> These numbers are for example purposes only.

>

> maxentents X next = largest transaction (amount of data that can be

 changed


> with only one commit at end) that your system will support without

 resorting


> to use of a special batch/maintenance/large transaction rbs segment

>

> minextents X next = minimum amount of data that your system will hold for

 a


> process that commits as it goes before it starts to overlay the rbs

> information that it has created.  This can be important in reducing

 snapshot


> too old errors encountered on the system.

>

> Optimal should be set to release unused but allocated extents from one

 segment


> so that the other segments can reuse them as needed.  I like to set it to

> minextents X next.

>

> I like to create the first rbs segments in my database as a large segment

 that


> I keep offline except for maintenance operations such as rebuilding the

> largest table in my system.

>

> I hope this helps.

>

> -- Mark D Powell --

>

>

>  -----  Posted via NewsOne.Net: Free (anonymous) Usenet News via the

 eb  -----


>   http://newsone.net/ -- Free reading and anonymous posting to 60,000+

 groups


>    NewsOne.Net prohibits users from posting spam.  If this or other posts

> made through NewsOne.Net violate posting guidelines, email

abuse_at_newsone.net