Re: UNDO Tablespace, and how NOT to use

On Aug 2, 5: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?

See http://www.oracle.com/technology/products/manageability/database/pdf/OOW2002SpaceMgmtEXT.pdf There are various scripts floating about to see various things. It kinda sounds like your dba needs more training or perhaps needs to be able to communicate them better to you. Some older explanations of rollback segments can make things more clear, see for example metalink Note:1011108.6. More recent and informative google: undo site:jonathanlewis.wordpress.com

>
> 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?

I would suggest reading the concepts manual before getting to Tom's book. The information in the undo tablespace is needed to be able to reconstruct data to present it to transactions that start at arbitrary times. You need to get your head around the fact that much of Oracle's architecture is dealing with how other people query your data. And now it even has the ability to query your own data as it looked in the past.

Tom's book is really good, especially for explaining why you need to unlearn your bad habits.

>
> 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?

That's kind of like asking how to unhook your car's computers because you think you can set timing better manually while you are driving.

>
> Thanks for any advice and I look forward to a discussion about how
> this works.

jg

--
@home.com is bogus.
$oerr opra 369
OPRA-00369, "Attempt to access overweight celebrity %s"
// *Cause:  Attempt to access overweight celebrity failed because it
is necessary
//          to become a credentialed paparazzi.
// *Action: If you have recently received your accredation, then wait
for it to
//          become active.  Otherwise contact Oprah support ho's.