我们可以通过DBA_TEMP_FILES和V$SORT_USAGE获得临时段相关的信息,需要注意的是V$SORT_USAGE使用块为单位,所以需要对其进行转换,下面提供查看普通表空间和临时表空间的sql脚本给大家参考
/*表空间使用情况*/
SELECT
FILES.TABLESPACE_NAME,
FILES.TOTAL_MB,
FREE.FREE_TOTAL_MB,
FREE.FREE_MAX_BLOCK_MB,
(FILES.TOTAL_MB - FREE.FREE_TOTAL_MB) USED_TOTAL_MB,
ROUND((FILES.TOTAL_MB - FREE.FREE_TOTAL_MB)/FILES.TOTAL_MB*100, 3) USED_TOTAL_PERCENT
FROM
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_TOTAL_MB, MAX(BYTES)/1024/1024 FREE_MAX_BLOCK_MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_MB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) FILES
WHERE FREE.TABLESPACE_NAME(+) = FILES.TABLESPACE_NAME
ORDER BY USED_TOTAL_PERCENT DESC
;
/*查看临时表空间总体使用情况*/
SELECT TMP_TBS.TABLESPACE_NAME,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') PARA
GROUP BY TMP_USED.TABLESPACE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;
/*查看临时表空间中排序段和数据段的使用情况*/
SELECT TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE TEMP_SEG_TYPE,
SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
SUM(USED_TOT.USED_MB) USED_MB,
SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TMP_TBS,
(SELECT TMP_USED.TABLESPACE, TMP_USED.SEGTYPE,
SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
FROM V$SORT_USAGE TMP_USED,
(SELECT VALUE DB_BLOCK_SIZE
FROM V$PARAMETER
WHERE NAME = 'db_block_size') PARA
GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;