ablog

不器用で落着きのない技術者のメモ

表領域単位で空き領域を調べる SQL

Oracle Perl Oracle 
SQL> select tablespace_name, sum(bytes)/1024/1024/1024 gb from dba_free_space group by tablespace_name;

TABLESPACE_NAME                        GB
------------------------------ ----------
UNDOTBS1                       2.66601563
SYSAUX                         1.076660156
USERS                          5.8731079
SYSTEM                         .001159668
INDX                            11.652771

空き領域じゃなくて、空きエクステントか。だから HWM 以下で空いている領域かな。たぶん。
表領域の空き容量を調べる SQL を書いてみた。

SQL> select total.tablespace_name,
		round(total.s/1024/1024, 2) "total(MB)",
		round((total.s-free.s)/1024/1024, 2) "used(MB)",
		round(free.s/1024/1024, 2) "free(MB)",
		round((1-(nvl(free.s,0)/total.s))*100, 0) "pctused(%)"
	from 
		(select tablespace_name, sum(bytes) s 
			from dba_data_files group by tablespace_name) total,
		(select tablespace_name, sum(bytes) s 
			from dba_free_space group by tablespace_name) free
	where 
		total.tablespace_name = free.tablespace_name(+);

TABLESPACE  TOTAL(MB)   USED(MB)   FREE(MB) PCTUSED(%)
---------- ---------- ---------- ---------- ----------
UNDOTBS1          201    25.6875   175.3125 12.7798507
SYSAUX            141   122.9375    18.0625 87.1897163
USERS          117.75   115.4375     2.3125 98.0360934
SYSTEM            301   280.9375    20.0625 93.3347176

参考