AW: how to shrink undo tablespace on 10GR2?
- From: "Petr Novak" <Petr.Novak@xxxxxxxxxxxx>
- To: <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx>, <mccdba1@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 19 Oct 2010 17:03:01 +0200
Following query shows free space at datafile end (=possible for shrink)
column freem heading Free(MB) format 999G999D99
select /*+ RULE */ f.tablespace_name,f.file_id, sum(f.bytes)/1024/1024 freem
from dba_free_space f,
(select file_id,max(block_id) max_block from dba_extents
group by file_id) e where
f.file_id=e.file_id(+) and f.block_id>nvl(e.max_block,0)
group by f.tablespace_name,f.file_id
order by f.tablespace_name,f.file_id;
Best Regards,
Petr
-----Ursprüngliche Nachricht-----
Von: oracle-l-bounce@xxxxxxxxxxxxx im Auftrag von Sheehan, Jeremy
Gesendet: Di 19.10.2010 16:53
An: mccdba1@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Betreff: RE: how to shrink undo tablespace on 10GR2?
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of dba1 mcc
Sent: Tuesday, October 19, 2010 10:44 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-db-l@xxxxxxxxxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » AW: how to shrink undo tablespace on 10GR2? - Petr Novak
