关闭

查看表空间的容量

275人阅读 评论(0) 收藏 举报
分类:
set line 300 pages 2000

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(+);



--------eg.
TABLESPACE_NAME                        Sum_space(M)           SUM_BLOCKS        Used_space(M)         Used_rate(%)        Free_space(M)
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
BOSSDATA                                     784248            100383744            187199.44                23.87            597048.56
SYSTEM                                         2047               262016                299.5                14.63               1747.5
UNDOTBS1                                      40867              5230976              1192.06                 2.92             39674.94
TEMP                                          16380              2096640                11786                71.95                 4594


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:20080次
    • 积分:406
    • 等级:
    • 排名:千里之外
    • 原创:22篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类