Re: why undo_management=auto

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 21 Dec 2006 18:14:06 -0800
Message-ID: <1166753645.178039@bubbleator.drizzle.com>

Andy Hassall wrote:

> On Thu, 21 Dec 2006 14:56:12 -0800, DA Morgan <damorgan_at_psoug.org> wrote:
>

>> I just went into the docs:
>> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#sthref413
>> and found an error I just reported. Where what is written is: "By
>> default, this parameter is set to MANUAL."
>
> What makes you think this is a documentation error?

Your question got me to thinking and now I'm more confused than ever (and that takes a bit of doing).

Here's what the Oracle doc says:
"The UNDO_MANAGEMENT initialization parameter determines whether an instance starts in automatic undo management mode which stores undo in an undo tablespace. By default, this parameter is set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode."

This would indicate that undo tablespace is NOT the default and requires manual intervention.

And this query:

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 21 17:58:50 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> col name format a30
SQL> col value format a30
SQL> SELECT name, value, isdefault

2 FROM gv$parameter
3 WHERE name LIKE '%undo%';

NAME                           VALUE                          ISDEFAULT
------------------------------ ------------------------------ ---------
undo_management                AUTO                           FALSE
undo_tablespace                UNDOTBS1                       FALSE
undo_retention                 3600                           FALSE

SQL> Would tend to confirm that AUTO is not the default.

Except that I just built a 10g database and there is no question that the value defaulted to AUTO and the database has an UNDO tablespace.

And this doc:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams220.htm#sthref897 states:
"In manual undo management mode, undo space is allocated externally as rollback segments."

Anybody out there running 10gR2 with rollback segments and not undo? I've certainly never seen it. There must be something I'm missing so, I hope, someone can complete the picture are resolve the apparent paradox.

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

Received on Thu Dec 21 2006 - 20:14:06 CST