This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,153 Users
  • 2,269,775 Discussions


alter database move tempfile|controlfile|spfile

Andreas Huber
Andreas Huber Member Posts: 38
edited May 18, 2016 7:11AM in Database Ideas - Ideas

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


Andreas Huber

Andreas HuberBPeaslandDBAMarco MischkectriebDaniel HillingerN.B.berxGbenga Ajakayejoverman2014pattonjgUser_JDNQ2Sven W.ulohmannJoerg.SobottkaAndris Perkons-OracleMartin Preiss
16 votes

Active · Last Updated


  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    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?

    > 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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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!

    Gbenga Ajakaye