搬砖头

Knocking on Heaven's Door
posts - 34, comments - 6, trackbacks - 0, articles - 0

查看表空间使用情况

Posted on 2007-03-05 22:45 生活在别处 阅读(9721) 评论(0)  编辑  收藏

查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

====================================

set pages 100
col ts_name form a20 head 'Tablespace'
col pieces form 9990 head 'Pcs'
col ts_size form 999,990 head 'SizeMb'
col largestpc form 999,990 head 'LrgMB'
col totalfree form 999,990 head 'FreeMb'
col pct_free form 990 head '%Free'
col whatsused form 999,990 head 'Used'
col pct_used form 990 head '%Used'
col problem head 'Prob??'
--spool /tmp/tbs_size.log

select q2.other_tname ts_name, pieces, ts_size ts_size,
        nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
        nvl(round((total_free/ts_size)*100,2),0) pct_free,
        ts_size-total_free whatsused,
        nvl(100-round((total_free/ts_size)*100,2),100) pct_used,
        decode(nvl(100-round((total_free/ts_size)*100,0),100),
               85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
               92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
               98,'+++++',99,'+++++',100,'+++++','') problem
  from (select dfs.tablespace_name,count(*) pieces,
               round(max(dfs.bytes)/1024/1024,2) largest_chunk,
               round(sum(dfs.bytes)/1024/1024,2) total_free
         from dba_free_space dfs group by tablespace_name) q1,
       (select tablespace_name other_tname,
               round(sum(ddf2.bytes)/1024/1024,2) ts_size
          from dba_data_files ddf2 group by tablespace_name) q2
 where q2.other_tname = q1.tablespace_name(+)
 order by nvl(100-round((total_free/ts_size)*100,0),100) desc;


====================================

查看表空间使用情况的SQL语句:
   SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM  (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name  

===================================== 
rem    fsfi value compute
    rem    fsfi.sql
    column fsfi format 999,99
    select tablespace_name,sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))) fsfi
    from dba_free_space
    group by tablespace_name order by 1;
    spool fsfi.rep;
    /
    spool off;


只有注册用户登录后才能发表评论。


网站导航: