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

Thread: ORA-1628: max # extents 32765 in 10g

  1. #1
    Join Date
    Feb 2001
    Posts
    129

    ORA-1628: max # extents 32765 in 10g

    Hi,

    sun solaris 64 bit
    db - 10.2.0.2

    I'm getting ora-1628 errors on a undo tablespace.
    ORA-1628: max # extents 32765 reached for rollback segment _SYSSMU261$

    The tablespace is 200gb in size and with over 35gb of free space. The undo retention was 15 hrs.. i've reduced to 12 hrs ..but still that does not seem to help.

    Many transactions are failing and the alert log is getting flooded with ora-1628 errors on the same rollback segment.

    Below are the results from dba_extents and dba_ssegments.

    Appreciate your help

    select sum(blocks),count(*),segment_name from DBA_EXTENTS where tablespace_name = 'PSAPUNDO' group by segment_name order by segment_name;

    SUM(BLOCKS) COUNT(*) SEGMENT_NAME
    .
    .
    .
    1859952 32765 _SYSSMU261$
    .
    .

    select sum(blocks),sum(extents),segment_name from DBA_SEGMENTS
    where tablespace_name = 'PSAPUNDO' group by segment_name order by segment_name;
    SUM(BLOCKS) SUM(EXTENTS) SEGMENT_NAME
    .
    .
    1859952 32765 _SYSSMU261$
    .
    .

    Regard
    Jay

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Allocate more rollback segments or If possible, increase the value of
    either the MAXEXTENTS or PCTINCREASE initialization parameters

    You can chang the maxextents via:

    alter rollback segment storage ( maxextents N );


    if you are going to change next, you could:

    alter rollback segment storage ( next );

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by gopu_g
    Allocate more rollback segments or If possible, increase the value of
    either the MAXEXTENTS or PCTINCREASE initialization parameters

    You can chang the maxextents via:

    alter rollback segment storage ( maxextents N );


    if you are going to change next, you could:

    alter rollback segment storage ( next );
    really - think about answers before you write them - none of that is possible in automatic undo

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Hi Nugpot,

    Can you post the UNDO tablespace creation statement?

  5. #5
    Join Date
    Mar 2008
    Posts
    28
    Agree with Dave. In 10g UNDO are automatic. Are u using any retention guarantee parameter set for UNDO.

  6. #6
    Join Date
    Feb 2001
    Posts
    129
    It seems to be an oracle bug 6499872. There is no patch for 10.2.0.2., so I ended up recreating the undo tablespace.

    The syntax for the undo tablespace creation was ...
    CREATE UNDO TABLESPACE PSAPUNDO DATAFILE
    '/oracle/XXX/YYYY/undo_23/undo.data23' SIZE 8190M AUTOEXTEND OFF,
    .....
    .....
    ......
    ONLINE
    RETENTION NOGUARANTEE
    BLOCKSIZE 8K
    FLASHBACK ON;

    Thanks
    Jay

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