--------------------------------------------------------------------------
--File: getspaceinfo.sql
--Function: get space info of all tablespaces
--Author: NinGoo
--Create: 2006-3-11
--Modify:
--Version: 1.0
--------------------------------------------------------------------------

注：在oracle10g中提供了一个新的视图，可以直接获得表空间的空间使用情况

SQL> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
--------------------------------- -------------------- -------------------------------- --------------------
SYSAUX 14384 4194302 .342941448
SYSTEM 33192 4194302 .791359325
TEMP 0 4194302 0
UNDOTBS1 160 4194302 .003814699
USERS 16 4194302 .00038147

set echo off
set feedback off
set verify off
set linesize 80

column tablespace_name for a20 heading 'Tablespace'
column filename for a25 heading 'File'
undefine paraname
ttitle center 'The space infomation of all tablespaces' skip 3
--------------------------------------------------------------------------
select b.tablespace_name,
substr(b.file_name,instr(b.file_name,'',-1,1)+1) as "filename",
a.bytes/1024/1024 as "Free(M)",
b.bytes/1024/1024 as "Total(M)",
(1-trunc(a.bytes/b.bytes,2))*100 as "Used(%)"
from (select tablespace_name,file_id,sum(bytes) as bytes
from dba_free_space group by tablespace_name,FILE_ID) a,
dba_data_files b
where a.file_id=b.file_id
union all
select c.tablespace_name,'_Total_Tablespace_',
c.bytes,
d.bytes,
(1-trunc(c.bytes/d.bytes,2))*100
from (select tablespace_name,sum(bytes)/1024/1024 as bytes
from dba_free_space group by tablespace_name) c,
(select tablespace_name,sum(bytes)/1024/1024 as bytes
from dba_data_files group by tablespace_name having count(1)>1) d
where c.tablespace_name=d.tablespace_name
order by 1,2;
--------------------------------------------------------------------------
set feedback on
set verify on
ttitle off