Operating System - HP-UX
1748195 Members
4620 Online
108759 Solutions
New Discussion юеВ

ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

 
SOLVED
Go to solution
Ratzie
Super Advisor

ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

I just received an error from the alert log:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Now I do understanding that the TEMP tablespace with not size down until the database is restarted, so stay 99.9% is not a bad thing. But, what I do whan to know, is if I did everything to correct the problem.

First I ran:
select sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where a.sqladdr = b.address and
a.sqlhash = b.hash_value and
a.session_addr = c.saddr
order by sid

This gave me the Sid, User name, sql_text ( I did not create this query myself, but pulled from net)
Output:
222.00 USER1 WORKGROUP\LAPT TEMP 132.00 17,160.00
I do not know how the extents and blocks (132.00 & 17,160) plays into, or what it means??? But, it did return the user name and sid

I then ran:
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2)

(again from net)
This came up with 3...
SID USERNAME TABLESPACE SQL_TEXT SIZE_MB
123,10674 ACAPP TEMP -- 10.16
193,1966 USER2 TEMP SELECT 704.84
222,3729 USER1 TEMP SELECT 134.06

I contacted User2 and had him log off. This still did not clear. So I did an:

Alter system kill session '193,1966';

User1, this query ended or the user killed him self. Now I understand, it is probably because they were quering on columns that were not indexed, and the query was quite huge. BUt, what I would like to know is if I covered all my steps to fix the problem.

Q1: Also, I did not touch the ACAPP, since this is part of the application and I do not know if I would break anything. How do I check to see what this SID is actually doing? Or to find more info on it.
Q2: The SIZE_MB? Is this the amount that the query is reserving in the temp tablespace to run?

Q3: What other checks can be run to be sure I cleared this problem up?
6 REPLIES 6
Indira Aramandla
Honored Contributor
Solution

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

Hi

A sort segment is created by the first statement that uses a TEMPORARY tablespace for sorting, after startup. These are not released until the database is restarted. The view $SORT_SEGMENT can be used to see how many of the currently allocated sort segments are being used.

If a PERMANENT tablespace is used for sorting, then any temporary segments created are dropped by SMON on completion of the statement, and the space is released for use by other objects.

SMON wakes itself every 5 minutes and checks for tablespaces with default pctincrease != 0.
And the most common indicator is the SMON process consuming large amounts of CPU for a long period trying coalesc Free space in Temp tablespace.

Try looking at v$sort_segment to see the number of used_blocks
during the sort, used_blocks should go up and then it should reduce (or may get the ORA-1652) if it runs out of blocks.

Query v$sort_segment for free_blocks and then continue to query it during the statement that produced the first ORA-1652.

To find Users using temp space query;
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
a.sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2);

Attached is a document with discussions to guildlines for temporary tablespaces.

Indira A


Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

hi,

Indira has posted very important information above.
===========================================
Q1: Also, I did not touch the ACAPP, since this is part of the application and I do not know if I would break anything. How do I check to see what this SID is actually doing? Or to find more info on it.
===========================================
You can enable trace for that sessions dynamically by issueing the "dbms_system.set_sql_trace_in_session" and analysing the trace file generated.

If you want to rebuild the temp tablespace, one quick solution would be to create a new temp tablespace and modify the properties of users of existing temp tablespace. You may wish to proceed as follows:
create TEMPORARY TABLESPACE TEMP2 tempfile .....

then change the temporarry tablespace parameter for all users to TEMP2,

spool reset_user.sql
select 'alter user '||username||' temporary tablespace temp2;' from dba_users;
spool off;
@reset_user.sql

then drop the old tablespace (as soon as possible).


hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

Hi,

Trying to add some info to the one provided by Indira and Yogeeraj, I would use the following query to IDENTIFY who's the bad "guy" :).

Bad guys and there bad SQL abuse from getting data from the database: consistent gets! Here is the way to identify the abusing session (it's normally the first row returned):

select s.*,
st.value
from v$sesstat st, v$session s, v$sysstat syt
where syt.name = 'consistent gets' and
syt.statistic# = st.statistic# and
st.sid = s.sid
order by st.value desc

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Gerald Thai
Occasional Advisor

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

I resolve this issue by creating TEMP1 (locally managed) and TEMP2 (dictionary managed).

smon will clean up TEMP2 (can be manually forced by changing %increase (from 1 to 0 or vise versa))

filling TEMP1 is not a problem. It may look 99% full but actually marked as free.
renarios
Trusted Contributor

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

Hi Gerald,

If you have the chance to use Locally managed tablespaces, don't bother anymore using dictionary managed tablespaces (only system has to be a dmt). Go to http://otn.oracle.com or sign up at http://metalink.oracle.com and find out what the best strategy is for your Oracle environment. Creating both locally managed and and dictionary managed (temporary) tablespaces in a databases will reduce your administration abilities.
Try to use Oracle enterprise manager to manage your databases. It's free , powerfull and has a handy dandy gui.

Cheers,

Renarios
Nothing is more successfull as failure
Ratzie
Super Advisor

Re: ORA-1652 Unable to extent TEMP -- Did I do I can do to correct problem?

Thanks for the help!