3 Replies Latest reply: Nov 30, 2012 5:11 AM by user522961 RSS

    HWM and Undo

    user522961
      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
          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
            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
              Thanks to all.