自分用メモ。
テーブルスペース毎の領域量を出力する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
