5 Replies Latest reply: Jan 22, 2013 1:32 PM by Nick B RSS

    TABLESPACES files are growing.

    986302
      HI,

      I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
      I need some helpful suggestion to reuse my server space .



      Thanks.
        • 1. Re: TABLESPACES files are growing.
          fac586
          983299 wrote:
          HI,

          I have issue of TABLESPACES on test instance. Tablespace files are TEMP1,TEMP2 and APPS_UNDOTS1. Initially TEMP1 and TEMP2 were of 4 GB but now the have grown to 32 GB each respectively. Resulting an occupied space of 64 GB on test server. I want to reuse that space on test instance as those tablespaces never crosses used space of 1%. Reason of this problem was my DBA set AUTOEXTEND as ON for tablespaces.
          I need some helpful suggestion to reuse my server space .
          This is off-topic for this forum. Post DB issues with no APEX dimension in the {forum:id=61} forum.

          (As a matter of interest, why did you post this apparently non-APEX question in the Application Express forum?)
          • 2. Re: TABLESPACES files are growing.
            Nick B
            I recently experienced the very same issue; my TEMP tablespace grew from 1 GB to 20 GB. System engineers don't like that all. A possible explanation can be found in the Oracle docs:

            +"Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete."+

            The solution is pretty straightforward:

            1. create a new temp tablespace
            2. make it the default temp tablespace in the DB
            3. kill sessions that are using the old temp tablespaces
            4. drop the old temp tablespaces
            5. recreate your temp tablespaces (with an appropriate size of course)
            6. assign a default temp tablespace
            7. kill sessions that are using the temp tablespaces you created in step 1
            8. drop the temp tablespace you created in step 1

            +"Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected."+

            I basically followed the steps described in the following article, that did the trick for me:
            http://dbafix.blogspot.be/2010/08/how-to-drop-and-recreate-temp.html

            Other resources:
            http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces007.htm#CFHJFBBJ
            http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php


            Good luck,
            Nick
            • 3. Re: TABLESPACES files are growing.
              usman1997
              i am new user and registered today on this forum..
              • 4. Re: TABLESPACES files are growing.
                usman1997
                what type of large processes were performed on your database? can you give some explanation? on my side some processes were performed regarding Create Accounting (on oracle EBS) from functional team.

                Regards
                • 5. Re: TABLESPACES files are growing.
                  Nick B
                  I think it was caused by a DBMS_JOB that ran. Nothing special happens during the execution of this job; just a query that runs for a couple of seconds and generates several .csv files based on the query result. That's it. I have no idea what caused the exploding TEMP tablespace. It happened just once in five months now. I don't think you can compare my case with yours. APEX is pretty lightweight compared to EBS... ;]