This content has been marked as final. Show 5 replies
I guess the answer for that will be a no AFAIK. Its been a long time since the query ran and without having Auditing enabled,this kind of info is not kept anywhere. If the session is connected than something like this can help,1 person found this helpful
But if the session has been disconnected than I am not sure that there will be much of info available.
select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser,a.status from V$SESSION a,V$SORT_USAGE b where a.saddr = b.session_addr order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
Hope some one else would have a more better idea about it as well.
You didn't tell us the version. If its 10g, than AWR tables can hold the info for a longer period of time. You may want to check some DBA_HIST* table which does hold this info about temp. There is a view, DBA_HIST_SQLTEXT which does store the info aboutthe queries in it after the snapshot, check if that can help.
Edited by: Aman.... on Jan 9, 2009 5:18 PM
Well i admit that this can be complicated, is there any way to detect that TEMP is about to blow up ?
That tenp is already at 100% of usage, so this indicator is useless :(
Note: 10g Database ;)
Edited by: Helder Oliveira on 9/Jan/2009 4:04
Check also the ASH [v$active_session_history|http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1007.htm#REFRN30299] - any events that relate to the temp t/s datafiles?
If you aren't licensed for AWR, then have a look at [Kyle Hailey - SASH|http://perfvision.com/tools.php]. It won't help this time, but could help next time.
Edited by: nthomas on Jan 9, 2009 12:08 PM - corrected SASH link
You could instruct the user to use resumable session in combination with "after suspend" trigger when the ORA-1652 happens
you may inspect if the temp tablespace is just too small or what.
in this particular case the TEMP has 4GB, there are some other examples of the same set up that the TEMP has 32GB... the solution must be better querys and not more space.
In that 32GB case i have detected the problematic query, but in this one not yet... and it seems that this will be a long fight because i havent's started monotorizing all clients... just 4 of 100 tight now :)
This is just like a note, im exploring the tips you guys are giving me and i will reply back soon.