| UNDO Tablespace [message #160665] |
Mon, 27 February 2006 23:46  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
