Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

UNDO Tablespace, and how NOT to use

From: Guy Taylor <>
Date: Thu, 02 Aug 2007 17:25:27 -0700
Message-ID: <>

I have a situation where our UNDO TABLESPACE is filling up with data. Our application creates a temporary table. It then creates several indexes and runs dozens, if not hundreds of selects against the table. The table is then dropped. The entire process may take an hour or two.

I am not an Oracle DBA, so I may get some of the terms wrong. I also find this fascinating because it is very counterintuitive.

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?

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?

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?

Thanks for any advice and I look forward to a discussion about how this works. Received on Thu Aug 02 2007 - 19:25:27 CDT

Original text of this message