Re: Recover some HD, HUGE temporary file

Erik,

If you have been able to free up the excess space, you should be able to resize the file; e.g:
ALTER DATABASE DATAFILE 'filename' RESIZE 5M;

The solution you have posted below would probably work for you, but you would also have to drop and recreate the tablespace in order to get rid of the newly removed file. OFFLINE DROP doesn't remove a file from a tablespace - it leaves it there in 'Recovery' mode forever.

Hope that helps,
Paul.

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

-- 
=====================================================================
Paul Harrington - Oracle DBA/Developer, Orbiscom, Dublin.
Email:  paulh_at_io.com. Please note: I discard all BCC messages unread.
=====================================================================