3 Replies Latest reply: Feb 24, 2014 8:42 PM by Krishna-Oracle RSS

    Reducir espacio en datafiles SYSAUX

    user10068542

      Hola,

      Quisiera reducir espacio en el tablespace SYSAUX y además identificar cuales son los segmentos que hace la marca de HWM por cada datafile.

      Ya que está creciendo demasiado.

      La versión de mi base de datos es Oracle 11g.

        • 1. Re: Reducir espacio en datafiles SYSAUX
          Emad Al-Mousa

          can you please write your question in "English " ?

          • 2. Re: Reducir espacio en datafiles SYSAUX
            onkar.nath

            Below commands will give you the size upto which DBF can be reduced alongwith command:

             

            set verify off

            column file_name format a50 word_wrapped

            column smallest format 999,990 heading "Smallest|Size|Poss."

            column currsize format 999,990 heading "Current|Size"

            column savings  format 999,990 heading "Poss.|Savings"

            break on report

            compute sum of savings on report

             

            column value new_val blksize

            select value from v$parameter where name = 'db_block_size'

            /

             

            select file_name,

                   ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

                   ceil( blocks*&&blksize/1024/1024) currsize,

                   ceil( blocks*&&blksize/1024/1024) -

                   ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

            from dba_data_files a,

                 ( select file_id, max(block_id+blocks-1) hwm

                     from dba_extents

                    group by file_id ) b

            where a.file_id = b.file_id(+)

            /

             

            column cmd format a75 word_wrapped

             

            select 'alter database datafile '''||file_name||''' resize ' ||

                   ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd

            from dba_data_files a,

                 ( select file_id, max(block_id+blocks-1) hwm

                     from dba_extents

                    group by file_id ) b

            where a.file_id = b.file_id(+)

              and ceil( blocks*&&blksize/1024/1024) -

                  ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

            /

             

             

            Onkar

            • 3. Re: Reducir espacio en datafiles SYSAUX
              Krishna-Oracle

              HI,

              Kindly refer

              How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

               

              Thanks,

              Krishna