RE: how to shrink undo tablespace on 10GR2?

From: Sheehan, Jeremy <JEREMY.SHEEHAN_at_nexteraenergy.com>
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

Original text of this message