Re: Oracle 9.2.0.1.0 Undo Management?

Comments embedded.

Jackie Chang wrote:
> Oracle 9.2.0.1.0 undo tablespace extend managment use LOCAL. I think the
> tablespace set to autoextend.
> I use Oracle Enterprise Manager Console check it! And check UNDOTBS01.DBF
> file size!
>
> Undo_retention to large(10800 9iR2 default), So Oracle does not reuse undo
> tablespace.

Incorrect, as Oracle does reuse UNDO space. You've obviously generated enough redo to cause Oracle to presume the normal transaction load is properly represented by your three transactions. Given that, and your UNDO_RETENTION parameter value, I'm not surprised your UNDO tablespace grows.

> Thinks a lot!
>
> Jackie Chang!
>
> > Jackie, you have provided insufficient information in your post.
> >
> > What specific version of Oracle? I will guess 9.2 something from the
> > connect string
> >
> > What are the undo tablespace parameters? You imply that it is set to
> > autoextend.
> >
> > What query are you using to look at size?
> >
> > What value is undo_retention set to?
> >
> > How many megabytes of data does the update generate?
> >
> > Without knowing any specifics I will guess that Oracle extended the
> > undo tablespace to be able to retain the data for the time period
> > specified by undo_retention.
> >
> > HTH -- Mark D Powell --
> >

You need to query the V$UNDOSTAT view, as it will provide a wealth of information about how your UNDO is being used. Also read this link:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#9505

as it explains how the undo tablespace is used and how Oracle manages it. Also, a reasonably accurate calculation for determining your UNDO tablespace size is provided.

David Fitzjarrell Received on Sat Jun 18 2005 - 16:53:45 CDT