Scripts:查看表空间的语句脚本

col "Tablespace" for a13   
col "Used MB" for 99,999,999   
col "Free MB" for 99,999,999   
col "Total MB" for 99,999,999   
   
select fs.tablespace_name "Tablespace",   
    (df.totalspace - fs.freespace) "Used MB",   
    fs.freespace "Free MB",   
    df.totalspace "Total MB",   
    round(100 * (fs.freespace / df.totalspace)) "Pct. Free"   
from   
    (select tablespace_name,   
        round(sum(bytes) / 1048576) TotalSpace   
      from dba_data_files   
      group by tablespace_name) df,   
    (select tablespace_name,   
        round(sum(bytes) / 1048576) FreeSpace   
      from dba_free_space   
      group by tablespace_name) fs   
where df.tablespace_name = fs.tablespace_name   

;

set linesize 150 pagesize 150
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(+);