alter database move tempfile|controlfile|spfile

Andreas Huber

With Oracle 12c you can move datafiles online with

SQL> alter database move datafile ...

It would be very helfpull, if you could use the same syntax on tempfiles and maybe controlfiles and spfile.

Thus the wohle database could easily be rearranged and not just datafiles.

I use this great feature a lot to reorganize some strange filesystem layouts or correct creation errors in databases that have been given to my internal customers without needing extra downtime.

Relocating the Temp-TBS with creating a new one in the correct location and dropping the old one when nobody uses it is well known to me (maybe again, if tablespace name matters a lot), but thats much more work and thinking than doing a (hypothetical) "alter database move tempfile".

Today i don't know a solution for relocation of controlfiles and SPfile - a downtime is allways needed.

In times of 24/7 databases it's sad to need downtimes for these activities.

Please implement online solutions like "alter database move tempfile|controlfile|spfile".

Regards

Andreas Huber

top.gun

Is it really faster to move these files rather than just creating from scratch?

Andreas Huber

Is it really faster to move these files rather than just creating from scratch?

> Is it really faster to move these files rather than just creating from scratch?

Thats not the question.

The question is "downtime or no downtime" für controlfile and spfile.

With tempfiles its just ease of handling - you can do everything in one way and don't have to wait until the old temp tablespace is freed.

Maybe even the problem of not freed diskspace when dropping a tablespace could be solved.

BPeaslandDBA

I'm not sure we'd ever be able to move the controlfile in this manner, not ONLINE with the db running. Remember that each checkpoint updates the controlfile. Moving it online would freeze change until the move is complete. But I'm all for the rest!