UNDO Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: UNDO Tablespace

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    UNDO Tablespace

    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.

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width