oracle11g下清理临时表空间

时间:2021-10-31 03:39:13

        维护的一个生产数据库,被“集中监控”软件监控到临时表空间使用率100%,需要清理。于是,想到了添加临时文件的方法。但是想到原临时文件temp01.dbf已经用掉了2GB,想一并清理这个文件,用到了shrink特性,释放了空间。

1:查看用户的默认临时表空间

SQL> select * from v$version where rownum<=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select temporary_tablespace from dba_users where username='VLSI';

TEMPORARY_TABLESPACE
------------------------------------------------------------
TEMP

2:查看表空间的利用率

SELECT D.TABLESPACE_NAME,  
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) ||

'%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)

FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL

--如果有临时表空间
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;
结果为:

oracle11g下清理临时表空间

3:temp表空间已经100%,需要释放。

SQL> col file_name for a50
SQL> col autoextensible for a10
SQL> select file_name,autoextensible
2 from dba_temp_files
3 where tablespace_name='TEMP';

FILE_NAME AUTOEXTENS
-------------------------------------------------- ----------
D:\APP\ADMINISTRATOR\ORADATA\VLSI\TEMP01.DBF YES

SQL> alter tablespace temp shrink space;

表空间已更改。

4:shrink后,再次查看temp表空间的使用率

oracle11g下清理临时表空间

5:一下降到了1.99M,此时需要添加一个临时文件,固定大小。

SQL> alter tablespace temp add tempfile 'D:\app\Administrator\oradata\vlsi\TEMP02.DBF' size 1024 M
2 ;

表空间已更改。

6:再次查看表空间使用率

oracle11g下清理临时表空间


从结果可以看出,使用率降到了0.19%。问题解决。


总结:在oracle 11g中,当临时表空间使用率过高时,可以通过alter tablespace temp shrink space来降低使用率。

            通过添加文件,也可以达到降低空间使用率的目的。