oracle 查看表空间使用情况（详细准确）

 

 首先需要建一个视图辅助查询：

create or replace view dba_tablespace_free asselect a.tablespace_name,a.total_space_mb allocated_space_mb,round(b.free_space_mb,2) free_space_mb,(a.max_space-a.total_space_mb) free_allocate_mb,round(a.max_space,2) max_space_mb,round((a.total_space_mb-b.free_space_mb)/a.total_space_mb*100,2) pct_usage,round(a.total_space_mb/a.max_space*100,2) pct_allocatedfrom (select tablespace_name,sum(bytes)/1024/1024 total_space_Mb,decode(sum(maxbytes/1024/1024),0,sum(bytes)/1024/1024,sum(case when AUTOEXTENSIBLE='YES' then maxbytes                             else bytes end)/1024/1024) max_space from dba_data_files  group by tablespace_name)a,(select tablespace_name, sum((bytes)/1024/1024) free_space_Mb from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name;

 然后这是查询语句

SELECT TOTAL.TABLESPACE_NAME AS 表空间名,
       ROUND(TOTAL.MB - FREE.MB, 2) || ' MB' AS 当前已使用的空间,
       ROUND(TOTAL.MB, 2) || ' MB' AS 当前可使用总空间,
       ROUND(FREE.MB, 2) || ' MB' AS 当前剩余可使用空间,
       ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS 当前使用百分比,
       ROUND(TOTAL.MAX_MB, 2) || ' MB' AS 可扩展到的最大空间,
       DECODE(TOTAL.MAX_MB, 0, 0, ROUND(TOTAL.MAX_MB - TOTAL.MB, 2)) ||
       ' MB' AS 剩余可扩展的空间,
       DECODE(TOTAL.MAX_MB,
              0,
              0,
              ROUND((1 - TOTAL.MB / TOTAL.MAX_MB) * 100, 2)) || '%' AS 剩余可扩展的百分比
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 AS MB
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) FREE,
       (SELECT TABLESPACE_NAME,
               SUM(BYTES) / 1024 / 1024 AS MB,
               SUM(MAXBYTES) / 1024 / 1024 AS MAX_MB
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) TOTAL
 WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
 ORDER BY TOTAL.TABLESPACE_NAME

 

因为oracle是自动扩展的，注意《剩余可扩展的空间》 这个数如果太小就需要添加表空间文件了。