Re: UNDO Tablespace, and how NOT to use

Guy Taylor wrote:
> I bought the book this AM.
>
> I know the UNDO tablespace filling up is a problem because the Oracle
> DBA insists it is.

I wouldn't be so sure. <g>

> If there is no downside to this tablespace filling
> up, then I have been mislead.

Quite possibly.

> Perhaps my terminology is wrong
> regarding the "temporary" table. I create a table in the schema's
> tablespace, run queries against it, and then drop it when I am done.

That is what we understood and NEVER ... EVER ... do that in Oracle. It is completely unnecessary under all conditions and kills scalability and performance.

You do that in SQL Server as a work around for poorly designed locking and transaction handling going back to its roots in Sybase and Ingres: But not here.

> Thus, perhaps it is not a "temporary table" as defined in the
> literature.

Not in Oracle. It is a permanent table being built on-the-fly and it is painful. It contains no indexes, the optimizer knows nothing about it, and the overhead of creating and dropping it beyond your imagination.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org