Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Assignment of undo tablespace to new user

Re: Assignment of undo tablespace to new user

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 22 May 2003 21:40:08 +1000
Message-ID: <dv2za.39757$1s1.564048@newsfeeds.bigpond.com>

"Peter" <peter_at_nomorenewsspammin.ca> wrote in message news:49tncv8l80tpsnqbb5a7s97p4ib137jbmo_at_4ax.com...
>
> Suppose you have an undo tablespace in use right now and then you
> create a new user and you assign the user to an undo tablespace(on
> Oracle9i) which is not currently in use. Will Oracle complain about it
> or will it just assign the user to use system undo segments?
>
> Thanks
>

Er, tangled wires here Peter. You don't assign users to an undo tablespace, only to a temporary tablespace.

If the question is: if I create a new user, does Oracle immediately allow that user to use their temporary tablespace, the answer is, yes... when that user starts to do something which requires swapping down to TEMP.

If the question is actually about undo segments, then the answer is: a user's transaction gets assigned (automatically) to whichever undo segment is least-heavily loaded at the time the transaction starts. At least, that's part of the story (and the part of the story which hasn't changed since undo segments were called rollback segments in 8i). The slightly fuller story in 9i is that Oracle prefers to create a new undo segment for each transaction before some mysterious point is reached, at which point it decides it's created enough of them, and starts sharing the existing segments amongst transactions -at which point, it allocates transactions to the least heavily stressed segment, just as it used to.

You really need to read the Oracle Concepts manual, because this isn't the first question you've asked which indicates a fairly fundamental mix-up of reasonably basic Oracle principles.

Regards
HJR Received on Thu May 22 2003 - 06:40:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US