Oracle 数据库临时表空间不足BUG

 

2019/12/01 05:01 我们维护的一套数据库包月续订部分过程执行异常，报 ORA-1652: unable to extend temp segment by 8 in tablespace                 XXX_TEMP 错误，经与 Oracle 原厂一起分析，初步确定为命中BUG： Bug 17836837 - ORA-1652 in TEMPORARY tablespace even though there is space (Doc ID 17836837.8) ， 需要升级补丁解决。

 

Oracle 版本： 11.2.0.4

 

【分析小结】

 

1， XXX_TEMP 临时表空间总计 64 G左右。

2，2019/12/01 05:01 时，临时表空间使用量不到 1G ，剩余可用空间较多还有 63G， 与该 BUG 描述的现象匹配。

 

INSTANCE_NUMBER SAMPLE_ID SAMPLE_TIME SESSION_COUNT TEMP_SPACE_ALLOCATED 
1 180803451 2019-12-01 05:01:27.179 7 447152128 
2 180599789 2019-12-01 05:01:23.197 10 100794368 
3 180703585 2019-12-01 05:01:27.949 6 118161408 

 

3，该BUG没有明确触发条件，发生具有偶发性，非必现。

 

【BUG对应的官方文档：】

Bug 17836837  ORA-1652 in TEMPORARY tablespace even though there is space

 This note gives a brief overview of bug 17836837. 

 The content was last updated on: 03-OCT-2017

 Click here for details of each of the sections below.

Affects:

 

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	(Not specified)
Versions confirmed as being affected	12.1.0.1 (Base Release) 11.2.0.4 11.2.0.3
Platforms affected	Generic (all / most platforms affected)

 

Fixed:

 

The fix for 17836837 is first included in

12.2.0.1 (Base Release) 12.1.0.2 (Server Patch Set)

 

Interim patches may be available for earlier versions - click here to check.

Symptoms:	Related To:
Error May Occur ORA-1652	(None Specified)

Description

A query using a large amount of temp space can fail with ORA-1652 

even when there is sufficient free space in the temp tablespace.

  

Workaround

  None

 

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support. 

References

Bug:17836837 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article