关闭

查询表空间使用情况

323人阅读 评论(0) 收藏 举报
分类:

1、表空间空闲大小

select t.tablespace_name, sum(t.bytes)/1024/1024 as free

from dba_free_space t

group by t.tablespace_name


2、表空间总大小

select a.tablespace_name, sum(a.bytes)/1024/1024 as total
from dba_data_files a
group by a.tablespace_name


3、使用情况SQL如下

select t1.tablespace_name,
trunc(t2.total, 2) as total_gb,
trunc(t1.free, 2) as free_gb,
round((t2.total - t1.free) / t2.total * 100, 2) as  "使用百分比%"
 from 
(
select t.tablespace_name, sum(t.bytes)/1024/1024/1024 as free
from dba_free_space t
group by t.tablespace_name
) t1,
(
select a.tablespace_name, sum(a.bytes)/1024/1024/1024 as total
from dba_data_files a
group by a.tablespace_name
) t2
where t1.tablespace_name = t2.tablespace_name
order by "使用百分比%" desc;



4、临时表空间使用情况

select a.tablespace_name,
  trunc(sum(a.total_gb),2) total_gb,
  trunc(sum(b.used_gb),2) used_gb,
  trunc(sum(b.used_gb) / sum(a.total_gb) * 100, 2) "used_persent %"
  from 
  (select tablespace_name, sum(bytes) / (1024*1024*1024) total_gb
 from dba_temp_files
group by tablespace_name
  ) a,
 (select t1.tablespace,
         sum(t1.blocks * t2.db_block_size) /(1024*1024*1024) used_gb
    from v$sort_usage t1,
         (select value db_block_size
            from v$parameter
           where name = 'db_block_size'
         ) t2
   group by t1.tablespace
 ) b
 where a.tablespace_name = b.tablespace(+)
 group by a.tablespace_name;

0
0

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