Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: 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!

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    My answer is use the Oracle Managed Files (It might be called Oracle Managed segments) and do not manage it. I do not have much personal experience yet but I have it from 2 production DBAs that OMF (as I call it) is the way to go. Check out http://download-west.oracle.com/docs...1/omf.htm#1656

    HTH
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Hopefully a couple of things to try to help you out...

    First, your datafile for your UNDO tablespace is set to automatically extend. While it is set this way, your datafile will continue to expand whenever it is needed to accomodate the need for more undo space. Try: ALTER DATABASE DATAFILE '...\file.dbf' AUTOEXTEND OFF;

    Second, once a datafile expands to claim disk space, it will not let it go unless you resize it: ALTER DATABASE DATAFILE '...\file.dbf' RESIZE 1024M; However, to shrink a file down in size, data must not be present in the portion of the file you lose because of the file shrinking. Usually with undo tablespaces, I cannot resize them during the day; best time is at database start up when few, if any, transactions have been issued.

    Finally, the undo_retention parameter tells the database to allow a transaction to retain undo information for up to (in your case) 3 hours. My guess is that your materialized view is taking more than 3 hours to refresh, correct? If so, you need to (like Metalink suggests) increase the parameter to accomodate the long running query. Also, if not necessary, instead of running a COMPLETE refresh use the FAST refresh.

    Hope this helps clear up some questions.

    JoeB
    Last edited by joebednarz; 02-06-04 at 16:00.

  4. #4
    Join Date
    Jan 2004
    Posts
    84
    Hey Thanks JoeB!
    However I still have some queries.
    1.When UNDO Management has been set to AUTO , I do not expect rollback segment's name to be RBS2. Where is it coming from?
    Is it because the master site is Oracle8i and their teh rollback segments are managed in the conventional way. But when the snapshots are on 9i site the rollback segments used should be those at 9i site only.
    SQL> SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS;

    SEGMENT_NAME
    ------------------------------
    SYSTEM
    _SYSSMU1$
    _SYSSMU2$
    _SYSSMU3$
    _SYSSMU4$
    _SYSSMU5$
    _SYSSMU6$
    _SYSSMU7$
    _SYSSMU8$
    _SYSSMU9$
    _SYSSMU10$
    _SYSSMU11$
    _SYSSMU12$
    _SYSSMU13$
    _SYSSMU14$
    _SYSSMU15$
    _SYSSMU16$
    _SYSSMU17$
    _SYSSMU18$
    _SYSSMU19$

    2.If I put AUTOEXTEND Off, will it not yield errors for lack of undo space
    even though space is available in the filesystem(which I would not prefer) The idea of having that ON is to let the available space get used
    when required but I thought it would get reclaimed when doen with.

    Thanks again!
    Preeti

Posting Permissions

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