Also erik set the datafile NOT to autoextend. You expect the temporary
tablespace to fill and then reuse existing empty segments. however if
autoextend is on I believe that the file just keeps growing.
regards
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Erik Dantes" <erik.dantes_at_wmich.edu> wrote in message
news:3BBDC7CF.99902358_at_wmich.edu...
> Thank you, that worked perfectly...and without hassle.
>
> Erik
>
> Paul Harrington wrote:
>
> > 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.
> > =====================================================================
>
Received on Fri Oct 05 2001 - 10:06:04 CDT
