Re: Deleting a file from a temporary tablespace

Thanks Lothar! That solved my problem.

--shirish
(http://www.shaydes.com)

Lothar Armbruester <lothar.armbruester_at_t-online.de> wrote in message news:<PM0003B0717F87DC81_at_hades.none.local>...
> Shirish Nilekar wrote:
> > Hi,
> >
> > I recently added a file, 'temp02.dbf' to TEMP tablespace on a 9i DB.
> >
> > I created 'temp02.dbf' with an initial size of 100M with increments of
> > 1M.
> > It got created in $ORACLE_HOME/dbs directory. Due to some huge queries
> > involving
> > CLOB fields this file has ballooned to 869M!! I would like to delete
> > this file,
> > and recreate (a smaller one, if possible) in another location. This
> > seems possible
> > for datafiles, but how do I do it for temp files?
> > Thanks.
>
> Hello Shirish,
> try this:
>
> alter database tempfile 'temp02.dbf' drop including datafiles;
> alter tablespace ts_xxx add tempfile 'temp02.dbf' size 100M reuse;
>
> Maybe you could do:
>
> alter database tempfile 'temp02.dbf' resize 100M;
>
> Hope that helps,
> Lothar
Received on Wed Nov 27 2002 - 18:39:22 CST