Re: UNDO Tablespace, and how NOT to use

On Thu, 02 Aug 2007 17:25:27 -0700, Guy Taylor <twopotsaday_at_gmail.com> wrote:

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

Shortly you can't. It would invalidate the entire transaction and read consistency model, and it would mainly work when you are the only user of the database, and your database is stored on punch cards. You are in dire need of reading the Concepts Manual. All of your assertions in this paragraph show you never read it. UNDO tablespaces are there to
a) make it possible to rollback INSERT, UPDATE, DELERE statements b) make it possible to reconstruct a read consistent image of a table *prior* to the transaction.
In Oracle readers *DON'T* block writers, and even Mickeysoft's SQL server is trying to implement this concept currently.

-- 


Sybrand Bakker
Senior Oracle DBA