ORACLE表空间检查语句

Select d.tablespace_name TS_Name,
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), 0) / 1024 / 1024, 2) TS_UsedSize,
     Round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes, 0), 4) TS_Used,
      Round(NVL(a.maxbytes / 1024 / 1024, 0),2) TS_Size,
      Round((NVL(a.bytes - NVL(f.bytes, 0), 0)) /NVL(a.maxbytes,0), 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,
     (Select tablespace_name, 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,
     d.status TS_Status,
     d.extent_management TS_ExtentManagement,
     Round(NVL(a.bytes / 1024 / 1024, 0), 2) TS_Size,
     Round(NVL(t.bytes, 0) / 1024 / 1024, 2) TS_UsedSize,
     Round(NVL(t.bytes / a.bytes, 0), 4) TS_Used,
      Round(NVL(a.maxbytes / 1024 / 1024, 0),2) TS_Size,
      Round( NVL(t.bytes, 0) /NVL(a.maxbytes,0), 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_temp_files Group By tablespace_name) a,
     (Select tablespace_name, 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