Re: UNDO Tablespace, and how NOT to use

Guy Taylor wrote:
> I have a situation where our UNDO TABLESPACE is filling up with data.

Determined by what means and how/why is that an issue?

> Our application creates a temporary table.

Do you mean a global temporary table or an imitation of a SQL Server trick that kills performance and scalability?

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

You mean a SQL Server type temporary ... stop it. This is Oracle not SQL Server and what you are doing is both unnecessary and bad practice. Likely you need no intermediary table at all but should you need one then you should use a global temporary table which is a permanent table in which the data is temporary.

To stop duplicating SQL Server methodology get copies of Tom Kyte's books and read the sections that relate to temporary tables and general concepts and architecture.

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