undo tablespace growing big
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: undo tablespace growing big

  1. #1
    Join Date
    Mar 2002
    Posts
    56

    undo tablespace growing big

    My undo tablespace was created as part of thi screate database statement:

    CREATE DATABASE orsp
    MAXINSTANCES 1
    MAXLOGHISTORY 1
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    DATAFILE '&vDATAFILES\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
    UNDO TABLESPACE "UNDOTBS" DATAFILE '&vDATAFILES\undotbs01.dbf' SIZE 800M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    CHARACTER SET WE8MSWIN1252
    NATIONAL CHARACTER SET AL16UTF16
    LOGFILE GROUP 1 ('&vFIRSTREDO\redo01.log', '&vSECONDREDO\redo01B.log') SIZE 10M,
    GROUP 2 ('&vFIRSTREDO\redo02.log', '&vSECONDREDO\redo02B.log') SIZE 10M,
    GROUP 3 ('&vFIRSTREDO\redo03.log', '&vSECONDREDO\redo03B.log') SIZE 10M,
    GROUP 4 ('&vFIRSTREDO\redo04.log', '&vSECONDREDO\redo04B.log') SIZE 10M;


    I noticed that the size of the undo file grew in two months to 8.5G. Is it normal?

    Thanks,

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    some people are doing some long running queries making it grow that big

  3. #3
    Join Date
    Mar 2002
    Posts
    56
    I know it can grow, my question is what can I do to shrink it once in a while? Also, undo tablespace being that big affects the overall performance of the system or not? Is it worth having multiple files for this tablespace?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    One cause may be having undo_retention too big.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    DO NOT SET AUTO EXTEND ON.

    Tamil

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by tamilselvan
    DO NOT SET AUTO EXTEND ON.

    Tamil
    and get unable to extend tablespace error, much better!

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    alter rollback segment rbs1 shrink;

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by kris123
    alter rollback segment rbs1 shrink;
    not with undo management in 9i im afraid

  9. #9
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    Just as an add on

    IMO; the undo and temp size should go hand in hand. Also, if you have them set for auto extend you're setting yourself up for failure. Lets put it this way if you have a process out there that sucks up more than 2 gig of temp space (or undo) you should have your gun in hand and be on your way down to developers cube who wrote the process / code.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    but if this in a a production system your useers do not want their long running queries stopping with an error.

    If its not causing a problem with disk space or whatever, let it grow monitor it and fix things that cause problems

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