1 Reply Latest reply: Apr 8, 2013 1:12 AM by Billy~Verreynne RSS

    Temp used processes/session from past history.

    IndianDBA
      Hi

      Database Oracle 10.2.0.5.0
      OS: Sun Solaris 5.10

      It is observerd that Temp usage becomes high since couple of day's, is there a way to identify the processeses/sql_id/sid of those processes from the past date for a particular interval.

      Is there a query ?

      i can able to extract amount of usage with below commond but at the same time i want to know what were those process which used the temp ts.


      SELECT BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,B.NAME,
      ROUND((TABLESPACE_SIZE*8*1024)/1024/1024,2) SIZE_MB,
      ROUND((TABLESPACE_MAXSIZE*8*1024)/1024/1024,2) MAXSIZE_MB,
      ROUND((TABLESPACE_USEDSIZE*8*1024)/1024/1024,2) USEDSIZE_MB,
      ROUND(((TABLESPACE_USEDSIZE*8*1024)/1024/1024)/((TABLESPACE_MAXSIZE*8*1024)/1024/1024)*100,2) "Used_Percentage"
      FROM DBA_HIST_TBSPC_SPACE_USAGE A
      JOIN V$TABLESPACE B ON (A.TABLESPACE_ID = B.TS#)
      JOIN DBA_HIST_SNAPSHOT C ON (A.SNAP_ID = C.SNAP_ID)
      WHERE NAME = 'TEMP' -- Tablespacename to check the gradual growth of particular tablespace
      AND BEGIN_INTERVAL_TIME > SYSDATE - 20
      ORDER BY 1 DESC
      /

      Edited by: IndianDBA on Mar 28, 2013 5:23 AM