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

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Mon, 13 Sep 2004 11:51:22 +1000
Message-ID: <4144fd1b$0$20581$afc38c87@news.optusnet.com.au>

Mark Bole wrote:

> 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

You might care to check your newsreader! This was written weeks ago (I think!).

As for "simpler than that", care to share the secret on how you drop a tempfile? Whereas I can knock out the three lines of syntax I mentioned without even thinking about it, I'm stuffed if I can remember the syntax for dropping tempfiles! Getting old, I guess.

I get ORA-02142: missing or invalid ALTER TABLESPACE option if I try an "alter tablespace X drop tempfile '/path/name'" or an ORA-01900: LOGFILE keyword expected if I try an "alter database drop tempfile '/path/name'".

I could of course look it up... but then it stops being 'simple', doesn't it?

Regards
HJR Received on Sun Sep 12 2004 - 20:51:22 CDT