34 lines
1.2 KiB
SQL
34 lines
1.2 KiB
SQL
-------------------------------------------------------------------------------------------
|
|
-- SCRIPT: DF.SQL
|
|
-- PURPOSE: Show Oracle tablespace free space in Unix df style
|
|
-- AUTHOR: Tanel Poder [ http://www.tanelpoder.com ]
|
|
-- DATE: 2003-05-01
|
|
-------------------------------------------------------------------------------------------
|
|
|
|
col "% Used" for a6
|
|
col "Used" for a22
|
|
|
|
select t.tablespace_name, t.mb "TotalMB", t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB"
|
|
,lpad(ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*100)||'%', 6) "% Used", t.ext "Ext",
|
|
'|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*20),'#'),20,' ')||'|' "Used"
|
|
from (
|
|
select tablespace_name, trunc(sum(bytes)/1048576) MB
|
|
from dba_free_space
|
|
group by tablespace_name
|
|
union all
|
|
select tablespace_name, trunc(sum(bytes_free)/1048576) MB
|
|
from v$temp_space_header
|
|
group by tablespace_name
|
|
) f, (
|
|
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
|
|
from dba_data_files
|
|
group by tablespace_name
|
|
union all
|
|
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext
|
|
from dba_temp_files
|
|
group by tablespace_name
|
|
) t
|
|
where t.tablespace_name = f.tablespace_name (+)
|
|
order by t.tablespace_name;
|
|
|