1 Reply Latest reply on Jul 18, 2017 12:20 PM by mandevil

    we need alert from DPA for autoextended tablespaces

    sureshrs22

      We have configured DPA tool for tablespace freespace but since in our environment all the datafiles is autoextend enabled it will reach max 32GB and we need to get notification if any datafile reaches 25GB is it possible?

       

      In tablespace freespace alert, there is alert notification on only the % of current datafile size and also DPA says it monitors only non autoextend tablespaces

        • Re: we need alert from DPA for autoextended tablespaces
          mandevil

          I believe I've used this before. This looks at max bytes for the tablespace and calculates a percent used.

          You could change the select to just return the bytes used or customize for your purposes.

          Let us know how it goes!

           

          select df.tablespace_name, round(((MAX_BYTES-((MAX_BYTES-BYTES)+FREE_SPACE)) / MAX_BYTES )*100) "UPercent"

          from

          (select tablespace_name

                ,sum(bytes)/1024/1024 BYTES

                ,sum(decode(sign(maxbytes-bytes),-1,bytes,0,bytes,maxbytes))/1024/1024 MAX_BYTES

          from dba_data_files

          group by tablespace_name ) df,

          (select tablespace_name,sum(bytes)/1024/1024 FREE_SPACE

          from dba_free_space

          group by tablespace_name) FS

          where df.tablespace_name = fs.tablespace_name

          order by 1