1 Reply Latest reply on Jul 20, 2017 11:25 AM by mandevil

    I need DPA alert for tablespace freespace based on bytes

    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 so basically alert based on bytes. 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

       

      In below alert information which is configured it says it does not evaluate auto extend tablespaces

       

      Alert Information

      Type Tablespace Freespace
      Description This Oracle and DB2 alert determines the percent of free space in each tablespace of the monitored database. For Oracle databases, only non-autoextensible online tablespaces are evaluated. For DB2, only database managed tablespaces (DMS) are evaluated.
        • Re: I need DPA alert for tablespace freespace based on bytes
          mandevil

          I've used this query for tablespace sizes previously. Thinking you could modify it since it currently works on a % basis.

          I'd suggest that since you are monitoring all of your tablespaces, you add a case statement to this so it will evaluate to a 0 or 1 (any TBS not exceeded threshold or exceeded threshold).

          Otherwise, you'll have to define an alert for each TBS.

          Once a day should be sufficient for this kind of alert.

           

          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