My Oracle Support Banner

High temp space consumption by DBSNMP (Doc ID 1389377.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

1. The following error is reported in the alert log file:
ORA-1652: unable to extend temp segment by xxx in tablespace TEMP

2. Monitoring which SQL statements are responsible for temp space consumption will return similar results:

SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username,
2 T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
3 T.sqladdr address, Q.hash_value, Q.sql_text
4 FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
5 WHERE T.session_addr = S.saddr
6 AND T.sqladdr = Q.address (+)
7 AND T.tablespace = TBS.tablespace_name
8 ORDER BY S.sid;

SID_SERIAL USERNAME MB_USED TABLESPACE ADDRESS          HASH_VALUE
SQL_TEXT
5965,8477  DBSNMP   14330   TEMP       070000042DAA4B20 1015067330
SELECT :B3 , :B2 , :B1 FROM DUAL

 

Please note that around 14GB of temp space is consumed by the following query ran by DBSNMP:

SELECT :B3 , :B2 , :B1 FROM DUAL


3. Some SQL statements can be very slow due to lack of temp space.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.