ORACLE数据库查询用户表空间使用率
当我们需要查询oracle数据库某个用户下的表空间总容量，已使用容量等信息时，可以使用一下sql查看总容量，已使用容量和占用率：
SELECT ROUND(SUM(allocSpace + extensibleSpace)) AS maxSpace, ROUND(SUM(usedSpace)) AS usedSpace, ROUND(SUM(usedSpace) * 100 / SUM(allocSpace + extensibleSpace), 2) AS ratio FROM ( SELECT b.TablespaceName, SUM(b.UsedByte) / 1024 / 1024 AS allocSpace, (SUM(b.UsedByte) - SUM(a.FreeByte)) / 1024 / 1024 AS usedSpace, SUM(a.FreeByte) / 1024 / 1024 AS freeSpace, SUM(b.ExtensibleByte) / 1024 / 1024 AS extensibleSpace, SUM(a.FreeByte + b.ExtensibleByte) * 100 / SUM(b.UsedByte + b.ExtensibleByte) AS freeRatio, SUM(a.Extend) AS totalExtends FROM ( SELECT DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - bytes), 1, maxbytes - bytes, 0), 0) ExtensibleByte, bytes UsedByte, file_id FileID, tablespace_name TablespaceName FROM dba_data_files UNION ALL SELECT DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - bytes), 1, maxbytes - bytes, 0), 0) ExtensibleByte, bytes UsedByte, file_id FileID, tablespace_name TablespaceName FROM dba_temp_files ) b, ( SELECT SUM(bytes) FreeByte, COUNT(*) Extend, file_id FileID, tablespace_name TablespaceName FROM dba_free_space GROUP BY file_id, tablespace_name UNION ALL SELECT SUM(bytes_free) FreeByte, COUNT(*) Extend, file_id FileID, tablespace_name TablespaceName FROM v$temp_space_header GROUP BY file_id, tablespace_name ) a WHERE b.FileID = a.FileID(+) AND b.TablespaceName = a.TablespaceName(+) AND b.TablespaceName IN ( SELECT tablespace_name FROM dba_ts_quotas WHERE username = #{userName,jdbcType=VARCHAR} UNION SELECT default_tablespace FROM dba_users WHERE username = #{userName,jdbcType=VARCHAR} ) GROUP BY b.TablespaceName ) t替换sql中的username条件，查询结果如下：
也可以去掉最外层的查询，直接查询以下内容：
表空间名,已分配空间,已使用空间,剩余空间,可扩展空间,空闲比例,碎片数量SELECT b.TablespaceName, SUM(b.UsedByte) / 1024 / 1024 AS allocSpace, (SUM(b.UsedByte) - SUM(a.FreeByte)) / 1024 / 1024 AS usedSpace, SUM(a.FreeByte) / 1024 / 1024 AS freeSpace, SUM(b.ExtensibleByte) / 1024 / 1024 AS extensibleSpace, SUM(a.FreeByte + b.ExtensibleByte) * 100 / SUM(b.UsedByte + b.ExtensibleByte) AS freeRatio, SUM(a.Extend) AS totalExtends FROM ( SELECT DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - bytes), 1, maxbytes - bytes, 0), 0) ExtensibleByte, bytes UsedByte, file_id FileID, tablespace_name TablespaceName FROM dba_data_files UNION ALL SELECT DECODE(autoextensible, 'YES', DECODE(SIGN(maxbytes - bytes), 1, maxbytes - bytes, 0), 0) ExtensibleByte, bytes UsedByte, file_id FileID, tablespace_name TablespaceName FROM dba_temp_files ) b, ( SELECT SUM(bytes) FreeByte, COUNT(*) Extend, file_id FileID, tablespace_name TablespaceName FROM dba_free_space GROUP BY file_id, tablespace_name UNION ALL SELECT SUM(bytes_free) FreeByte, COUNT(*) Extend, file_id FileID, tablespace_name TablespaceName FROM v$temp_space_header GROUP BY file_id, tablespace_name ) a WHERE b.FileID = a.FileID(+) AND b.TablespaceName = a.TablespaceName(+) AND b.TablespaceName IN ( SELECT tablespace_name FROM dba_ts_quotas WHERE username = '' UNION SELECT default_tablespace FROM dba_users WHERE username = '' ) GROUP BY b.TablespaceName