ORA-01652 in UNDO tablespace

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.

ORA-01652: unable to extend temp segment by string in tablespace
string

Cause: Failed to allocate an extent of the required number of blocks
for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

The Oracle engine "seems" to think you're out of space. This IS a
strange one. Check MetaLink and see if anybody else has reported it.

On Sep 7, 4:22*am, vsevolod afanassiev
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
--
@home.com is bogus.
http://www3.signonsandiego.com/stori...ace/?uniontrib

[giteshtrivedi]

Hi,

Error indicates "out of space". May be not expected behaviour of Oracle. Did you get any other error with same? Check alert.log or any other trace files.