Re: Temporary Tablespace Problem

Mo Shah wrote:

> 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

What version and edition?
What Windows implementation?
Have you downloaded and implemented the latest patches? Have you sized the largest SQL statement in terms of the amount of data to determine the appropriate size of the temp tablespace?

The above create tablespace statement signifies to me that the person that created the database had little experience with Oracle. I'd suggest hiring a consultant to fix the problems and train you.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)