Re: Segment management auto clause

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:80l0cvcep7bfvplq5gbmh4hqp2ivvhh35q_at_4ax.com...
> If I set the UNDO_MANAGEMENT=AUTO init parameter, Oracle is supposed
> to manage undo automatically. So if I create a tablespace, do I still
> need to include the "SEGMENT MANAGEMENT AUTO" clause?
>

Chalk and Cheese, Peter. The 'create tablespace blah... segment space management auto' statement means that you are creating a perfectly ordinary tablespace (which can NOT contain undo segments, because it's not a proper undo tablespace) which happens to be using the new ASSM method for determining which blocks will receive newly inserted data.

ASSM has significant overheads, and I strongly advise you not to use it unless you happen to be suffering from extreme free list contention (which commonly happens in a RAC environment). If you *do* have bad freelist contention issues, however, ASSM is generally a better "cure" for it than the alternative of multiple freelists. But I suspect you don't have freelist contention, and therefore you'd best get out of the habit of specifying the 'switch on ASSM' clause which you seem to think is a 'standard' part of the create tablespace command.

None of which has the slightest thing to do with automatic undo management.

That is switched on by setting two init.ora parameters: UNDO_MANAGEMENT=AUTO and UNDO_TABLESPACE=BLAH ...and "BLAH" is a proper (and dedicated) undo tablespace, created either at 'Create Database' time, or subsequently with the 'create undo tablespace BLAH datafile '/whatever/blah.dbf' size 50M' statement.

Given your other post this evening, I suspect you might find a trip to http://tahiti.oracle.com to be of benefit. In particular, you want to read the 9i Release 2 Concepts manual. It's big, and should mean you don't confuse these sorts of issues in the future.

Regards
HJR Received on Tue May 13 2003 - 05:20:41 CDT