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

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 Web ----- 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