当收到类似这样的告警信息ORA-01652: unable to extend temp segment by 128 in tablespace xxxx 时,如何Troubleshooting ORA-1652这样的问题呢?当然一般xxx是临时表空间,也有可能是用户表空间。
我们先来模拟一下这个情况,在两个会话窗口执行下面SQL语句,这个视图比较特殊(因为比较懒,不想去构造一个大量消耗临时段的SQL,便使用手头的一个脚本),它里面有一个DISTINCT操作会消耗TEMP表空间中大量的临时段
SQL> select count(*) from v_ies_go_information;
开启两个会话窗口执行上面这个SQL,此时这两个会话会耗大量临时段,那么你用下面SQL语句就能捕获到这个SQL,如下所示:
For 8.1.7 to 9.2
SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT
FROM V$SESSION A, V$SORT_USAGE B, V$SQLAREA C
WHERE A.SADDR = B.SESSION_ADDR
AND C.ADDRESS= A.SQL_ADDRESS
AND C.HASH_VALUE = A.SQL_HASH_VALUE
ORDER BY B.TABLESPACE, B.BLOCKS;
For 10.1 and above:
COL USERNAME FOR A16;
COL OSUSER FOR A16;
COL TABLESPACE FOR A10;
COL SQL_TEXT FOR A160;
SELECT A.USERNAME, A.SID, A.SERIAL#, A.OSUSER, B.TABLESPACE, B.BLOCKS, C.SQL_TEXT
FROM GV$SESSION A, GV$TEMPSEG_USAGE B, GV$SQLAREA C
WHERE A.SADDR = B.SESSION_ADDR
AND C.ADDRESS= A.SQL_ADDRESS
AND C.HASH_VALUE = A.SQL_HASH_VALUE
ORDER BY B.TABLESPACE, B.BLOCKS;
当然消耗临时表空间的BLOCKS是一直变化的,下面只是其中一次查询结果的截图
![17d5b5e04058bde43a2a6d0350a7ffca.png](https://i-blog.csdnimg.cn/blog_migrate/c2a1fd5bcdff8778f8cb671bb19171e1.png)
当然这个也可以通过下面SQL查询当前消耗TEMP临时段的SQL_ID以及具体大小信息。这些信息都是实时变化的。
SQL> SELECT SQL_ID,SUM(BLOCKS) FROM GV$TEMPSEG_USAGE GROUP BY SQL_ID ORDER BY 2 DESC;
SQL_ID SUM(BLOCKS)
------------- -----------
cw4d8h5fudg6b 261888
SQL> SELECT SQL_ID,SUM(BLOCKS) FROM GV$TEMPSEG_USAGE GROUP BY SQL_ID ORDER BY 2 DESC;
SQL_ID SUM(BLOCKS)
------------- -----------
cw