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

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 13 Sep 2004 02:43:36 GMT
Message-ID: <sP71d.18890$1L6.18126@newssvr27.news.prodigy.com>

Howard J. Rogers wrote:

> 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
>
>

Sorry for the delay. vacation-induced. I'll try not to make that mistake again! ;-)

alter database tempfile '/u01/oradata/xxx/temp03.dbf' drop; alter tablespace temp add tempfile '/u01/oradata/zzz/temp08.dbf';

No accounting for syntax consistency .... "drop this_thing", "this_thing drop", "add this_thing" (at least, no "this_thing add")

--Mark Bole Received on Sun Sep 12 2004 - 21:43:36 CDT