RE: how to shrink undo tablespace on 10GR2?
Date: Tue, 19 Oct 2010 10:53:53 -0400
Message-ID: <C3F905167E081B418BFC63B8668D52FF1B81B23926_at_GOXEXVS03.fplu.fpl.com>
It might be a clunky way of resizing the UNDO space, but I generally create a new undo tablespace, set that one to be the default, drop the current and be done with it.
You can also use this query to see if anything has a hold on undo space.
SELECT substr(s.sid||','||s.serial#,1,15) SID_SERIAL, substr(NVL(s.username, 'None'),1,20) orauser, substr(s.program,1,20) program, substr(r.name,1,20) undoseg, substr(t.used_ublk * TO_NUMBER(x.value)/1024||'K',1,20) "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
/
Jeremy
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of dba1 mcc
Sent: Tuesday, October 19, 2010 10:44 AM
To: oracle-l_at_freelists.org
Cc: oracle-db-l_at_Groups.ITtoolbox.com
Subject: how to shrink undo tablespace on 10GR2?
We have 10GR2 on Linux server. I found one database "undo" tablespace has been extend to 32 GB size. I used 'dba_free_space" to check and found this data file actually is empty. I tried to "resize" it back to small size but I can only resize it to 28GB. I knew this may be cause by "high water mark".
Does there has way to shrink it to small?
Thanks.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 19 2010 - 09:53:53 CDT