查看表空间使用情况

SQL>select sum(bytes)/(1024*1024) as free_space,tablespace_name from
dba_free_space group by tablespace_name;


SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,
C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES
"% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME= B.TABLESPACE_NAME AND
A.TABLESPACE_NAME= C.TABLESPACE_NAME;


SQL>column tablespace_name format a18;
SQL>column Sum_M format a12;
SQL>column Used_M format a12;
SQL>column Free_M format a12;
SQL>column pto_M format 9.99;
SQL>select s.tablespace_name,ceil(sum(s.bytes/1024/1024))||'M'
Sum_M,ceil(sum(s.UsedSpace/1024/1024))||'M'
Used_M,ceil(sum(s.FreeSpace/1024/1024))||'M' Free_M, 
sum(s.UsedSpace)/sum(s.bytes) PTUSED
from (select b.file_id,b.tablespace_name,b.bytes, (b.bytes-sum(nvl(a.bytes,0)))
UsedSpace, sum(nvl(a.bytes,0)) FreeSpace,(sum(nvl(a.bytes,0))/(b.bytes)) * 100
FreePercentRatio
from sys.dba_free_space a,sys.dba_data_files b
where a.file_id(+)=b.file_id group by
b.file_id,b.tablespace_name,b.bytes order by b.tablespace_name) s
group by s.tablespace_name
order by sum(s.FreeSpace)/sum(s.bytes) desc; 


select a.tablespace_name "表空间名称",
100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
round(a.bytes_alloc/1024/1024,2) "容量(M)",
round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
Largest "最大扩展段(M)",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name,sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f group by tablespace_name) a,
(select f.tablespace_name,sum(f.bytes) bytes_free
from dba_free_space f group by tablespace_name) b,
(select round(max(ff.length)*16/1024,2) Largest,ts.name tablespace_name
from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
group by ts.name, tf.blocks) c where a.tablespace_name = b.tablespace_name and
a. tablespace_name = c.tablespace_name;


SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2)
TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME)D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;