Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003

    Unanswered: How do I flush the UNDO tablespace?

    For some reason when I execute the dbms_java.grant_permission statement
    Oracle writes massive amounts of data to the undo tablespace. Oracle is also permanently retaining it even though I have it set for only a 5 minute
    retention. Is there any way to force a flush of the tablespace so I can reclaim the space?

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >Is there any way to force a flush of the tablespace so I can reclaim the space?
    As a general rule tablespaces "never" shrink; especially below their HWM.
    I suggestion that you go find a REAL problem to solve, even if you are successful, I doubt that any user would ever know the difference.
    What problem are you really trying to solve?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2003
    Virginia, USA
    I have seen this requirement for security minded customers. You can do it, but not directly as you might like.

    First, Oracle will retain the contents of UNDO. Oracle migth overwrite the contents of UNDO based on the time limit set by the Undo retention parameter in your init.ora file. In other words, any Undo data older than X is allowed to be overwritten, but won't actually be overwritten unless there's DML happening in your database.

    Second, keep in mind you can have numerous Undo tablespaces, but only one can be on-line at a time. Use this to your advantage. Create a new Undo tablespace, tell Oracle to start using it, then drop the old tablespace "including contents and datafiles".
    Author, Oracle Database 10g: From Nuts to Soup

Posting Permissions

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