preventing unable to extend tablespace

[steeve123]

I want to be able to monitor tablespace that is near 100% full.
But only tablespaces that has datafile that are not autoextend.

Also,
if the datafile is autoextend on, i want to see the tablespace only if it is near his maxsize value.

Complication:
The thing is that i might have more than one datafile related to my tablespace.

And i might have the following situation:
tablespace USERS
datafile 1: 99% autoextend on not near maxsize value
datafile 2: 99% autoextend off;

So i would not want to see that USERS tablespace in my query. Since it is not near his maxsize value.

But i would like to see this scenario:
tablespace USERS2
datafile 1: 99% autoextend on near maxsize value
datafile 2: 99% autoextend off;


Does any one has that kind of query?

This query is close to what i need but does not work well:

select d.tablespace_name "TABLESPACE",
sum(d.bytes)/1048576 "SIZE (M)",
100 -(round((FREESPCE/(sum(d.bytes)/1048576))*100)) "USED (%)"
FROM dba_data_files d,
( SELECT round(sum(f.bytes)/1048576,2) FREESPCE,
f.tablespace_name Tablespc
FROM dba_free_space f
GROUP BY f.tablespace_name)
WHERE d.tablespace_name = Tablespc
group by d.tablespace_name,FREESPCE
order by 1 asc

[marist89]

ah, been there, done that about 5 years ago. Let me look at the code and get back to you...

[tamilselvan]

Try this:

select tbs_name, file_name, file_id, 
         autoextensible, maxsize, cur_size_in_mb, free,
       ( case when autoextensible = 'YES' 
          then round( ( cur_size_in_mb-free)/greatest(maxsize,1) ,2) 
          else round( ((cur_size_in_mb -free) / cur_size_in_mb ), 2) 
          end
         ) "% utilization"
from (
select tablespace_name TBS_NAME, 
        file_name, file_id, autoextensible , 
       trunc(maxbytes/1024/1024) maxsize,
       bytes/1024/1024 cur_size_in_mb, 
	   (select trunc(sum(bytes)/1024/1024)  
                  from dba_free_space b
	     where b.file_id = a.file_id) free
from dba_data_files a)

You can add filter condition on "% Utilization" column on the outer query.

Tamil