Emptying Temp and undo tablespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Emptying Temp and undo tablespaces

  1. #1
    Join Date
    Jan 2006
    Location
    Bangalore,IND
    Posts
    47

    Question Emptying Temp and undo tablespaces

    How would i empty these tablespaces?

    When i shutdown and restart the database my temp tbs still shows full. As for undo when the transaction completes execution may empty it, but not by bouncing the db.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you dont need to touch them, they will always show as full (or near) full all the time due to online undo / temp segments

  3. #3
    Join Date
    Mar 2006
    Posts
    40
    You don't need to free the temp tablespace it is done automatically when needed. And the free space might not always be correctly shown so if you don't receove errors that there is not enogh space in it you should not bother.
    If the undo tablespace is automatic you also don't need to bother with it. If it is manual you can free space by shirnking the rollback segments in it.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    As the others have indicated, you normally don't have to worry about the TEMP & UNDO tablespaces. However, if you have encountered an abnormal process that has greatly increased the size of these datafiles/tempfiles, then you can shrink them by using the following:

    TEMP Tablespace:

    -- create a new TEMP tablespace
    -- make this new TEMP tablespace the default temporary tablespace for the database
            (ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;)
    -- drop the old TEMP tablespace

    UNDO Tablespace:

    -- create a new UNDO tablespace
    -- switch the database to use the new UNDO tablespace
            (ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;)
    -- drop the old UNDO tablespace
       
       

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I'd avoid making a temp TS autoextend, especially in development. Forget a join condition and you've a Cartesian product that can blow out the biggest disks. So you should almost never need to shrink them.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #6
    Join Date
    Jan 2006
    Location
    Bangalore,IND
    Posts
    47
    thanks all for your time

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