DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01562

  1. #1
    Join Date
    Jan 2001
    Posts
    2

    Thumbs down 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]

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    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.

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width