oracle 表空间查看--包含自动扩展后的使用情况

---查询表空间使用率（包括自动扩展）
col tablespace_name for a40
col CUR_PCT for a10
col EXT_PCT for a10
set linesize  1000
select tablespace,
       current_used_mb,
       total_mb,
       can_extend_mb,
       trunc(current_used_mb / total_mb * 100, 2) || '%' cur_pct,
       trunc(current_used_mb / can_extend_mb * 100, 2) || '%' ext_pct,
       count_file
  from (select a.tablespace_name tablespace,
               trunc((b.total - a.free) / 1024 / 1024, 2) current_used_mb,
               trunc(b.total / 1024 / 1024, 2) total_mb,
               trunc(b.extent_total / 1024 / 1024) can_extend_mb,
               b.count_file count_file
          from (select tablespace_name,
                          sum(nvl(bytes, 0)) total,
                          sum(decode(maxbytes, 0, bytes, maxbytes)) extent_total,
                          count(file_name) count_file
                     from dba_data_files
                    group by tablespace_name) b,
                    (select tablespace_name, sum(bytes) free
                  from dba_free_space
                 group by tablespace_name) a
         where a.tablespace_name = b.tablespace_name)
 order by EXT_PCT desc;