This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

I need DPA alert for tablespace freespace based on bytes

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.
  • 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 emoticons_plus.png

    order by 1