Re: [Q] how to online drop tempfile???

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 13 Sep 2004 01:34:20 GMT
Message-ID: <wO61d.18862$ha6.2865@newssvr27.news.prodigy.com>

Howard J. Rogers wrote:

> aaa wrote:
>
>
>

>>If I use same command when database open.  It will
>>hang in there for ever.
>>
>>Can anyone tell me how to drop tempfile  when database
>>open?
>>
>>Thanks.

>
>
> You only can't drop it because it is the default temporary tablespace for
> the database. Not unnaturally, you can't get rid of something which is the
> fall-back default.
>
> Therefore the answer is simple: create a new temporary tablespace, make that
> the default, and then you can drop the original. Something like:
>
> create temporary tablespace temp2 tempfile....;
> alter database default temporary tablespace temp2;
> drop tablespace temp1;
>
> Regards
> HJR
Simpler than that... no need to alter default or drop/recreate tablespace.

Just add a new tempfile to the existing temp tablespace, then drop the old tempfile.

--Mark Bole Received on Sun Sep 12 2004 - 20:34:20 CDT