Tuesday, June 30, 2009

check tablespace usage

kalo gak ada toad, ato tools yang mendukung untuk check tablespace
gimana caranya ya, ya pake sqlplus sajah n jalankan query berikut :

col "Tablespace" for a20
col "Allocated" for 999999999999

select ddf.TABLESPACE_NAME "Tablespace",
ddf.BYTES "Allocated",
round(((ddf.BYTES-dfs.BYTES)/ddf.BYTES)*100,2) "Used %",
round((1-((ddf.BYTES-dfs.BYTES)/ddf.BYTES))*100,2) "Free %"
from (select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_data_files
group by TABLESPACE_NAME) ddf,
(select TABLESPACE_NAME,
sum(BYTES) bytes
from dba_free_space
group by TABLESPACE_NAME) dfs
where ddf.TABLESPACE_NAME=dfs.TABLESPACE_NAME
order by ((ddf.BYTES-dfs.BYTES)/ddf.BYTES) desc
/

No comments:

Post a Comment