Re: Assignment of undo tablespace to new user

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 23 May 2003 05:40:16 +1000
Message-ID: <lx9za.40128$1s1.566625@newsfeeds.bigpond.com>

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message
> You can actually have more than one undo tablespace that is in use in
> Oracle 9i. You set it with parameter undo_tablespace=undotbs in your
> init file. But only one can be in use at any time.

That's not actually quite right.

You can alter the UNDO_TABLESPACE at any time (alter system set undo_tablespace=NEWBLAH).

When you do so, new transactions will immediately start using the new undo tablespace, whilst any existing transactions are allowed to complete in the old one. Therefore, albeit temporarily, you have two undo tablespaces that are active.

Never mind that in a RAC database, each instance gets its own undo tablespace. So a 32-node RAC will have 32 perfectly legitimately active undo tablespaces.

Regards
HJR Received on Thu May 22 2003 - 14:40:16 CDT