Re: Status of Undo Tablespace

Ora wrote:
>
> Oracle 9i: Release 2 / Win2k
>
> When another Undo Tablespace is created, the status of all Undo
> Tablespaces is only ONLINE?

That's normal. Unless you take the tablespace offline, that's the status I would expect.

>
> I thought only one would be active at one time.

Correct. Only one undo tablespace can be active at a time per instance.

>
> After switching to another Undo Tablespace, the status of the old Undo
> Tablespace continue to be ONLINE only and not PENDING ONLINE ( even
> though there was uncommitted DML transaction going on prior to the
> switch) as per the document?

Again normal. The tablespace does go into a pending offline state but I'm not sure where this is recorded (certainly not in DBA_TABLESPACES as the tablespace is still online as such). I would have to look it up.
>
> How to find out, which is the default Undo Tablespace?

Show parameter undo_tablespace will do the trick. Look in dba_undo_extents to see which extents are still active.

> And how to find out which transaction is using which Undo Tablespace

V$transaction

> if more than one ONLINE Undo Tablespace is available ?

There can only be one undo tablespace available per instance (although, yes some undo segments may still be active from another undo tablespace if you've just done a switch and the prior transactions haven't completed).

Cheers

Richard
>
> TIA
>
> Ora

Received on Wed Aug 14 2002 - 02:56:22 CDT