 temp space blowing [message #159774] |
Tue, 21 February 2006 08:16 |
car
Messages: 6
Registered: February 2004
|
Junior Member |
|
|
Ora 9i
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
My temp space is continually running out of space with above error. Small enough DB, about 3GB, average datafile is 300mb. temp space is up to 1.6gb and is still blowing. Ive reset but it just keeps growing until it blows. Ive got another 14 DBs of the same type of app on the box but this is the only one that has the temp issue. Ive blown, created new temp_ts and alter db default temp space new_ts 3 times now. gernrealyl takes about 7-10 days for it to go.
Ive tried to see what was using the temp space at the time by running what saeems to be a standard temp usage script on the web..
select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
but it keeps returning no rows.
I can get over this issue pretty quickly when it happens but how can I see whats causing it to blow, the other (similar usage/size/same app) DBs that dont have this problem all have their temp_ts set to 300mb and dont ahve a prob but as I said this one kept growing (error keeps occuring no matter what I set the size to) til its 1.6gb which is too big. somethings up , I just dont know what.
oh yeah..sort_area_size is set to 262144 which is the same as the other DBs.
cheers.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
