【ORACLE】查询表空间使用率

一、非扩展数据文件表空间

 SELECT  df.tablespace_name,
      df.totalspace_mb,
      fs.freespace_mb,
      df.totalspace_mb-fs.freespace_mb used_mb,
      round((1-fs.freespace_mb/df.totalspace_mb)*100, 2) used_percent
FROM 
  ( SELECT  tablespace_name, round(sum(bytes)/1024/1024) totalspace_mb
  FROM dba_data_filesGROUP BY tablespace_name) df, 
  ( SELECT  tablespace_name, round(sum(bytes)/1024/1024) freespace_mb
  FROM dba_free_spaceGROUP BY tablespace_name) fs
WHERE df.tablespace_name=fs.tablespace_name;

二、可扩展数据文件表空间

 SELECT  df.tablespace_name,
      df.totalspace_mb,
      fs.freespace_mb,
      df.totalspace_mb-fs.freespace_mb used_mb,
      round((1-fs.freespace_mb/df.totalspace_mb)*100, 2) used_percent,
      df.tsp_maxextend_mb,
      round(((df.totalspace_mb-fs.freespace_mb)/df.tsp_maxextend_mb)*100, 2) tsp_used_percent
FROM 
  ( SELECT  tablespace_name, round(sum(bytes)/1024/1024) totalspace_mb, round(sum(maxbytes)/1024/1024) tsp_maxextend_mb
  FROM dba_data_filesGROUP BY tablespace_name) df, 
  ( SELECT  tablespace_name, round(sum(bytes)/1024/1024) freespace_mb
  FROM dba_free_spaceGROUP BY tablespace_name) fs
WHERE df.tablespace_name=fs.tablespace_name;