UNDO Tablespace

[malru]

Initially my database had UNDOTBS1,in addition to it i've created another UNDO tablespace,

create undo tablespace undotbs2
datafile 'c:\oracle\oradata\testdb1\undotbs02.dbf' size 10m;

Then,i checked the staus of both the tablespace using,

SQL> select segment_name, tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU14$ UNDOTBS2 OFFLINE
_SYSSMU15$ UNDOTBS2 OFFLINE
_SYSSMU16$ UNDOTBS2 OFFLINE
_SYSSMU17$ UNDOTBS2 OFFLINE
_SYSSMU18$ UNDOTBS2 OFFLINE
_SYSSMU19$ UNDOTBS2 OFFLINE
_SYSSMU20$ UNDOTBS2 OFFLINE

21 rows selected.

To brought UNDOTBS2 online,i issued the followind command,

SQL> alter system set undo_tablespace=UNDOTBS2;

System altered.

Then,i checked the staus of both the tablespace using,


SQL> select segment_name, tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 ONLINE
_SYSSMU12$ UNDOTBS2 ONLINE
_SYSSMU13$ UNDOTBS2 ONLINE
_SYSSMU14$ UNDOTBS2 ONLINE
_SYSSMU15$ UNDOTBS2 ONLINE
_SYSSMU16$ UNDOTBS2 ONLINE
_SYSSMU17$ UNDOTBS2 ONLINE
_SYSSMU18$ UNDOTBS2 ONLINE
_SYSSMU19$ UNDOTBS2 ONLINE
_SYSSMU20$ UNDOTBS2 ONLINE

Everything is ok,upto this.
Then,i made UNDOTBS1 online using,

SQL> ALTER TABLESPACE UNDOTBS1 ONLINE;

Tablespace altered.

Then,i checked the staus of both the tablespace using,

SQL> select segment_name, tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 ONLINE
_SYSSMU12$ UNDOTBS2 ONLINE
_SYSSMU13$ UNDOTBS2 ONLINE
_SYSSMU14$ UNDOTBS2 ONLINE
_SYSSMU15$ UNDOTBS2 ONLINE
_SYSSMU16$ UNDOTBS2 ONLINE
_SYSSMU17$ UNDOTBS2 ONLINE
_SYSSMU18$ UNDOTBS2 ONLINE
_SYSSMU19$ UNDOTBS2 ONLINE
_SYSSMU20$ UNDOTBS2 ONLINE

21 rows selected.

My doubt in this is,

1) After making UNDOTBS1 as ONLINE,still it's showing UNDOTBS2 as ONLINE & UNDOTBS1 as OFFLINE. Why it's so?

2) For UNDOTBS1(whose size is 200M),exactly 10 segments are created & for UNDOTBS2(size is 10M) also,exactly 10 segments are created.

2.1) Is there any way to control the number of segments allocated for a tablespace or it's system duty to allocate segments.

2.2) If it's system duty,based on which it exactly allocated 10 segments to each tablespace eventhough the datafile size of two tablespaces differs by a lot.

Please anyone clear my doubts.

[TimHall]

Hi.

1) Each instance (thread) can only have a single UNDO tablespace. That's the way it is.

2.*) Once you ask Oracle to manage UNDO, there is little you can do other than alter the tablespace size and the retention time. You've more or less got to forget about it. It controls the number of extents as it sees fit. It just so happens it allocates 10 segments to start with, but in the next release it might be 20, or 5. It's whatever the guys at Oracle have decided.

Cheers

Tim...