UNDO Tablespace in 9i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: UNDO Tablespace in 9i

  1. #1
    Join Date
    Sep 2003
    Posts
    19

    UNDO Tablespace in 9i

    My database version is 9.2.0.4
    undo_retention parameter is set to 10800(sec) 3 hrs(as default value)
    I have observed the undo tablespace size has been going on increasing.. I have never seen it getting down.. Currently its datafile size is 19.4GB

    Still error like the following is coming and the metalink suggests that undo retention parameter be increased.

    (ORA-12008: error in materialized view refresh path
    ORA-01555: snapshot too old: rollback segment number 2 with name "RBS2" too small
    ORA-02063: preceding line from MIS_LINK)

    How do I manage the Tablespace size . It may fall short of the filesystem size someday!
    Preeti

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Have you set UNDO_MANAGEMENT=AUTO ?

    Suspecting because the rollback segment name in questions is "RBS2" whereas in auto-undo management rollback segments names are like _SYSSMUn$ which is system generated.

    Second, If RBS is manually configured how the extents are sized?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Sep 2003
    Posts
    19
    I am surprised too . There is no rollback segment expected in the database with such name and the following returns no rows
    SQL> select * from dba_segments where segment_name like '%RBS%';

    However the master site from where the snapshot refresh takes place is 8i
    Preeti

  4. #4
    Join Date
    Sep 2003
    Posts
    19
    Yes undo management is set to AUTO

    SQL> select segment_name from dba_rollback_segs;

    SEGMENT_NAME
    ------------------------------
    SYSTEM
    _SYSSMU1$
    _SYSSMU2$
    _SYSSMU3$
    _SYSSMU4$
    _SYSSMU5$
    _SYSSMU6$
    _SYSSMU7$
    _SYSSMU8$
    _SYSSMU9$
    _SYSSMU10$
    _SYSSMU11$
    _SYSSMU12$

    13 rows selected
    Preeti

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Originally posted by preetikate
    I am surprised too . There is no rollback segment expected in the database with such name and the following returns no rows
    SQL> select * from dba_segments where segment_name like '%RBS%';

    However the master site from where the snapshot refresh takes place is 8i
    Do you have RBS2 in the master site (8i) with rollback segment number 2.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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