查詢表空間的大小和使用率

注：用到的檢視有 dba_data_file 和dba_free_space。

------------------------ ‘SQL指令碼’----------------------------------

select a.tablespace_name,a.bytes/1024/1024 "Total MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/a.bytes) desc;

------------------------sql 指令碼執行結果-----------------------

TABLESPACE_NAME Total MB used MB free MB percent_used

------------------------------ ---------- ---------- ---------- ------------

PRODUCT 32767 9617 23150 29.35

AUD_TBS 5120 976.3125 4143.6875 19.07

UNDOTBS1 32767 3381.5625 29385.4375 10.32

SYSAUX 5120 331.3125 4788.6875 6.47

SYSTEM 5120 252.9375 4867.0625 4.94

MYSQL 200 4.875 195.125 2.44

PROCESSING_LOG_TBS 5000 2.1875 4997.8125 .04

IDEX 5120 2 5118 .04

查詢表空間的大小和使用率

相關文章