Re: Temporary Tablespace Problem

On 26 Aug 2003 07:40:11 -0700, moshah1_at_yahoo.com (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

Autoextend is turned on for temp01.dbf. However, initial size is 500M and maxsize is also 500M so that datafile will never extend. Received on Thu Aug 28 2003 - 20:16:51 CDT