如何检查 TEMP TABLESPACE 的使用情况

今天 ALERT LOG 文件监控报警，发现一条常见错误，

Tue Sep 06 14:43:03 2016
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP 

从错误提示看，应该是临时表空间不够导致的，登陆系统逐步查看。

1. 查看临时表空间目前使用情况

SQL> select TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as "TABLESPACE_SIZE(MB)", ALLOCATED_SPACE/1024/1024 as "ALLOCATED_SPACE(MB)", FREE_SPACE/1024/1024 as "FREE_SPACE(MB)" from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE(MB) ALLOCATED_SPACE(MB) FREE_SPACE(MB)
------------------------------ ------------------- ------------------- --------------
TEMP                                         10233               10233           9624

看上去目前已经恢复了正常，TEMP表空间总共10GB，目前已经分配了10GB，可用空间大约9.5GB。

**这里的 ALLOCATED_SPACE 是指已经从磁盘分配的空间。

2. 下一步看看临时表空间中的 SEGMENT 的使用情况。可以大致看出空间被哪个用户那个 SESSION 用了。

SQL> select username,session_num, SEGTYPE, EXTENTS, BLOCKS, BLOCKS*8/1024 as "SIZE(MB)", TABLESPACE from v$tempseg_usage;

USERNAME                       SESSION_NUM SEGTYPE      EXTENTS     BLOCKS   SIZE(MB) TABLESPACE
------------------------------ ----------- --------- ---------- ---------- ---------- -------------------------------
USER007                               5501 LOB_DATA          32       4096         32 TEMP
USER007                              16115 LOB_DATA          86      11008         86 TEMP
USER007                              12515 LOB_DATA           8       1024          8 TEMP
USER007                              33217 LOB_DATA           9       1152          9 TEMP
USER007                              27315 LOB_DATA         171      21888        171 TEMP
USER007                                639 LOB_DATA          77       9856         77 TEMP
USER007                              25055 LOB_DATA           7        896          7 TEMP
USER007                              54443 LOB_DATA          24       3072         24 TEMP
USER007                               5427 LOB_DATA         121      15488        121 TEMP
USER007                               7681 LOB_DATA           1        128          1 TEMP
SYS                                     35 LOB_INDEX          1        128          1 TEMP
USER007                               3833 LOB_DATA           5        640          5 TEMP
USER007                              39675 LOB_DATA          38       4864         38 TEMP
USER007                               1205 LOB_DATA           1        128          1 TEMP
USER007                              50231 LOB_DATA           1        128          1 TEMP
USER007                              23251 LOB_DATA           1        128          1 TEMP
USER007                              31207 LOB_DATA           1        128          1 TEMP

USERNAME                       SESSION_NUM SEGTYPE      EXTENTS     BLOCKS   SIZE(MB) TABLESPACE
------------------------------ ----------- --------- ---------- ---------- ---------- -------------------------------
USER007                               9891 LOB_DATA          19       2432         19 TEMP
SYS                                     31 LOB_INDEX          1        128          1 TEMP
SYS                                  19053 LOB_INDEX          1        128          1 TEMP
SYS                                     53 LOB_INDEX          1        128          1 TEMP
SYS                                     47 LOB_INDEX          1        128          1 TEMP
SYS                                     47 LOB_INDEX          1        128          1 TEMP

23 rows selected.

目前为止一切正常，究竟刚才是哪个 SESSION 在执行哪个 SQL 的时候出了问题，还需要进一步研究。今天还有别的事情，先写这些吧。