-
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
-
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
-
Hello
The retention time is 10800.
-
Hi,
Are you asking for releasing space from UNDO tablespace or UNDO segments ?
Dilipkumar Patel.
-
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"
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|