Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 4 December 2012

ORA-01652: unable to extend temp segment by string in tablespace string

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Description: When a sort operation is too large to fit in memory, Oracle allocates space in a temporary tablespace for intermediate data to be written to disk. Temporary space is a resource shared by multiple sessions on the database, and quotas cannot be set to limit how much temporary space can be used by an individual user or session. If a sort operation runs out of space, the statement initiating the sort will fail with error: ORA-01652: unable to extend temp segment by 128 in tablespace temp. It may only take one query missing part of its WHERE clause to fill an entire temporary tablespace and cause many users to encounter failure because the temporary tablespace is full.
It is easy to detect when failures have occurred in the database due to a lack of temporary space. With the setting of a simple diagnostic event, it is also easy to see the exact text of each statement that fails for this reason. For more detail click on the link: "Monitor SQL Statement that fail due to lack of  temporary space
Action: Wait for the SMON to clean it up the unused temp segments or try to restart the instance or manually coalesce the tablespace or use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
From the following solution of above issue, we can select any one as per the oracle database version.
Solution 1: You can check for held TEMP segments with this query:
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
order by b.tablespace, b.segfile#, b.segblk#, b.blocks;
Use the below command in oracle version 11g to remove a TEMP segment:
SQL>alter tablespace xxxxx coalesce;
SQL>alter tablespace TEMP coalesce;
--Query to display Coalesce information
SELECT tablespace_name,  bytes_coalesced, extents_coalesced,
 percent_extents_coalesced, blocks_coalesced, percent_blocks_coalesced
FROM  sys.dba_free_space_coalesced
ORDER BY  tablespace_name;
Solution 2: remove temporary space from a tablespace
Alternatively you can use drop segment event to remove temporary space from tablespace:
ALTER SESSION SET EVENTS 'immediate trace name drop_segments level &x';
where: x is the value for file#  for ts# =1 from Tablespace.
Solution 3: Add the temp file for Temporary tablespace
SQL>SELECT FILE_NAME||' '||TABLESPACE_NAME||' '||BYTES/1024/1024
 FROM DBA_TEMP_FILES;
FILE_NAME||''||TABLESPACE_NAME||''||BYTES/1024/1024
D:\ORACLE\ORADATA\RMAN\TEMP01.DBF TEMP 40
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\TEMP01.DBF’ SIZE 80M;
Tablespace altered.
SQL> SELECT TABLESPACE_SIZE/1024/1024||' '||ALLOCATED_SPACE/1024/1024||' ‎‎'||FREE_SPACE/1024/1024 FROM DBA_TEMP_FREE_SPACE;
SQL> SELECT tablespace_name, total_blocks, used_blocks, free_blocks FROM v$sort_segment;
SQL> SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;
Now, resize or add the datafile related to a Tablespace if require:
SQL> ALTER TABLESPACE rman ADD DATAFILE ‎‎‘D:\ORACLE\ORADATA\RMAN\RTBS02.DBF' SIZE 200M;
SQL> SELECT *FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%RT%';
We can resize the existing Datafile if the mount point have enough space
ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE ‎‎200M;
Solution 4: Create a new Temporary Tablespace and make this temporary tablespace as default temporary tablespace for database.
SQL> CREATE TEMPORARY TABLESPACE TEMP02
TEMPFILE ' D:\ORACLE\ORADATA\RMAN\TEMP02.DBF'
SIZE 256M REUSE AUTOEXTEND ON NEXT ‎‎256M MAXSIZE 8192M
EXTENT MANAGEMENT LOCAL;
Tablespace created.
Now make this temporary tablespace default at Database level:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;
Database altered.
Bounce the database so that actual temporary space is release from 'temp' Tablespace and Drop tablespace 'temp' including content.
SQL> shutdown immediate;
SQL> startup;
SQL> Drop tablespace temp including contents;
Tablespace dropped.
SQL> select *from dba_temp_files;
Now, we can see the new Temporary tablespace is 'TEMP02' with enough free space. No matter you can keep TEMP02 as default tablespace or you can created again TEMP tablespace and make it as a default tableapace.

3 comments: