ORA-1652 unable to extend table by 128 in tablespace

I have been getting the ORA-1652 errors, and I have no more disk to allocate to my TEMP tablespace:

Tue Dec 23 07:38:16 2008

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Tue Dec 23 07:51:11 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

I have waited for SMON to clean out the un-used TEMP segments. How do I remove the temp segments?

Normally, you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

1.  Identify temporary datafile details :

  

SYS> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

FILE_NAME                                           TABLESPACE_NAME

-------------------------------------------------------    ------------------------------

/oracle_backup/test/TEST/temp01.dbf     TEMP

2.  Check if there is any space available in temporary tablespace (segment)

SYS>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                              54              42              12

(in above case out of 54 MB only 12 MB is free)

3.  Temporary fix

a) Resize temporary file as 

SQL> alter database tempfile  ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> alter tablespace temp add tempfile  ‘/u01/oradata/VIS11i/temp02.dbf’ SIZE 1024M;

Root Cause Analysis

1.  Identify temp segment usages per session

Temp segment usage per session.

SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) statements

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,

P.program, TBS.block_size, T.tablespace

ORDER BY mb_used;

2. Identify temp segment usages per statement

Temp segment usage per statement

SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace

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 mb_used;

Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.

For Troubleshooting:

1

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig

from DBA_HIST_ACTIVE_SESS_HISTORY

where

sample_time > sysdate-2 and

TEMP_SPACE_ALLOCATED > (4*1024*1024*1024)

group by sql_id order by sql_id;

SQL_ID                      GIG

----------------------          --------------------

4q9b2jga61n6s              4.94921875

ay1rf7kr04nkd               4.96972656

b6xhw95dpzvvs             4.89355469

This gives the sql_id and maximum allocated temp space of any queries that ran in the past two days and exceeded 4 gigabytes of temp space.

2

Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME        TABLESPACE_SIZE   ALLOCATED_SPACE   FREE_SPACE

------------------------------      --------------------------  ----------------------------    -----------------

TEMP                                        56623104               56623104        55574528

DBA_TEMP_FREE_SPACE shows bytes which are free and also which are allocated but are available for reuse

SQL> select tablespace_name, bytes_used, bytes_free from v$temp_space_header group by tablespace_name;

TABLESPACE_NAME      BYTES_USED  BYTES_FREE

------------------------------    ------------------  ------------------

TEMP                              56623104                  0

V$TEMP_SPACE_HEADER shows total free bytes (allocated but available for reuse are not shown here)

3

For Oracle 8 and above, the following query will return all users and their SIDs which are doing a sort:

SELECT  b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status

FROM  v$session a,v$sort_usage b WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

You will receive the following:

                                                                                                                  

                            File     Block                                                                                   

Tablespace Name    ID        ID      Blocks     SID    SERIAL# USERNAME     OSUSER    STATUS 

-----------------------  ------  ---------      ----------  -------  ---------- ------------------   -----------------  -----------

TEMP                     4        22        289      15       1966       SCOTT       usupport     ACTIVE

4

Here are various scripts which helps in determining who's using the TEMP tablespace.

a)

SQL>select a.inst_id,b.Total_MB, b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,       

round(used_blocks*8/1024) Current_Used_MB, round(max_used_blocks*8/1024)            

Max_used_MB from gv$sort_segment a,  (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

b)
SQL> 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;

c)
SQL> select s.sid, s.osuser, s. process, s.sql_id, tmp.segtype, ((tmp.blocks*8)/1024)MB, tmp.tablespace

from gv$tempseg_usage tmp, gv$session s

where tmp.session_num=s.serial# and segtype in ('HASH','SORT')

order by blocks desc;

d)
SQL> select sql_id,sum(blocks) from gv$tempseg_usage group by sql_id order by 2 desc;