ORA-01652: unable to extend temp error-Viewing SID and SERIAL# That Is Consuming Temporary Space   Leave a comment

When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary
tablespace is too small. However, Oracle may throw this error if it runs out of space because of a one-time event, such
as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts
of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query:

set linesize 1000
set pagesize 1000
col USERNAME format a20
col SPID format a10
col MODULE format a30
col PROGRAM format a30
SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
FROM v$sort_usage su
,v$session s
,dba_tablespaces tbsp
,v$process p
WHERE su.session_addr = s.saddr
AND su.tablespace = tbsp.tablespace_name
AND s.paddr = p.addr
s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;

Sample Output
You will find the temp consumption accordingly in MB_USED column. if any process is consuming more temp and if others are facing this error frequently then increase TEMP tablespace.
If you determine that you need to add space, you can either resize an existing data file or add a new one. To resize a temporary tablespace data file, use the ALTER DATABASE TEMPFILE...RESIZE statement. The following command resizes a temporary data file to 12GB:
SQL> alter database tempfile '/u01/dbfile/o12c/temp02.dbf' resize 12g;
You can add a data file to a temporary tablespace, as follows:
SQL> alter tablespace temp add tempfile '/u02/dbfile/o12c/temp04.dbf' size 2g;

Posted November 11, 2013 by balaoracledba.com in Oracle DBA, Oracle10g/11g

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: