TEMP Tablespace filling up
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: TEMP Tablespace filling up

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Unhappy

    I have a problem with my TEMP tablespace which is constantly filling up. This can happen as early as 30minutes after starting the database.
    TEMP is the default temporary tablespace for all users and is 1.5gb in size.
    I have checked that no users have been allocated TEMP as their default tablespace acidentally.
    I am pretty sure the problem is being caused by certain reports which are run by some users. Some of the reports need to access some very large tables (up to 12 million rows in one case).
    My questions are these.
    1. Could the problem be badly written sql used by these reports (full table scans etc.)?
    2. When these reports finish, the space in TEMP is not released. The only way that I can clear it is by shutting down and re-starting the database, and then taking about 20calls complaining that the system is down!. Is there any way to force the release of space on completion of said reports?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    err temporary segments are not freed because after the sorting operation they can be overwritten, it´s Oracle´s normal behavior. You dont need to be worried

    FULL TABLE SCAN doesnt cause sorts so your temporary tablespace growth is not caused by that, SQL that cause sorting are group by, distinct, order by (and 4 or 5 more but I cant remembah but look for these 3)

  3. #3
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    in fact there is a strange behaviour of Oracle which does not release temp segments when needed and instead of that tells you that you cannot extend TEMP etc ... while it should just free space :/
    well nevertheless, my post is to give you a workaround to free TEMP without shutting down your instance, just follow these 3 steps :

    alter tablespace TEMP default storage (pctincrease 1);
    alter tablespace TEMP default storage (pctincrease 0);
    alter tablespace TEMP coalesce;

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    In a DW system, multiple temporary TSs on multiple disks under multiple disk controllers help in performance.
    If you have free disk space, create one more temporary tablespace with pctincrease 0, initial and next of same size ( = multiples of sort_area_size) and assign this TS to specific users who query from a large table. To improve performance you can place the data file for the TEMP TS in a non-mirrored disk.

    Or you can try locally managed temp tablespace with uniform extent of size 50M.

    The last option is: Look into the SQL statement whether it uses proper indexes or not.

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