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;