Re: Oracle 9i: temporary tablespace ora-03217

"Bernd Taucher" <violine24_at_yahoo.de> wrote in message news:bMmsb.1291$W7.1163_at_news.chello.at...
> Hi!
>
> I tried to move tempfiles to a different location. While trying to set
> the temporary tablespace offline, i got an error: ora-03217.
>
> Can anybody help me? Is it not possible to set an temporary tablespace
> offline?
>
> I could set an tempfile offline, but I couldn' t move it? Again, can
> anybody help me?
>

No version, I see.

Oh, and no error message either... strangely, I don't happen to know all 20,000+ error numbers off the top of my head, so I guess I'm supposed to do the looking-up for you?

In this case, I'm going to merely assume that you're getting the "invalid option for alter of temporary tablespace" error message, and if you're not, maybe you could jot down the actual text of an error message next time.

Anyway, the command to take a tempfile offline is, as you've probably discovered, alter database tempfile X offline.

And the idea of a temporary tablespace is that it can be dropped and re-created very quickly (because the tempfiles are sparse). Therefore, probably the easiest approach is to simply drop the entire temporary tablespace and create it somewhere else. Certainly, the 'alter database rename file X to Y approach doesn't work with tempfiles.

But if you really want to move tempfiles, you can do it like so:

alter database tempfile X offline;
<use O/S commands to move the file somewhere new> alter tablespace TEMP add tempfile </new_path/filename> resuse; alter database tempfile X drop;
alter database tempfile newX online;

But it really is much simpler just to drop the entire thing and re-create the tablespace with new tempfile locations. The thing goes offline however you do it, so I can't see that the 'move' approach is better than the 'destroy-and-recreate' one.

Regards
HJR Received on Wed Nov 12 2003 - 05:07:29 CST