博客

Oracle 临时表空间常用操作

1、查询临时表空间使用率

SELECT D.TABLESPACE_NAME,

       SPACE || 'M' "SUM_SPACE(M)",

       BLOCKS SUM_BLOCKS,

       USED_SPACE || 'M' "USED_SPACE(M)",

       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' 
"USED_RATE(%)",

       NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,

               SUM(BLOCKS) BLOCKS

          FROM DBA_TEMP_FILES

         GROUP BY TABLESPACE_NAME) D,

       (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) 
USED_SPACE,

               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) 
FREE_SPACE

          FROM V$TEMP_SPACE_HEADER

         GROUP BY TABLESPACE_NAME) F

 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

 ORDER BY 1;