8 Replies Latest reply on Oct 16, 2017 7:39 AM by Constantin C-Oracle

    temp01.dbf size increased drammatically

    goodluck247

      Hello all,

       

      We have 10.2.0.1.0 installed, and recently we encountered the following problem:

      the file /temp01.dbf has grown much larger in size, close to 6GB, within an hour, it also (at least we think so) caused our server to crash.

      Normally, the size of this file was 600-700 MB. We believe it slows down the server performance.

      What should we do about this? Can we delete this temp file, and create another one? Is it safe to manipulate this file?

       

      Thank you.

        • 1. Re: temp01.dbf size increased drammatically
          John Thorton

          goodluck247 wrote:

           

          Hello all,

           

          We have 10.2.0.1.0 installed, and recently we encountered the following problem:

          the file /temp01.dbf has grown much larger in size, close to 6GB, within an hour, it also (at least we think so) caused our server to crash.

          Normally, the size of this file was 600-700 MB. We believe it slows down the server performance.

          What should we do about this? Can we delete this temp file, and create another one? Is it safe to manipulate this file?

           

          Thank you.

          please post COPY & PASTE evidence that supports the assertions above.

           

          Files will only grow (expand their size) when AUTOEXTEND has been enabled.

          If you want file to NOT increase their size then ensure AUTOEXTEND is disabled.

           

          What is OS name & version?

          • 2. Re: temp01.dbf size increased drammatically
            Cyrille MODIANO

            The temp file is used to sort and group data, if the size increase rapidly it means that somebody ran a huge query including either a sort or a group by clause.
            Don't drop the temp file it will not resolve your problem, what you can do is limit the maximum size of this file to avoid filling all your filesystem space in case somebody makes a mistake by querying a very large amount of data and sort it or group it.

            1 person found this helpful
            • 3. Re: temp01.dbf size increased drammatically
              Mustafa KALAYCI

              huh! dramatically increasing is 6GB ? that should be small db. first of all, as already said, temp is used for temporary data, order by operations, joins, global temporary tables etc. so one of those should be happened. for example someone run a query with very huge data (too much rows) and an order by clause. you can delete temp file if this is an emergency oracle will create a new one, if not, you can create a new one. of course whoever uses temp at that moment will get an error. you should find who used that much temp space.

              1 person found this helpful
              • 4. Re: temp01.dbf size increased drammatically
                BPeaslandDBA

                As others have said, some user executed a SQL statement that needed temporary working space to complete. Since there was not enough memory in the session, the SQL statement needed to be executed in pieces, with other pieces stored temporarily on disk.

                 

                If this is a true tempfile, you could stop the instance, remove the datafile, then start the instance. On startup, the file would be recreated. But it might just be created at the same larger size, I can't remember.

                 

                Another option is to create a new temp tablespace with a smaller file. Then point everyone to this new temp tablespace. Then drop the old tablespace and its file.

                 

                All that being said, if you do not get down to the root cause, you may see this file grow again. You need to address why some session needed more temp space than is "normal" for your operations.

                 

                And as others have said, 6GB is not that much. I have temp tablespaces that are 100GB in size. So you may want to consider just leaving it alone, especially if this size file will be needed at a later date.

                 

                Cheers,
                Brian

                1 person found this helpful
                • 5. Re: temp01.dbf size increased drammatically
                  Oratig-Oracle

                  you will have to check why tabepsace is increased.

                  it should be by usage, there is no other way.

                  So, if you have the  undersized temp, you may get an error.

                  you can create the new temporary tablespace and then assign as default temporary tablespace (you should keep without AUTOEXTENT on).

                  you can drop the existing temporary tablespace.

                   

                  Now you may notice, your application/query  fails with an error.

                  1 person found this helpful
                  • 6. Re: temp01.dbf size increased drammatically
                    Jonathan Lewis

                    Further to the comments by BPeasland and Oratig-oracle:

                     

                    You can create (or alter) your temp file with "autoextend off"; or "autoextend on next 10M maxsize 1G"   (for example - pick a few MB for rate of growth and whatever you fancy as the limit).

                     

                    Once you've done this you can enable an event to dump a trace file if the file grows and hits its limit;

                     

                    alter system set events '1652 trace name errorstack lifetime 10';

                     

                    ORA-01652  is the "out of space in TEMP" error, and this command means that the next 10 times that error occurs it will dump a trace file reporting the error stack. Very near the top of the trace file you should see a line like: "Current SQL for this session ...." this will tell you what happened to cause the space allocation.

                     

                    An alternative strategy for dealing with the problem is to set the resumable_timeout (parameter - value is seconds) which will cause the session that hits a space management error to suspend itself until the timeout has passed (at which point it raises the error) or the problem goes away.  You could then write an "AFTER SUSPEND" system trigger that (a) the current SQL text of the suspended session and/or (b) constructs an email and sends it to the DBA.

                     

                    Regards

                    Jonathan Lewis

                    1 person found this helpful
                    • 7. Re: temp01.dbf size increased drammatically
                      Mohamed Houri

                      Hello Jonathan,

                       

                      As per regards to the resumable_timeout, a couple of years ago, I have got a real life experience which I have blogged about in this article

                      https://webcache.googleusercontent.com/search?q=cache:lMloZOcwh_IJ:https://hourim.wordpress.com/2015/02/14/resumable-time-out/+&cd=1&hl=fr&ct=clnk&gl=fr

                      If the link doesn’t work properly then googling with “resumable_timeout hourim” will show the blog article at the TOP of the returned list.

                       

                      @OP

                      Should you have been in a version >= 11g you would have been probably able to find the SQL which reported this TEMP error by looking at the v$sql_monitor (if appropriately licenced of course)

                      select * from gv$sql_monitor

                      where status like '%ERROR%'

                      and error_message like '%ORA-01652%';

                       

                      Though that I am not hundred percent sure that the sql monitor has shown an error in this particular case of resumable_timeout parameter set to 9000 seconds.

                       

                      And if the sql monitor has already been flushed from memory, 12cR1 give you an option to get historical monitored executions as well. Unfortunately you still are not in these two releases.

                       

                      You can also use ASH (active_session_history) to see what SQL has consumed the maximum of TEMP by looking at the temp_space_allocated columns

                       

                      Best Regards

                      Mohamed Houri

                      1 person found this helpful
                      • 8. Re: temp01.dbf size increased drammatically
                        Constantin C-Oracle

                        Hi

                         

                        There are three recommended methods of supplying sufficient storage space to meet the needs of current sessions by increasing the size of your temporary tablespace

                        1) Set one or more of the tempfiles|datafiles for the tablespace to AUTOEXTEND with MAXSIZE set ... so that you do not exhaust all available disk volume space
                                        (discuss this with a System Administrator)

                             After a month or so of having this setting ... AUTOEXTEND can be disabled .. as it is likely that the system has found its true high watermark for temporary segment usage


                        2) Monitor the temporary segment usage via queries like

                                    SELECT sum(blocks)*<block size of the temporary tablespace>
                                    FROM v$tempseg_usage
                                    WHERE tablespace = '<name of the temporary tablespace>';

                              and resize one or more of the tempfiles|datafiles for the tablespace as the tablespace becomes close to exhausted


                        3) Add a tempfile|datafile to the temporary tablespace with the problem and monitor usage as described in #2


                        Another good idea is to monitor temporary tablespace usage over time to determine what queries are consuming the temporary space space.

                        1 person found this helpful