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

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 29 Aug 2004 07:44:22 +1000
Message-ID: <4130fcd1$0$24219$afc38c87@news.optusnet.com.au>

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 Received on Sat Aug 28 2004 - 16:44:22 CDT