4 Replies Latest reply on Jun 16, 2014 7:05 AM by sybrand_b

    Temp data file Issue


      Environment Description
      For Loading : Unix Script and Pl Sql tables

      For Reporting : SAP BO4.1 SP3


      We just have one user for loading as well as reporting and data volumes are very heavy.Issue arises when both loading as well as reporting peaks up and takes most of Temp file , then either loading suffers or reporting goes for toss.Loads are incremental in nature and reporting is scheduled to accomodate the added values.


      We have asked DBA's to get the AWR logs when loading starts so that we can check the peak of both activities.

      Apart from this, it is decided to have one more user created and both the users would be activity specific.


      Now, will the 2users division help the temp file not filling up ? Can some body suggest best approach to be followed in this scenario,

      How will the AWR logs suggest the stats for temp file ?


      We also want to automate this process that when temp file is filled upto 85% of original , then mail would be sent to requested users by script.


      Request you to help me in resolving the temp file size issue and by providing the script too.

        • 1. Re: Temp data file Issue
          Salman Qureshi


          Have a look at this thread


          HIGH Temp tablespace usage


          Important thing to note is that when temp tablespace usage increases, new extents are allocated to the temp files and these extents are not de-allocated again (until instance/database is restarted). So your way to "alert" you when tempfile is 75% used, might not be a good idea.


          I would suggest you to create 2 separate temporary tablespaces for both of these users performing 2 different tasks and assign these temp to each of them respectively. This will help you to monitor the temp usage more efficiently.


          Now, will the 2users division help the temp file not filling up ?

          No. Temp usage is related to the work required by your sessions executing the queries and not related to the number of users. Only way to stop temp filling is to tune your query in a way that it runs efficiently with minimum required data fetching during execution.


          AWR report does not actually show any significant useful information regarding temp tablespaces because excessive temp usage is usually symptom rather than the problem.


          We also want to automate this process that when temp file is filled upto 85% of original , then mail would be sent to requested users by script.

          Use OEM to send you alerts for this. Otherwise you can use following query to see the current temp usage (This query was copied from some website - not my code)


          SELECT b.tablespace,
          ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
          a.sid||','||a.serial# SID_SERIAL,
          FROM sys.v_$session a,
          sys.v_$sort_usage b,
          sys.v_$parameter p
          WHERE p.name  = 'db_block_size'
          AND a.saddr = b.session_addr
          ORDER BY b.tablespace, b.blocks; 



          • 2. Re: Temp data file Issue
            Ola Yehia-Oracle



            I agree with what Salman said and i suggest the you also check this note


            How Can Temporary Segment Usage Be Monitored Over Time? (Doc ID 364417.1)


            Any further help is most welcome.


            Kindly mark the post as helpful/correct upon your judge to Help others to find the answer.



            Kind Regards,

            Ola Yehia

            • 3. Re: Temp data file Issue

              Thanks Salman for your thoughts.


              I believe we do not have OEM on our server side but still I would double check and come back.There is client side DBA to manage but some how this idea of having 2 diff user's initially stuck our mind. But how will oracle come to know that loading has started and simultaneously reporting is to be done.


              For Ex :

              User Load => Temp A

              User Report => Temp B

              And Temp A + Temp B would be the combined size of Temp ? Is this what you mean ?


              On the automated script, need is of plsql procedure that would be part of our database and send the required mail when temp would be 85% of its original value.

              Any pointers on this will help me sort this thing out.

              • 4. Re: Temp data file Issue

                As these alert facilities are in the dbms_alert package which is part of database control, and can be managed through OEM, please try to read documentation.

                You already have this facility (and pay for it by your license) so please do not try to reinvent the wheel.



                Sybrand Bakker

                Senior Oracle DBA