Temporary Tablespace Problem

Hello all,

For the application I now support, over the past month there have been three separate occasions where we've been getting "Unable to extend TEMP tablespace" errors, which causes the Oracle server to shoot up to 100% CPU utilization and necessitates a shutdown/restart.

We've been adding more space to the TEMP tablespace, but this error continues. Everytime we restart, the TEMP tablespace (through enterprise manager) reports 499 Megabytes used (which is almost the size of one of our datafiles).
(Note: We haven't been able to identify any large offenders in terms of reports or large queries. )

When I took a look at the DDL for the TEMP tablespace, I found this which was curious:

CREATE
    TEMPORARY TABLESPACE "TEMP" TEMPFILE
'D:\ORACLE\ORADATA\R3PROD\TEMP03.DBF' SIZE 500M REUSE,
'D:\ORACLE\ORADATA\R3PROD\TEMP04.DBF' SIZE 1000M REUSE,
'D:\ORACLE\ORADATA\R3PROD\TEMP01.DBF' SIZE 500M REUSE
    AUTOEXTEND
    ON NEXT 8K MAXSIZE 500M,
'D:\ORACLE\ORADATA\R3PROD\TEMP02.DBF' SIZE 1000M REUSE EXTENT
    MANAGEMENT LOCAL UNIFORM SIZE 1024K; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP" I'm a bit confused as to what the above signifies. When the database needs more than 500 MB of TEMP space, where would it go? My guess is that Oracle is getting confused and not either using TEMP03/04, or using TEMP02 and autoextending it. (Related stupid question: for AUTOEXTEND, does Oracle attempt to create TEMP02.DBF or does it rely on an existing DBF?)

Thanks for any insight/help for this relative DBA newbie.

-M Received on Tue Aug 26 2003 - 09:40:11 CDT