ORA-01652 in UNDO tablespace

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Mon, 7 Sep 2009 04:22:47 -0700 (PDT)
Message-ID: <20953e24-2f3c-43d7-b147-5b38243eba58_at_z4g2000prh.googlegroups.com>

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.

Received on Mon Sep 07 2009 - 06:22:47 CDT