1. 查看表空间的名字和所属文件以及大小
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0)||'M' total_space
from dba_data_files order by tablespace_name;
2. 查看表空间名字和大小
SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/(1024 * 1024)), 0)||'M' TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
3. 查看表空间名字和未使用的空间百分比
select a.tablespace_name,free*100/total "% FREE" from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;
4. 查看表空间信息(表空间名字,表空间大小,以使用的字节,空闲字节,使用率,未使用的空间百分比)
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;