Reducing size of temp tablespace

We are experiencing a temp tablespace problem. The temp tablespace sizing is normally big enough (4 GB) to handle day-to-day operation. But due to the usage of LOB data in our application, the lifetime of the temp tablespace usage becomes a problem. Is there any way we can reduce the size of the temp tablepace either by optimizing SQL queires or by setting some parameters in the database? Moreover I would like to know the lifetime of the temp tablespace. Is it call based or session based?

    Requires Free Membership to View

    Login

What kind of files are you using for your temp tablespace? If you are using TEMPFILES, then there is a bug where Oracle does not release allocated temp space to be reused after a session is done with it. The temp space should only be allocated only for as long as it is needed. The workaround for this bug is to recreate your temp tablespace using DATAFILES, not TEMPFILES.

Otherwise, the *_AREA_SIZE parameters (where *=SORT or HASH, etc) control when a session needs to stop using memory for the operation and start using the temp tablespace. You can try increasing these parameters, but keep in mind that you will be requiring more memory for your session.

This was first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top