d.contents TS_Type,
d.status TS_Status,
d.extent_management TS_ExtentManagement,
Round(NVL(a.bytes / 1024 / 1024,0),2) TS_Size,
Round(NVL(a.bytes - NVL(f.bytes,0) / 1024 / 1024,2) TS_UsedSize,
Round(NVL((a.bytes - NVL(f.bytes,0)) / a.bytes,4) TS_Used,
Round(NVL(a.maxbytes / 1024 / 1024,
Round((NVL(a.bytes - NVL(f.bytes,0)) /NVL(a.maxbytes,4) TS_AllUSED,
Round(a.incrementBy*NVL((select BLOCK_SIZE from dba_tablespaces where tablespace_name=a.tablespace_name),0)/1024/1024,2) incrementBy
From sys.dba_tablespaces d,
(Select tablespace_name,Sum(bytes) bytes,sum(maxbytes) maxbytes,sum(increment_by) incrementBy From dba_data_files Group By tablespace_name) a,Sum(bytes) bytes From dba_free_space Group By tablespace_name) f Where d.tablespace_name = a.tablespace_name(+) And d.tablespace_name = f.tablespace_name(+) And Not (d.extent_management Like 'LOCAL' And d.contents Like 'TEMPORARY')
Union All
Select d.tablespace_name TS_Name,
d.contents TS_Type,
Round(NVL(t.bytes,
Round(NVL(t.bytes / a.bytes,
Round( NVL(t.bytes,0) /NVL(a.maxbytes,2) incrementBy
From sys.dba_tablespaces d,sum(increment_by) incrementBy From dba_temp_files Group By tablespace_name) a,Sum(bytes_cached) bytes From v$temp_extent_pool Group By tablespace_name) t
Where d.tablespace_name = a.tablespace_name(+) And
d.tablespace_name = t.tablespace_name(+) And
d.extent_management Like 'LOCAL' And
d.contents Like 'TEMPORARY'
Order By TS_Name
TS_NAM表空间
TS_TYPE空间类型
TS_STATUS状态
TS_ExtentManagement 管理方式
TS_Size 文件大小
TS_UsedSize 当前使用大小
TS_Used,当前使用率
TS_Size 总扩展大小
TS_AllUSED,总扩展大小使用率
incrementBy 自增量
单位:MB