Re: Recover some HD, HUGE temporary file

Erik Dantes wrote:

> Got an issue. Oracle 8.1.5 on Win NT4
>
> I need to recover a large amount of space on the hard drive. We had a
> mishap with the temporary tablespace. The datafile in the last day has
> grown over 3 Gig. I have coalesced the free extents and it is now 99%
> free. Needless to say, it has caused a space crunch.
>
> I need to desperately resize the datafile down to something more
> concise. The tablespace is a temporary type.
>
> Can I add another datafile to the TEMPORARY_DATA tablespace, then take
> the large datafile offline, and subsequently delete it? Being that it
> is a 'temporary', once I add anther file I should be able to safely
> remove it, right?
>
> Will this work?
> * ALTER TABLESPACE "TEMPORARY_DATA" ADD DATAFILE 'tmp2orcl.ora' SIZE
> 5M
>
> * ALTER DATABASE DATAFILE 'E:\ORANT81\DATABASE\TMP1ORCL.ORA' OFFLINE
>
> * Delete the file tmp1orcl.ora
>
> If not, what are my other options?
> Erik Dantes
> erik.dantes_at_wmich.edu

  Eric,

  You are right, that is the one way of doing it , but with some corrections.

  Once you created another TEMP tabpespace you have to change the users temporary tablespaces to the new one so when they need it they are not going to allocate space in the old one.

  Once you take the old TEMP tablespace offline, DROP the tablespace before deleting the datafile. Then recreate the tablespace and switch the users again to the old TEMP tablespace.

  Good Luck.

  Trifon Anguelov
   Senior Oracle DBA

  http://www.dbaclick.com Received on Wed Oct 10 2001 - 23:46:47 CDT