Oracle 查看表空间使用率的脚本（定义视图）

Oracle 查看表空间使用率的脚本（定义视图）

实验效率最高的表空间使用率检查方式，完全满足生产使用；
新增文件后有一定的延时，不过理解不差那点点时间，忽略延时

-- 查看表空间使用率视图 JEFF_TBS_USE
CREATE OR REPLACE FORCE VIEW SYS.JEFF_TBS_USE
(TABLESPACE_NAME,TYPE, BIGFILE, STATUS,DATAFILES,"USED_SIZE(GB)", "Allocated_SIZE(GB)", "MAX_SIZE(GB)", "FREE_SIZE(GB)", "Allocated_USED%", "MAX_Used%")
BEQUEATH DEFINER
AS
SELECT T.NAME TABLESPACE_NAME,
       T.TYPE,
       T.BIGFILE,
       T.STATUS,
       COUNT(F.RFNO) DATAFILES,
       ROUND(SUM(F.ALLOCATED_SPACE)*T.BLOCK_SIZE/1024/1024/1024,2) AS "USED_SIZE(GB)",
       ROUND(SUM(F.FILE_SIZE)*T.BLOCK_SIZE/1024/1024/1024,2) AS "Allocated_SIZE(GB)",
       ROUND(SUM(F.FILE_MAXSIZE)*T.BLOCK_SIZE/1024/1024/1024,2) AS "MAXSIZE(GB)",
       ROUND((SUM(F.FILE_SIZE)-SUM(F.ALLOCATED_SPACE))*T.BLOCK_SIZE/1024/1024/1024,2) AS "FREE_SIZE(GB)",
       ROUND(SUM(F.ALLOCATED_SPACE)/SUM(F.FILE_SIZE)*100,2) AS "Allocated_USED%",
       ROUND(SUM(F.ALLOCATED_SPACE)/SUM(F.FILE_MAXSIZE)*100,2) AS "MAX_Used%"
FROM V$FILESPACE_USAGE F JOIN (
  SELECT T2.TS#,T1.TABLESPACE_NAME NAME,T1.BLOCK_SIZE,T1.CONTENTS TYPE,T1.STATUS,T1.BIGFILE
  FROM DBA_TABLESPACES T1,V$TABLESPACE T2
  WHERE T1.TABLESPACE_NAME = T2.NAME
  ) T
ON F.TABLESPACE_ID = T.TS#
GROUP BY T.NAME , T.TYPE,T.STATUS,T.BLOCK_SIZE,T.BIGFILE
ORDER BY SUM(F.ALLOCATED_SPACE)/SUM(F.FILE_SIZE) DESC;

-- 创建public 同义词
CREATE OR REPLACE PUBLIC SYNONYM JEFF_TBS_USE FOR SYS.JEFF_TBS_USE;

-- 查询使用
SELECT * FROM JEFF_TBS_USE;