ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2

**********************************************************************

Following errors written to the Alert log file. Please verify

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2

**********************************************************************

Solution

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.

This basically means that free space from other instances is being requested, and typically signifies that there is instance contention.  Instance contention within the temporary space can make the instance take more time to process.

In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:

1. Increase size of the temp tablespace
2. Increase sort_area_size and/or pga_aggregate_target

Identify temporary datafile details :

SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

FILE_NAME                                     TABLESPACE_NAME

/apps/db/apps_st/data/temp01.dbf    TEMP1
/apps/db/apps_st/data/temp02.dbf         TEMP2

SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

TEMP2 524160 3456 520704
TEMP 0 0 0
TEMP1 524160 512 523648

Add additional temp file in temporary table-space

SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/u01/app/oracle/oradata/ iamdb/temp03.dbf’ size 1024m;

Check if there is any space available in temporary table-space (segment)

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

TEMP1             4096                   6.625           4089.375
TEMP2             4096                     43             4053

Monitoring Temporary Tablespaces and Sorting

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from $TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED BYTES_FREE

TEMP1                          4294967296            0
TEMP2                          4294967296            0

Root Cause Analysis

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;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS




Identify temp segment usages per statement

 Temp segment usage per statement.

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

SID_SERIAL USERNAME HASH_VALUE SQL_TEXT MB_USED TABLESPACE



SQL > select * from v$sort_segment;

TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO

TEMP2 0 0 128 38 4095 524160 38 4864 4057 519296 0 4980625 0 0 4095 524160 4095 524160 4059 519552 0
TEMP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1024
TEMP1 0 0 16 37 32760 524160 37 592 32723 523568 0 26657530 0 0 32760 524160 31266 500256 31191 499056 0


Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.

To find out if the actual limit is 32gb, run the following:

sql > select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.

If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
  2  TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

Create & drop TEMP TABLE-space in 11gR1

While doing this activity, existing temporary tablespace may have existing live sessions, 

due to same oracle won’t let us to drop existing temporary tablespace. Resulting,

 we need to kill existing session before dropping temporary tablespace.

Following query will give you tablespace name and datafile name along with path of that 

data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/prod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

Following query will help you to alter database for default temporary tablespace. 

( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old 

temporary tablespace ( i.e. TEMP ) and kill them.

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

Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

Example :-

SQL> alter system kill session '59,57391';

Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

Contents and datafiles are deleted successfully.

If you wish to continue with old temporary tablespace name, i.e. 

‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.