sqlplus 語法查詢表格空間使用狀況

oracle 10g 可以利用OEM去觀察各tablespace的使用狀況！
但是如果我是要以sqlplus去下sql指令，來了解我資料庫的tablespace使用狀況，
要如何下手呢？

首先，我先提到兩個和oracle DBA管理用的table，分別是dba_data_files，dba_free_space。
dba_data_files是記錄所有的tablespace資訊，名稱，ID，多大？等等
dba_free_space，望文生義，是記錄所有在dba_data_files有登記的tablespace的剩餘空間。

SQL> set pagesize 20;
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_data_files
2 group by tablespace_name order by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
EIP 1024
...
...
...
（略過）


12 rows selected.

SQL>
以上就是以MB為單位，把全部tablespace的大小資訊給抓取出來。

接著看，我們要來抓dba_free_space的資料。
SQL> set pagesize 20;
SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_free_space
2 group by tablespace_name order by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
EIP 424.375
...
...
...
（略過）

11 rows selected.

SQL>

OK！於是從而查出，EIP這個tablespace本身有1G之大，而剩餘約424MB之多。其他類推！
也許抓到的資料筆數不相稱，那可能是oracle系統本身多帶的東東，不重要！
總之，利用這兩個table資訊，就能讓我們了解到目前資料庫的空間使用狀況。
不管你有沒有設定tablespace自動增長，都應該要常常去掌控這兩個table的比較結果。
不然，資料庫爆掉，或是硬碟空間吃光，都是件不小的災難。

當然，聰明點的朋友，應該會想到用更進階的語法，把我上面這兩個查詢結果整合在一起。
所以，我也找了一個範例：

SQL> select b.file_id, b.tablespace_name, b.bytes,
2 (b.bytes-sum(nvl(a.bytes,0))),
3 sum(nvl(a.bytes,0)),
4 sum(nvl(a.bytes,0))/(b.bytes)*100
5 from dba_free_space a, dba_data_files b
6 where a.file_id=b.file_id
7 group by b.tablespace_name, b.file_id, b.bytes
8 order by b.file_id;


進一步註釋說明，我用中括號包著：
SQL> select
b.file_id ［檔案ID編號］,
b.tablespace_name ［表格空間名稱］,
b.bytes ［位元組數］,
(b.bytes-sum(nvl(a.bytes,0))) ［已使用的位元組］,
sum(nvl(a.bytes,0)) ［剩餘空間］,
sum(nvl(a.bytes,0))/(b.bytes)*100 ［剩餘百分比］

from dba_free_space a,dba_data_files b［這裡表示出a是dba_free_space，b是dba_data_file］

where a.file_id=b.file_id［用相同的file_id來建立其相依］

group by b.tablespace_name,b.file_id,b.bytes

order by b.file_id ;

執行的結果就可看出各表格空間的使用狀況了。
但由於擷取的欄位，可能還是比較多了些，可能顯示起來比較難看清楚。

可以採下面較簡明的寫法：
SQL> select b.tablespace_name, sum(nvl(a.bytes,0))/(b.bytes)*100
2 from dba_free_space a,dba_data_files b where a.file_id=b.file_id
3 group by b.tablespace_name,b.file_id,b.bytes order by b.file_id ;

直接看各表格空間的己使用比率！

後記：
其實我查過學貫出的Oracle DataBase 10g DBA技術手冊，
裡頭寫的查詢語法是像下面這樣的方式...

SQL> select ts.name tablespace, trim(substr(df.name,1,100)) filename,
2 df.bytes/1048576000 allocated_gb,
3 ((df.bytes/1048576000) - nvl(sum(dfs.bytes)/1048576000,0)) used_gb,
4 nvl(sum(dfs.bytes)/1048576000,0) free_space_gb
5 from v$datafile df left outer join dba_free_space dfs
6 on df.file# = dfs.file_id join v$tablespace ts
7 on df.ts# = ts.ts#
8 group by ts.name, dfs.file_id, df.name, df.file#, df.bytes
9 order by filename;

它用了v$tablespace，v$datafile，dba_free_space三個table來組成查詢條件式，
不像最上面的語法，只用了dba_data_file和dba_free_space兩個。
所以，看個人的習慣針酌使用囉。