监控数据库表空间使用情况

1.按用户查表空间使用情况 

select a.tablespace_name 表空间名称,total_space/(1024*1024)  总量,free_space/(1024*1024)  剩余量, 
trunc((free_space/total_space)*100)||'%' 剩余比例 
from 
(select tablespace_name,sum(bytes) free_space  from dba_free_space 
group by tablespace_name) a, 
(select tablespace_name,sum(bytes)  total_space from dba_data_files 
group by tablespace_name) b 
where a.tablespace_name = b.tablespace_name   order by (free_space/total_space ) 

2.查询某个用户的表占用空间大小 
select segment_name,segment_type,tablespace_name,bytes,initial_extent, 
next_extent from dba_segments where owner='CAL'   order by bytes desc; 

3.监控数据库表空间使用情况 

Select d.tablespace_name,Space "Sum_space(M)",Blocks sum_blocks,Space-nvl(free_space,0) "Used_space(M)", 
       round((1-nvl(free_space,0)/Space)*100,2) "Used_rate(%)",free_space "Free_space(M)" 
From (Select tablespace_name,round(Sum(bytes)/(1024*1024),2) Space,Sum(blocks) Blocks From dba_data_files Group By tablespace_name) d, 
(Select tablespace_name,round(Sum(bytes)/(1024*1024),2) free_space From dba_free_space Group By tablespace_name) f 
Where d.tablespace_name = f.tablespace_name(+) 
Union All 
Select d.tablespace_name,Space "Sum_space(M)",blocks sum_blocks,used_space "Used_space(M)", 
       round(nvl(used_space,0)/Space*100,2) "Used_rate(%)",nvl(free_space,0) "Free_space(M)" 
From (Select tablespace_name,round(Sum(bytes)/(1024*1024),2) Space,Sum(blocks) blocks From dba_temp_files Group By tablespace_name) d, 
(Select tablespace_name,round(Sum(bytes_used)/(1024*1024),2) used_space,round(Sum(bytes_free)/(1024*1024),2) free_space 
From v$temp_space_header Group By tablespace_name) f 
Where d.tablespace_name=f.tablespace_name(+);