oracle11gR2表空间使用查询-526互联

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)", 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 WHERE a.tablespace_name = b.tablespace_name



SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'SYSAUX' and bytes >1048576 order by bytes desc




SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS  FROM DBA_TABLESPACES T,DBA_DATA_FILES D  WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME;