On Aug 2, 8:25 pm, Guy Taylor <twopotsa..._at_gmail.com> wrote:
> 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.
You might want to purchase and read Tom Kyte's latest book. It has a
thorough discussion of UNDO tablespaces.
Received on Thu Aug 02 2007 - 19:28:10 CDT
