Oracle Logbook

November 6, 2009

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

Filed under: Uncategorized — heliodias @ 8:55 pm

Sometimes you have a huge space on temporary tablespace and even so some bad query use it all.
First step is investigate the normal usage of sort area, and if you see an abnormal behavior, then it´s time to find out , what´s this query.

SQL> SELECT MAX(SORTS_TOTAL/EXECUTIONS_TOTAL) FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0; 

MAX(SORTS_TOTAL/EXECUTIONS_TOTAL)
———————————
                           102173

The above query will show the top temporary resource consumer. Then all you have to do is

SQL> SELECT SQL_ID FROM DBA_HIST_SQLSTAT WHERE EXECUTIONS_TOTAL>0 AND SORTS_TOTAL/EXECUTIONS_TOTAL>=102173;

SQL_ID
————-
4w84ym20xy7da

And finally

SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID=’4w84ym20xy7da’;

Advertisements

Blog at WordPress.com.