搬磚頭

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

          查看表空間使用情況

          Posted on 2007-03-05 22:45 生活在別處 閱讀(9729) 評(píng)論(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語(yǔ)句:
          ?? 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;


          只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。


          網(wǎng)站導(dǎo)航:
           
          主站蜘蛛池模板: 桐乡市| 高台县| 陆丰市| 武邑县| 嘉善县| 皮山县| 漳州市| 清远市| 辽中县| 雷波县| 皋兰县| 霸州市| 邳州市| 常宁市| 新蔡县| 宽甸| 任丘市| 塘沽区| 同德县| 六枝特区| 通州区| 宁夏| 梧州市| 凤阳县| 裕民县| 叙永县| 邛崃市| 林口县| 杭锦旗| 县级市| 伊春市| 高台县| 同江市| 蒙山县| 永清县| 如皋市| 聊城市| 获嘉县| 永川市| 白朗县| 宁夏|