查询表空间使用情况

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;

阅读更多
个人分类: ORACLE
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

查询表空间使用情况

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭