This discussion is archived
3 Replies Latest reply: Nov 30, 2012 3:11 AM by user522961 RSS

HWM and Undo

user522961 Newbie
Currently Being Moderated
Hi ,
on 11g R2 on Win 2008,
my undo file is 5Gb and occupied only with 90 Mb. I want to resize to for example 4 Gb. It fails saying that there are users' data. Even if I just restarted database. I think that it is because of HWM.

How can I reduce it to be able to resize my undo file ?
Thanks.
  • 1. Re: HWM and Undo
    Fran Guru
    Currently Being Moderated
    SQL> CREATE UNDO TABLESPACE undotbs02 DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 4G;
    SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
    SQL> ALTER TABLESPACE undotbs01 OFFLINE;
    SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
  • 2. Re: HWM and Undo
    Niket Kumar Pro
    Currently Being Moderated
    check how much a tablespace shrinks wiith resize command.

    column tablespace_name format a10
    column file_name format a32
    column file_mb format 9999990
    column hwm_mb format 9999990
    column used_mb format 9999990
    column shrnk_mb format 9999990

    break on report
    compute sum of file_mb on report
    compute sum of hwm_mb on report
    compute sum of used_mb on report
    compute sum of shrnk_mb on report

    select a.*
    , file_mb-hwm_mb shrnk_mb
    from (
    select /*+ rule */
    a.tablespace_name,
    a.file_name,
    a.bytes/1024/1024 file_mb,
    b.hwm*d.block_size/1024/1024 hwm_mb,
    b.used*d.block_size/1024/1024 used_mb
    from
    dba_data_files a,
    (select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
    from dba_extents
    group by file_id) b,
    dba_tablespaces d
    where a.file_id = b.file_id
    and a.tablespace_name = d.tablespace_name
    ) a
    order by a.tablespace_name,a.file_name;
  • 3. Re: HWM and Undo
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points