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

1 Comment »

  1. Exactly. We can trace sql statement from DBA_HIST_SQLSTAT for memory crunch.

    Comment by Jack Nicholson — September 23, 2010 @ 9:04 pm | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: