Run out of Temporary tablspace

      Once I encounter a problem that the used space of temporary tablespace always increases till out of space on production environment, an “ORA-1652: unable to extend temp segment” error rises at last which causes the application crashed. Later restart application server, the used space releases immediately, but the used space increases once application is online.

The application restarted at 8:00PM 2010/02/01, the application crashed after the usage reached 100%.
    Learn from various forums and documents, almost all of them say that this error might caused by sort operations(creating index, processing queries including ORDER BY or GROUP BY clause)  which is too large to fit in memory.
To indentify the sql statements caused the problem, I did the following queries:
--Temporary Segments
SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE                     MB_TOTAL                               MB_USED                                MB_FREE                               
TEMP                            4000                                   1945                                   2055                                  

--Sort Space Usage by Session
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

SID_SERIAL                                                                       USERNAME                       OSUSER                         SPID         MODULE                                           PROGRAM                                          MB_USED                                TABLESPACE                      SORT_OPS                              
134,51358                                                                         RS_USER                                                       7965                                                                                                           856                                    TEMP                            1                                     
137,50660                                                                         RS_USER                                                       7968                                                                                                           59                                     TEMP                            1                                     
167,1310                                                                          RS_USER                                                       8004                                                                                                           36                                     TEMP                            1                                     
289,21173                                                                         RS_USER                                                       8002                                                                                                           991                                    TEMP                            1                                     

--Sort Space Usage by Statement
SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
ORDER BY S.sid;


SID_SERIAL                                                                       USERNAME                       MB_USED                                TABLESPACE                      ADDRESS                          HASH_VALUE                             SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
134,51358                                                                         RS_USER                        857                                    TEMP                            000000007C04D8F8                 2445178264                             Select distinct t.* From rtd_to_do_list_item t Where t.is_valid = 1 And t.status = 'OPEN' And Exists ( Select 1 From rtd_viewable_user v Where v.TO_DO_LIST_OID = t.oid  And v.USER_ID = 'LUORY2') And t.item_type In ('524','529','526','532','530','533','520','525','523','531') And t.create_time >= to_date('02/05/2010 14:32:13','MM/DD/YYYY HH24:MI:SS') And (t.REFERANCE_DATE_TO >= '20100106063714.000' Or t.REFERANCE_DATE_TO is null) And ( t.REFERENCE_DATE_FROM <= '20100307063714.000' Or t.REFERENCE_DATE_FROM is null)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
137,50660                                                                         RS_USER                        59                                     TEMP                            000000008C7C7CB0                 303609952                              SELECT oid,ttl_no FROM ( SELECT t.oid as oid,count(*) over() as ttl_no,row_number() over(order by t.CREATE_TIME desc) as rownumber FROM  ror_interface_message_header t Where (t.type = 'ServiceRequest-DCS' Or t.type = 'ServiceRequest-IRIS') And ( upper(t.SR_NO) Like'%3040238070%' Or 0=1 ) ) WHERE rownumber between 1 and 50                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
167,1310                                                                          RS_USER                        36                                     TEMP                            000000007FBF0B58                 2033372161                             SELECT OID, SEAL_NO, DO_CONTAINER_OID_2, UPDATE_TIME, IS_TWIN_HAULAGE, VERSION_ID, IS_VALID, CREATE_TIME, CONTAINER_NO, DO_NO, CUSTOM_SEAL_NO, CARGO_DESCRIPTION, SUBSTITUTE_CONT_SIZE_TYPE_OID, CREATOR, CONTAINER_OID, WEIGHT_VALUE, WEIGHT_UNIT_OID, JO_OID, VOLUME_VALUE, VOLUME_UNIT_OID, UPDATOR, CONT_SIZE_TYPE_OID, ACTUAL_CONTAINER_SIZE_TYPE FROM ROR_JO_CONTAINER_REL WHERE (JO_OID = :1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
289,21173                                                                         RS_USER                        993                                    TEMP                            00000000889BFC08                 584013674                              SELECT t0.OID, t0.UPDATE_TIME, t0.NEED_ALERT, t0.VERSION_ID, t0.DATE_NUMBER, t0.CREATE_TIME, t0.IS_VALID, t0.CREATOR, t0.USER_ID, t0.UPDATOR FROM RTD_CUSTOMIZED_TDL t0, CM_USER t1 WHERE (((t0.IS_VALID = :1) AND (UPPER(t1.USER_ID) = :2)) AND (t1.USER_ID = t0.USER_ID))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
   Unfortunately, I analyzed all the related sql statements but I didn’t find any sql statements that might cause sort operations.
   Then I analyzed the Metric Value diagram, and found that the increment is very high during about 11:45AM to 12:AM. So I analyzed the ART data during this period, a amazing discovery was found that a functionality was called frequently, then I check the functionality related code and found that a plsql function with return type is CLOB is called. After test this function in test environment, I found the root cause:
   Any plsql functions with CLOB return type used in sql statement requires temp spaces and retains the spaces during the whole database session.
In production environment, as the connection is managed in OC4J as connection pool at application server, the database sessions will not release until the application server stops (which cause the connection pool disconnected),so the temp spaces is not released all the time until restarted application server.

Conclusion: If you found that the used temp spaces increases incessantly till out of space, check  your code whether there are functions with lob return type used during a database session which doesn’t release in timely.