Results 1 to 9 of 9

Thread: Undo tablespace

  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Unanswered: Undo tablespace

    I am quite new to this oracle buisiness; just wondering does the undo table space keep getting bigger and bigger; how can i free the space it uses and or limit the size (with-out getting errors about running out of undo space)?

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    You can make it smaller by resizing it.
    ALTER DATABASE DATAFILE '.....' RESIZE xxxM;

    You can make sure it does not grow by turning autoextend off for this datafile.
    Or you can have autoextend on, but define a maxsize.

    For more info on the Undo tablespace, look here:
    http://download-east.oracle.com/otnd.../undo.htm#8888
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I know how to do that; it just when i do a delete of six - eight million rows it gets kinda full. It also crashed giving an error (here). I wish to try to avoid this but have limits with disc space. How can i limit the amount of stuff oracle attempts to save in undo (i.e. so that it uses say 1000mb for undo and when it gets to 1000mb it starts to overrite the old stuff.)

  4. #4
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    You can set a parameter, I think it's called undo_retention or something.
    This is the number of seconds that undo information has to be kept. Try to make this value lower, so it will release old info sooner.

    See the link I mentioned above for the exact name and description of the parameter.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    ok thanks. what do you think about setting this to zero?

    but i don't understand why it is running out of space in the first place:
    ...the system starts reusing unexpired undo space.
    that was a quote from the oracle link you gave.


    Have you any ideas what settings could cause this not to overrite the oldest undo data?
    Last edited by rhs98; 02-26-02 at 10:02.

  6. #6
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Originally posted by rhs98
    ok thanks. what do you think about setting this to zero?

    but i don't understand why it is running out of space in the first place:
    that was a quote from the oracle link you gave.


    Have you any ideas what settings could cause this not to overrite the oldest undo data?
    The only thing that stops it overwriting is the UNDO_RETENTION parameter and obviously any uncommited transactions that are using the space will never get overwritten.

    You can always revert to using convential rollback segments by setting UNDO_MANAGEMENT to 'manual' and removing the UNDO_TABLESPACE entry in your init.ora or spfile.

  7. #7
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    on another, similar but also unrealted question; how can i get a

    delete x where y=z

    to commit every X amount of rows?

  8. #8
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    With using PL/SQL.
    You can make a loop.

    But I'm no PL/SQL guru, maybe post that in the PL/SQL forum.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  9. #9
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    ok, i will do it in java. just wondered if you could set 'commit points' of say every 10000 rows or something....

    thanks

Posting Permissions

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