Re: ORA-01562: ...failed to extend rollback segment.. when update/delete stmt issued...
Date: 1996/03/23
Message-ID: <4j159n$626_at_inet-nntp-gw-1.us.oracle.com>#1/1
Zareh Dergevorkian <zareh_at_earthlink.net> wrote:
>When issueing an UPDATE or DELETE SQL statement to an Oracle WGS2000 NT
>server, I get the error message ORA-01562: ...failed to extend rollback
>segment..
>I have allocated upto 200MB of rollback against a 250MB user_data name
>space...
Use the query:
SQL> select segment_name, segment_type, bytes, extents 2 from sys.dba_segments 3 where segment_type = 'ROLLBACK';
To determine exactly how big your rollback is. You'll get some output like:
SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS ------------------------------ ----------------- ---------- ---------- SYSTEM ROLLBACK 204800 4 R01 ROLLBACK 22149120 21 R02 ROLLBACK 2109440 2 R03 ROLLBACK 23203840 22 R04 ROLLBACK 16107520 121
As you can see, I have one rollback segment that is maxed out at 121 extents. It is about 16 meg. Rollback segments do not use the pctincrease and grow linearly. Therefore, the rollback segment R04 maxed out at 130k * 121 extents. The tablespace containing my rollback is much larger but R04 will never get bigger. Actually, I had the same problem you are having. I left the rollback to default and hit the 121 since my largest rollback segment is 16meg. I corrected this in 3 out of 4 of my rollbacks so far by:
alter rollback segment r01 offline;
drop rollback segment r01;
create rollback segment r01 tablespace rbs storage ( initial 1m next 1m );
>the statements that generate the error are obviousely updating/deleting more
>than what can be held in the RB segment... but what I'm wondering is why when
>the RB seg is almost as large as the entire database itself, and the
>statements only affect roughly 50+K recs (out of 1.5M+ recs), do I get an
>error failed to extend rb seg... ??????
Data is stored in the rbs by block, not by row. If you have for example 30 rows per block, an update that affects 50,000 out of 1.5mill records *could* affect every block in that table and cause each before image block to be put in the rollback segment.
>please HELP !!!
>zdg~~~~~
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Sat Mar 23 1996 - 00:00:00 CET