Re: ORA-01652 in UNDO tablespace

From: joel garry <joel-garry_at_home.com>
Date: Tue, 8 Sep 2009 11:34:43 -0700 (PDT)
Message-ID: <ca7bb4d0-c151-4112-a5af-35568e516269_at_y28g2000prd.googlegroups.com>

On Sep 7, 4:22 am, vsevolod afanassiev <vsevolod.afanass..._at_gmail.com> wrote:
> I have 10.2.0.4 database on AIX, and for some strange reason it
> reported ORA-01652 error for UNDO tablespace:
>
> Sat Sep  5 11:20:06 2009
> ORA-1652: unable to extend temp segment by 8 in
> tablespace                 UNDO_01
> Sat Sep  5 11:20:06 2009
> ORA-1652: unable to extend temp segment by 8 in
> tablespace                 UNDO_01
> Sat Sep  5 11:20:06 2009
>
> This is strange as ORA-01652 normally gets reported for TEMP
> tablespace (sort, hash join, LOB operations) or for normal data
> tablespaces (create table as select, create index, index rebuild,
> etc). However I never saw it reported for UNDO.
>
> - Tablespace UNDO_01 is definitely UNDO tablespace:
>
> SQL> select contents from dba_tablespaces where tablespace_name =
> 'UNDO_01';
>
> CONTENTS
> ---------
> UNDO
>
> - No user has temporary tablespace set to UNDO_01:
>
> SQL> select distinct temporary_tablespace from dba_users;
>
> TEMPORARY_TABLESPACE
> ------------------------------
> TEMP2
>
> - Tablespace UNDO_01 contains only undo segments as expected:
>
> SQL> select distinct segment_type from dba_segments
>   2  where tablespace_name = 'UNDO_01';
>
> SEGMENT_TYPE
> ------------------
> TYPE2 UNDO
>
> - Parameter undo_management is set to AUTO:
>
> SQL> show parameter undo
>
> NAME                                 TYPE        VALUE
> ------------------------------------ -----------
> ------------------------------
> undo_management                      string      AUTO
> undo_retention                       integer     10800
> undo_tablespace                      string      UNDO_01
> SQL>
>
> - Value of V$UNDOSTAT.TUNED_UNDORETENTION was approx 50,000 at the
> time when errors were reported. The database generates approx 200,000
> - 300,000 undo blocks per day (uniformly distributed accross 24
> hours), 8K block size, at the time of the error UNDO_01 was 2 GB.

I may be confusing this with something else, but I have a vague memory of this happening when you run out of space that you've told Oracle you have available in the flash recovery area. The default happens to be 2GB.

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/sep/08/twitter-policies-come-workplace/?uniontrib

Received on Tue Sep 08 2009 - 13:34:43 CDT