DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Undo tablespace not release

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Undo tablespace not release

    Hi

    I did a list of delete-commit statements which increased the undo tablespace till a certain point where it hits the unable to extend segment error.

    What I do not understand is that when I tried to delete records which is about 1GB only but the undo tablespace have increased to 3.5GB! Is there a bug regarding the undo space being released after commit? My oracle version is 9.2.0.6. Thanks!

    The undo related init parameters are:

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO
    undo_retention integer 10800
    undo_suppress_errors boolean FALSE
    undo_tablespace string UNDOTBS

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    what's the retention time?
    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

  3. #3
    Join Date
    Jan 2000
    Posts
    387
    Hello

    The retention time is 10800.

  4. #4
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Hi,

    Are you asking for releasing space from UNDO tablespace or UNDO segments ?

    Dilipkumar Patel.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    10800 secs = 180 mins.. now you know why?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    This sql suggests the optimal value of the undo retention based on the size of the Undo Tablespace and undo traffic
    Code:
    SELECT us.undo_size/(1024*1024) "ACTUAL UNDO SIZE in Megs",SUBSTR(param1.value,1,25) "UNDO RETENTION in Sec", 
           ROUND((us.undo_size / (to_number(param2.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION in Sec" 
    FROM ( SELECT SUM(df.bytes) undo_size 
           FROM v$datafile df,v$tablespace ts,dba_tablespaces tbs 
           WHERE tbs.contents = 'UNDO'  AND tbs.status = 'ONLINE'   AND ts.name = tbs.tablespace_name 
           AND df.ts# = ts.ts#) us,v$parameter param1,v$parameter param2, 
         ( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec 
           FROM v$undostat ) g 
    WHERE param1.name = 'undo_retention' AND param2.name = 'db_block_size'
    This sql suggests the optimal size of the undo tablespace based on the value of the Undo Retention and undo traffic
    Code:
    SELECT sz.undo_size/(1024*1024) "ACTUAL UNDO SIZE in Megs",SUBSTR(param1.value,1,25) "UNDO RETENTION in Sec", 
           (TO_NUMBER(param1.value) * TO_NUMBER(param2.value) * us.undo_block_per_sec) / (1024*1024)"NEEDED UNDO SIZE in Megs" 
    FROM  (SELECT SUM(df.bytes) undo_size 
           FROM v$datafile df,v$tablespace tbs1,dba_tablespaces tbs2 
           WHERE tbs2.contents = 'UNDO' AND tbs2.status = 'ONLINE' AND tbs1.name = tbs2.tablespace_name 
           AND df.ts# = tbs1.ts# )sz,v$parameter param1,v$parameter param2, 
          (SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec 
           FROM v$undostat ) us
    WHERE param1.name = 'undo_retention'  AND param2.name = 'db_block_size'
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

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