えいのうにっき

a-knowの日記です

Oracle・テーブルスペース毎の領域量出力SQL

自分用メモ。

テーブルスペース毎の領域量を出力するSQL

TTITLE LEFT '========== TABLE SPACE AVAILABILITY =========='
select sumdf.tablespace_name "TS NAME", to_char(sumdf.total_bytes, '999,999,999,990') "TS TOTAL(bytes)",
  to_char(sumdf.total_bytes - sumfs.free_bytes, '999,999,999,990')  "TS USED(bytes)",
  to_char(sumfs.free_bytes, '999,999,999,990') "TS UNUSED(bytes)",
  to_char((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100, 
  '990.99') || '%' "TS UNUSED SIZE(%)",
  TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF') "CHECK TIME"
from (select df.tablespace_name, sum(df.bytes) total_bytes
      from dba_data_files df
      group by df.tablespace_name) sumdf
left outer join (select fs.tablespace_name, sum(fs.bytes) free_bytes
                 from dba_free_space fs
                 group by fs.tablespace_name) sumfs
on (sumdf.tablespace_name = sumfs.tablespace_name)
order by sumdf.tablespace_name
;
TTITLE OFF

出力結果。

========== TABLE SPACE AVAILABILITY ==========
TS NAME    TS TOTAL(bytes)    TS USED(bytes)     TS UNUSED(bytes)   TS UNUSED SIZE(% CHECK TIME
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
BAS           1,073,741,824          1,114,112      1,072,627,712     99.90%         2011-10-31 00:00:00.000000
AAAS001A        314,572,800        292,487,168         22,085,632      7.02%         2011-10-31 00:00:00.000000
AAAS001B     21,474,836,480     21,042,298,880        432,537,600      2.01%         2011-10-31 00:00:00.000000
AAAS001C     42,949,672,960            196,608     42,949,476,352    100.00%         2011-10-31 00:00:00.000000
AAAS002A     96,636,764,160     79,436,382,208     17,200,381,952     17.80%         2011-10-31 00:00:00.000000
AAAS003A     21,474,836,480      8,960,802,816     12,514,033,664     58.27%         2011-10-31 00:00:00.000000
MAUSER          629,145,600        491,257,856        137,887,744     21.92%         2011-10-31 00:00:00.000000
AABS001A        209,715,200         82,378,752        127,336,448     60.72%         2011-10-31 00:00:00.000000
AABS001B     95,991,889,920     75,439,734,784     20,552,155,136     21.41%         2011-10-31 00:00:00.000000
AABS001C     21,474,836,480     19,054,133,248      2,420,703,232     11.27%         2011-10-31 00:00:00.000000
AABS003A      1,073,741,824        207,749,120        865,992,704     80.65%         2011-10-31 00:00:00.000000
AACS001A        104,857,600          6,488,064         98,369,536     93.81%         2011-10-31 00:00:00.000000
AACS001B     10,737,418,240      3,372,089,344      7,365,328,896     68.59%         2011-10-31 00:00:00.000000
AACS001C      4,294,967,296             65,536      4,294,901,760    100.00%         2011-10-31 00:00:00.000000
AACS001D     10,842,275,840          1,441,792     10,840,834,048     99.99%         2011-10-31 00:00:00.000000
AACS001E      1,073,741,824        116,523,008        957,218,816     89.15%         2011-10-31 00:00:00.000000
BBAXXXX          52,428,800            720,896         51,707,904     98.63%         2011-10-31 00:00:00.000000
BBAYYYY         115,343,360         88,539,136         26,804,224     23.24%         2011-10-31 00:00:00.000000
BBES001A      3,745,513,472      2,399,666,176      1,345,847,296     35.93%         2011-10-31 00:00:00.000000
BBES001B        629,145,600        416,677,888        212,467,712     33.77%         2011-10-31 00:00:00.000000
BBKS001A      4,294,967,296      3,419,078,656        875,888,640     20.39%         2011-10-31 00:00:00.000000
BBKS001B      1,073,741,824         92,405,760        981,336,064     91.39%         2011-10-31 00:00:00.000000
BBKS002A        104,857,600             65,536        104,792,064     99.94%         2011-10-31 00:00:00.000000
BBTEST1         419,430,400        189,661,184        229,769,216     54.78%         2011-10-31 00:00:00.000000
BBTEST2           2,097,152            131,072          1,966,080     93.75%         2011-10-31 00:00:00.000000
CCC           1,073,741,824         39,321,600      1,034,420,224     96.34%         2011-10-31 00:00:00.000000
SYSAUX        1,178,599,424      1,138,622,464         39,976,960      3.39%         2011-10-31 00:00:00.000000
SYSTEM        4,322,230,272      2,941,190,144      1,381,040,128     31.95%         2011-10-31 00:00:00.000000
UNDOTBS1      3,093,299,200         96,665,600      2,996,633,600     96.88%         2011-10-31 00:00:00.000000
USERS         1,073,741,824        265,551,872        808,189,952     75.27%         2011-10-31 00:00:00.000000