Select substr(b.tablespace_name,1,20) TableSpace, Total_Max_MB, NVL(Total_Used_MB,0) Total_Used_MB,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0)),0) “Free_MB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/1024,2) “Free_GB”,
ROUND((Total_Max_MB – NVL(Total_Used_MB,0))/Total_Max_MB * 100,2) “Free%”
from (select tablespace_name, round(sum(bytes/1024/1024),2) Total_Used_MB
from dba_segments
group by tablespace_name) a,
(select tablespace_name,
round(sum(decode(maxbytes,0,bytes/1024/1024,maxbytes/1024/1024)),0) Total_Max_MB,
round(sum(user_bytes/1024/1024),0) Total_User_MB
from dba_data_files
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name (+)
and ROUND((Total_Max_MB – Total_Used_MB)/Total_Max_MB * 100,2) <=100
order by “Free%” desc;

TTITLE “Total buffers used by individual objects.”
COL OWNER FOR A8
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A10
select obj.owner “OWNER”, obj.object_name “OBJECT_NAME”,
obj.object_type “OBJECT_TYPE”, COUNT(distinct bh.block#) “# Buffers”
from dba_objects obj, v$bh bh
where obj.object_id = bh.objd and
obj.owner != ‘SYS’
group by obj.owner, obj.object_name, obj.object_type
having COUNT(distinct bh.block#) >= 2
order by 4;