Re: Deleting a file from a temporary tablespace

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

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de