Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: UNDO Tablespace, and how NOT to use

Re: UNDO Tablespace, and how NOT to use

From: Brian Peasland <>
Date: Fri, 03 Aug 2007 10:10:57 -0500
Message-ID: <46b3396e$0$619$>

Just to add to the other information posted in this thread...

> Our DBA tells me that the UNDO TABLESPACE has filled, or is close to
> filling up. He can tell me what SQL has run recently, or has an active
> or recent footprint in the UNDO TABLESPACE, but he cannot tell me how
> much space each statement, or even each transaction is consuming. It
> seems to be that this information should be available. If the
> TABLESPACE is filling up, one would think the database system would
> provide an interface to clean it up. Is this correct?

As has been stated, how does the DBA verify that the Undo ts has been "filled up"? One may query DBA_SEGMENTS or DBA_FREE_SPACE and see allocated space with no free space in the tablespace, but that does not mean that the extents do not have free space.

Oracle will clean up any unneeded undo records automatically. There is no interface for you to tell Oracle to clean up undo records manually.

> Secondly, the bulk of these queries are select statements. Why does a
> select statement need UNDO space? If Oracle is using the UNDO space to
> maintain a snapshot of the table for the query so that it has a
> consistent view of the table (as opposed to locking it), then why
> would the information remain in the UNDO TABLESPACE for hours? Once a
> query (or transaction) is committed, wouldn't the database release its
> resources from the UNDO TABLESPACE?

The SELECT statement does not actually create nor require undo records to be in the UNDO tablespace. However, if DML has occurred on the block that the SELECT statement accesses, the undo records are used to generate a read-consistent image of the block.

The length of time that the undo records are kept in the undo tablespace is determined by the UNDO_RETENTION initialization parameter. If the DBA has defined this parameter to store hours of undo records, then the undo will stick around for that amount of time. Once a transaction is committed, the undo will still remain for UNDO_RETENTION seconds. This is to support certain Flashback operations.

> Thirdly, assuming that the database maintains information in the UNDO
> TABLESPACE (so it can rollback select statements, I guess), and I run
> up against the limit of the UNDO TABLESPACE size, is there any way, at
> the beginning of a transaction or select statement that I can ask the
> database to not use the UNDO TABLESPACE? In other words, the
> application is constructed in such a way that the selected from table
> is guaranteed not to change. Thus, there is no need to worry about
> consistency or the data changing underneath me. How do I tell the
> database to punt on the rollback infrastructure?

If the UNDO_MANAGEMENT initialization parameter is set to AUTO, then you cannot bypass the undo mechanism.

One thing that has not been stated is that if there is not enough space in the Undo tablespace to store undo records for UNDO_RETENTION seconds, then this can cause problems. If the Undo tablespace is too small, some queries may see the Snapshot Too Old error (ORA-1555). If you are not seeing this error, then it is highly likely that your Undo tablespace is not too small. Things change in Oracle 10g if the DBA has defined the Undo tablespace with RETENTION GUARANTEE in which case if the Undo ts is too small, DML statements may not have anywhere to write undo records. Appropriate sizing of the Undo tablespace is required.



Brian Peasland

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Posted via a free Usenet account from
Received on Fri Aug 03 2007 - 10:10:57 CDT

Original text of this message