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.

we need alert from DPA for autoextended tablespaces

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

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

    order by 1