Trace "ORA-01652: unable to extend temp segment"

Important obligation for every DBA is to check alert.log regularly for ORA errors or other information.

It is not so rare to get error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP - particularly if you are limited with temporary segments because of insufficient disk space.

By default you won't receive trace file after ORA-01652 error happens so it is very difficult to find out what was the cause of the error. To avoid this error in the future you must have more information about the error so I'll show you how to get more information if error happens again.



For my testing purposes I will create small temporary tablespace and assign it to user.

SQL> create temporary tablespace temp2
2  tempfile '/oracle/oradata/temp02.dbf' size 5M
3  autoextend off
4  extent management local uniform size 1M;

Tablespace created.


SQL>alter user msutic temporary tablespace temp2;

User altered.

I have 5M temporary tablespace which is assigned as default temporary tablespace to user msutic.
I've used script of Tom Kyte to make big_table for testing purposes. My big_table has 1000000 rows and I'll just query that table with sorting output by the first column.
To sort output Oracle will first use memory available for sorting, but when exhausts amout of memory available for sorting, Oracle will write sort data to disk. If there isn't sufficient disk space for sorting - query brakes with ORA-01652 error.

SQL> select * from big_table order by 1;
select * from big_table order by 1
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

In alert.log I have output:

Sat Sep 13 11:04:15 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2

As you can see, I have information that ORA-1652 happened but there isn't any trace file to use for better investigation.


To produce trace file for specified error next time it happens I'll execute command below as SYSTEM.

SQL> alter system set events '1652 trace name errorstack level 12';

System altered.

Information from alert.log:

Sat Sep 13 11:25:34 2008
OS Pid: 19489 executed alter system set events '1652 trace name errorstack level 12'

Then I'll logon as user msutic and execute query from above again.

SQL> select * from big_table order by 1;
select * from big_table order by 1
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

Now I have trace file to investigate better.
From alert.log.

Sat Sep 13 11:26:26 2008
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Errors in file /oracle/diag/rdbms/test/test/trace/test_ora_20308.trc:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

To find out statement that caused error I'll examine trace file and search for statement.

vi /oracle/diag/rdbms/test/test/trace/test_ora_20308.trc

FROM TRACE FILE:
...
----- Error Stack Dump -----
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
----- Current SQL Statement for this session (sql_id=172pt8u245qzc) -----
select * from big_table order by 1
...

Statement that caused error was : "select * from big_table order by 1"!

To resolve problem I should resize temporary tablespace and grant more space for sorting or just stop executing this statement.


At the end of this demo I'll just execute next statements to return situation as it was before demo.

SQL> alter system set events '1652 trace name errorstack off';

System altered.

SQL> alter user msutic temporary tablespace temp;

User altered.

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped.

SQL> drop table big_table purge;

Table dropped.