-
ORA-01562
We have PRODUCTION database on the IBM Mainframe platform (OS-390, MVS/ESA).
Last Sunday a heavy update (batch job) abended with
ORA-01562:
failed to extend rollback segment number 5 ORA-01628
The following are the data extracted from V$ROLLSTAT
SEG-NAM----XTNTS---RSSIZE---OPTSIZE----HWMSZ-- XTNDS-SHRNK
SYSTEM----------4------241664---------------241664-----0---0
PRB002----------2---10481664--10485760--15724544-----1---1
NEWROL---------2---10481664--10485760--10481664-----0---0
PRB003----------2---10481664--10485760--10481664-----0---0
PRB001----------2---10481664--10485760--10481664-----0---0
PRB004----------2---10481664--10485760--10481664-----0---0
PRB005----------2---10481664--10485760-304082944---56---6
It looks like the segment-troublemaker (#5) was expanded 56 times:
initial size is 5Mb, increment size is 5Mb,optimal size set to (5*2)Mb. (56+2)*5Mb=290Mb=304087040
Maximum Number of extents (249) apparently was not reached.
My question is - why? Why ORACLE responded with "ORA-01562"?
It's obvious, that the total highwatermark size (even if it was reached at the same point in all segments) is less than 350Mb.
On the other hand, the total size available in ROLLBACK tablespace is 620Mb.
(We have 3 VSAM datasets with total 13260 TRKs on 3390 triple density DASD)
What did I miss ?
[Edited by jrvladr on 01-09-2001 at 02:52 PM]
-
It could be possible that the rollback ts was fragmented, thus causing the extents for PRB05 to be thrown all over the ts with a bunch of free space in the ts, causing it to fail. Consider coalesing the ts before such operations and/or set Autoextend ON for the rollback ts.
-
whats %INCREASE on ROLLBACK TABLESPACE ? It should be zero. It could be the problem with fragmentation.
Did you add 2blocks overhead for the datafile at the time creating tablespace ?
Your rollback tablespace extents are multiple of rollback segments initial and next sizes ?
I would advise you to use locally managed tablespaces for rollback and temp tablesapces, if your version supports 'em.
-
Yout ORA-01562 message contains also the exact cause of the error - "ORA-01628" which has the following error message: "max # extents (string) reached for rollback segment string".
This means that your rollback segment has the above limit different that you thought it is - obviously it is not 249 as you think. Either the RB segment or the tablespace it is residing in must have been created with some MAXEXTENTS specification in the storage clause. Check the MAX_EXTENTS in DBA_ROLLBACK_SEGS.
HTH,
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|