Re: Temporary Tablespace Problem

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3F4B8F18.67AB9CD1_at_exxesolutions.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
>
> 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.

I noticed in your script, you have 'AUTOEXTEND ON NEXT 8K' but the the tablespace is defined with unfirom 1024k extents. You may want to change that:

alter database tempfile 'temp.dbf' autoextend on next 1024k; Received on Thu Aug 28 2003 - 16:29:10 CDT