Re: Temp tablespace Database File Overgrown (20 Gigs)

From: Walt <walt_askier_at_YerBootsyahoo.com>
Date: Tue, 26 Apr 2005 17:16:25 -0400
Message-ID: <Jcybe.1048$II.191@news.itd.umich.edu>

Walt wrote:

> On one instance, the size of the TEMP01.DBF file is enormous - over 20
> Gigs (none of the other .dbf files are more than a gig or so) This has
> maxed out the disc space - apparently it's just grown until the disk has
> become full.
>
> Now, according to my research (limited, so far) this should clean itself
> up automatically eventually, and bouncing the instance should clean it
> up right away.
>
> That is not my experience. Bouncing the instance has no effect.
>
> Any suggestions? Since this is temp data, I should be able to throw it
> away with no ill effect, right? Unfortunately, I'm not sure how to
> dump the temp data.
>
> BTW, I've verified that there are no permanent objects in the temp
> tablespace (SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'TEMP';)
>
> sort_area_size == 524288
> Oracle 9.2 on W2k3.

Many thanks to all who offered help. The solution was to create a new Temporary Tablespace, set that as the default temporary tablespace, and drop the offending tablespace with the overly large datafile.

After deleting the file, I created another Temporary tablespace with the same name as the original, set it as the default and removed the one I created in step one. That put everything back to using TEMP for the default temporary tablespace.

Here's how it goes:

SQLWKS> CREATE TEMPORARY TABLESPACE "TEMP2"

      2>   TEMPFILE  'D:\ORACLE\ORADATA\BPRD\TEMP201.DBF'
      3>   SIZE 3809M REUSE
      4>   AUTOEXTEND ON NEXT 655360
      5>   MAXSIZE 4095M
      6>   UNIFORM SIZE 16M
      7>   EXTENT MANAGEMENT LOCAL;

Statement processed.
SQLWKS> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; Statement processed.
SQLWKS> DROP TABLESPACE TEMP;
Statement processed.

/* Now, use the OS to delete the data file associated with TEMP tablespace. Note that I had to create the datafile on D:, not F: where all the other datafiles are. That's because F: is full! Also, the OS wouldn't let me delete the datafile until I bounced the instance. */

SQLWKS> CREATE TEMPORARY TABLESPACE "TEMP"

      2>   TEMPFILE  'F:\ORACLE\ORADATA\BPRD\TEMP01.DBF'
      3>   SIZE 3809M REUSE
      4>   AUTOEXTEND ON NEXT 655360
      5>   MAXSIZE 4095M
      6>   UNIFORM SIZE 16M
      7>   EXTENT MANAGEMENT LOCAL;

Statement processed.
SQLWKS> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; Statement processed.
SQLWKS> DROP TABLESPACE TEMP2;
Statement processed.

/* remove D:\ORACLE\ORADATA\BPRD\TEMP201.DBF at my leisure */

One thing that's still confusing is that I'm pretty sure that I created the TEMP tablespace with a MAXSIZE of 4095M (the create tablespace statements above were purloined from the original database creation/setup scripts). Why would the file grow to 20 gig if the max is ~ 4?

Thanks again.

-- 
//-Walt
//
//

Received on Tue Apr 26 2005 - 16:16:25 CDT