DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Oracle TEMP tablespace grows to 30GB.

  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Question Oracle TEMP tablespace grows to 30GB.

    Hi Everyone,

    I would like anyone to share his experience with oracle9 with the temporary TEMP tablespace in AUTOEXTEND mode.

    I have it on a Windows 2003 Standard Edition, oracle patched to 9.2.0.8. Before I had a problem with the temporary TEMP tablespace, with not a very large query the tempfile could sometimes grow to 30GB. On a daily basis a 500MB file was enough. I ran onto an Oracle bug - problem with extending beyond a 4GB limit. This was supposed to be fixed with the patch I applied (as Oracle asked me), but my problem continues to persist.

    I set up an SQL to check that is on my TEMP - sorts at that time. Can anyone suggest, if I am doing it right:

    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') SDATE,
    opname,
    target,
    TARGET_DESC,
    SOFAR/1024 SOFAR_KB,
    TOTALWORK/1024 TOTALW_KB,
    to_char(START_TIME,'YYYYMMDD HH24:MI:SS') START_TIME,
    to_char(LAST_UPDATE_TIME,'YYYYMMDD HH24:MI:SS') LAST_UPDATE_TIME,
    ELAPSED_SECONDS,
    MESSAGE,
    USERNAME
    from V$session_longops
    /

    This leads me to a result showing I am using only about 250MB of space. maybe there is another way so see how much space I really need? The same query runs every day on the same amount of data at 10PM. When I rebuild the TEMP tablespace, everything is processed with any problems.

    Has anyone had this kind of a problem?

    Regards,
    Richard
    "Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    limit the max file size..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Jun 2006
    Location
    Wales, UK
    Posts
    62
    Bear in mind how the TEMP tablespace works - if you let it grow then it will, if you don't then it will reuse no longer needed space. With autoextend on it will continue to allocate space, but if you turn autoextend off then when it reaches full it will start re-using previously allocated space (if that makes sense!).

  4. #4
    Join Date
    Mar 2006
    Posts
    11

    Oracle TEMP tablespace grows to 30GB.

    Quote Originally Posted by cis_groupie
    Bear in mind how the TEMP tablespace works - if you let it grow then it will, if you don't then it will reuse no longer needed space. With autoextend on it will continue to allocate space, but if you turn autoextend off then when it reaches full it will start re-using previously allocated space (if that makes sense!).
    Hi,

    thanks, is it written somewhere in the documentation? I'll take a look, it's seems strange to me the space is no longer reused once released when working in autoextend mode, it would be rather silly. I am just used to traditional Oracle7/8 temp tablespaces. But this could explain why I am running into this. Also the database is closed each night during a backup. Everything works fine for a few days, the temp file does not even grow beyond 1Gb and then suddenly one day it bursts into the OS limit which is 30GB. I had a feeling this is still related to the 4GB Oracle bug.

    I will try the option without the autoextend off.

    Many thanks,
    Richard.
    "Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."

  5. #5
    Join Date
    Jun 2006
    Location
    Wales, UK
    Posts
    62
    If you are closing the Db every night then the TEMP tablespace will clear itself (although it's still a good idea to turn autoextend off).

    If the TEMP space is OK for a few days & then suddenly grows very fast I would be tempted to see what's running - it sounds like a very inefficient bit of code is suddenly staring, needing lots of temp space (maybe a cartesian join?).

  6. #6
    Join Date
    Mar 2006
    Posts
    11

    Smile

    Quote Originally Posted by cis_groupie
    If you are closing the Db every night then the TEMP tablespace will clear itself (although it's still a good idea to turn autoextend off).

    If the TEMP space is OK for a few days & then suddenly grows very fast I would be tempted to see what's running - it sounds like a very inefficient bit of code is suddenly staring, needing lots of temp space (maybe a cartesian join?).
    Well, thanks fot the hint, just before going home I checked my procedure that collects the statistics - guess what? It was working fine for 2 months and just 3 days ago it started failing....you never know.
    Just one thing to remember to check ....I'll see tomorrow if this is the reason.

    Regards,
    Richard.
    "Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."

  7. #7
    Join Date
    Sep 2001
    Posts
    200
    any update on this?
    Life is what is happening today while you were planning tomorrow.

  8. #8
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by ndisang
    any update on this?
    Hi,

    well, my system works fine now. The problem is with the application/database, for 3 or 4 days I did not have the statistics collected. Simple.... This system is very sensitive for lack of statistics.

    In fact it's the Windows Scheduler that screwed up, because instead of starting at 7PM it started at 4AM when my database was shutdown. From that point the trouble began.

    I thank you all for your attention,
    Regards,
    Richard.
    "Whenever you want to learn the basics about Oracle - start from the Oracle Concepts Guide."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width