Undo tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Undo tablespace

  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Undo tablespace

    Hi,
    Our undo table space UNDOTBS01.DBF ahs recently jumped to 2.6 GB of data after the machine crashed repeatedly. Is there any way of cleaning up what is held in the file or should I be leaving it alone?
    I do need the space its taking up as the disk is now almost full (56k free) and Oracle is running extremely slowly.
    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Have you tried resizing it down?

    If you cant do that.

    1 set undo_management = manual in spfile / init.ora file
    2 restart database
    3 drop tablespace undo_tbs including contents and datafiles;
    4 create a new tablespace with the correct size (with the same as that which you dropped)
    5 put undo_management back to auto
    6 restart

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I haven't had any success shrinking the datafile(s).

    Here's what I do (no restart required):

    create undo tablespace undotbs02 datafile 'yourfilenamegoeshere' autoextend on next 100m maxsize 6000M;

    alter system set undo_tablespace='UNDOTBS02';

    drop tablespace undotbs01 including contents and datafiles;

    You may not be able to drop this until existing sessions close and free up the offline pending segments.

    Check your undo_retention and make sure it's set right. Set it too low and you'll get ora-1555 snapshot too old errors. Too high and you'll end up with a muy largo unto tbs again.

    Best of luck.
    Last edited by KenEwald; 02-28-2005 at 04:35 PM.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by KenEwald
    . . . autoextend on next 100m maxsize 6000M;
    If 2.6GB is "too big" then the maxsize presumably should be set lower - of course it depends on what undo retention is realistically required.

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Good observation... I agree 100% with the correction.

    Here's what I do


    If he isn't happy with 2.6 gigs, he definately wouldn't want it growing past 6gb. he he...
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    i would of suggested that, but he said 56k of space left, no room to create a new one first

  7. #7
    Join Date
    Feb 2005
    Posts
    2
    Lads,
    Thanks for those comments they all helped.
    I ended up buying a new drive, it became the easiest option after talking to the users.
    Cheers,
    Jonathan

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966

    Re: Undo tablespace

    Originally posted by jflack
    Hi,
    Our undo table space UNDOTBS01.DBF ahs recently jumped to 2.6 GB of data after the machine crashed repeatedly. Is there any way of cleaning up what is held in the file or should I be leaving it alone?
    I do need the space its taking up as the disk is now almost full (56k free) and Oracle is running extremely slowly.
    Thanks.
    Have you tried a new disk array? Is the database crashing when a volumne runs out of space? You can also try to look at your data and see if it is being stored efficiently. DBMS_SPACE is a good place to look.
    this space intentionally left blank

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    easiest option after talking to the users


    Database Administration by POLLING THE MASSES .. interesting methodology .. hey, works for politicians (he he)
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by KenEwald
    Database Administration by POLLING THE MASSES .. interesting methodology .. hey, works for politicians (he he)
    Not so stupid . . . if you have to balance cost of down-time and/or DBA overtime against the cost of disk, then those who pay the bill should have say.

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