1.登录DBA用户,先统计表空间
SELECT T.TABLESPACE_NAME,
TO_CHAR((T.TOTAL_SPACE - nvl(F.FREE_SPACE,0)), '9999,999') "USED(M)",
TO_CHAR(nvl(F.FREE_SPACE,0), '9999,999') "FREE(M)",
TO_CHAR(T.TOTAL_SPACE, '9999,999') "TOTAL(M)",
TO_CHAR(T.AUTO_SPACE, '9999,999') "AUTO(M)",
TO_CHAR((ROUND((nvl(F.FREE_SPACE,0) / T.TOTAL_SPACE) * 100)), '999999') || ' %' PER_FREE,
TO_CHAR((ROUND((T.AUTO_SPACE / T.TOTAL_SPACE) * 100)), '999999') || ' %' PER_AUTO
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BLOCKS * (SELECT VALUE / 1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size') / 1024)) FREE_SPACE
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE,
ROUND(SUM(DECODE(SIGN(BYTES - MAXBYTES),
1,
0,
MAXBYTES - BYTES)) / 1024 / 1024) AUTO_SPACE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T
WHERE F.TABLESPACE_NAME(+)= T.TABLESPACE_NAME
AND (ROUND((nvl(F.FREE_SPACE,0) / T.TOTAL_SPACE) * 100)) < 40
AND (ROUND((T.AUTO_SPACE / T.TOTAL_SPACE) * 100)) < 40
ORDER BY TABLESPACE_NAME;
2.抓取datafile路径名: