This discussion is archived
5 Replies Latest reply: Jan 22, 2013 11:32 AM by Nick B RSS

TABLESPACES files are growing.

986302 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    i am new user and registered today on this forum..
  • 4. Re: TABLESPACES files are growing.
    usman1997 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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... ;]

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points