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

Saturday, September 13, 2008

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.


Share/Bookmark

2 comments:

  • Dejan Topalovic

    I ja sam muku mucio sa prosirivanjem TEMP tejblspejsa, ali se ni sam ne sjecam kako sam rijesio to. :)

    Jesi li koristio ikad temporary tablespace group mogucnosti? Mi smo probali i to, ali se u RAC okruzenju ta opcija zaglupi, pa ti najvise resursa ode na interni SQL upit, pomocu kojeg baza trazi temporary tablespace, koji je slobodan za koristenje i koji nije puno zauzet... Znaci, nekoliko miliona kratkih upita samo za to...

    Odustali smo od tih grupa, pa presli na pojedinacne temporary tablespace za svaku grupu aplikacija...

  • Marko Sutic

    Nope, nikad nisam koristio tablespace grupe jer su mi beskorisne. Ukoliko bi zatrebao više temporary prostora samo bi povećao trenutnu data fajlu ili dodao novu.

    A što se tiče proširivanja TEMP tablespace-a... ja postavim neku veličinu i onda na način koji sam naveo u postu (pomoću trace-a) hvatam upite koji pucaju.

    Ako je query smislen i s razlogom troši puno temporary prostora, onda prilagodim TEMP tablespace da bude dostatan za izvršavanje upita. Ali najčešće TEMP pukne zbog loše napisanog upita koji se u suradnji sa developerom fino optimizira da traje kraće i zauzima manje resursa.

    I sada mogu reći da jako malo imam problema sa ORA-01652 greškama :) (da kucnem u drvo)

    U principu mi je svaki GB diskovnog prostora jako bitan, tako da su mi svi TEMP tablespace-ovi točno onoliki koliki moraju biti.

  • Post a Comment