5 Replies Latest reply on Jan 9, 2009 2:26 PM by 676589

    Temp usage history

      Hi guys,

      im running in a little problem.

      This morning one client gave me an "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP" error in oralce log file.

      I have a query that can detect the oracle temp usage and by whom but at this current moment, but for that i need to detect the problem before it happens ( in some cases i can do it, in other cases i can't like this one ).

      My question is:

      Is it possible now, that 5 hours have passed since the error i can check wich process/sid/user/query has exploded TEMP at that time ? Maybe in history but i cant find anything that helps on that.

        • 1. Re: Temp usage history
          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,
          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;
          But if the session has been disconnected than I am not sure that there will be much of info available.

          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
          1 person found this helpful
          • 2. Re: Temp usage history
            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
            • 3. Re: Temp usage history
              Nigel C.L. Thomas
              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.

              Good luck!

              Regards Nigel

              Edited by: nthomas on Jan 9, 2009 12:08 PM - corrected SASH link
              • 4. Re: Temp usage history
                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.
                • 5. Re: Temp usage history
                  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.