One of the nicest new features in 12c - an online rename facility for datafiles

Something that has been needed for a long while to:

a) correct mistakes

and perhaps more usefully

b) aid migrations in and out of ASM or do different filesystems

Is an online rename of datafiles, this has been needed for as long as i can remember an oracle finally got around to doing it (not sure what too so long really).

Anyway - here is how you do it. First we create a tablespace and give it a 'bad name'

SQL> create tablespace demo datafile '/tmp/wrongname.dbf' size 8M reuse;

Tablespace created.

What would have been a pain to fix is now easy with this new syntax (which is all done online).
SQL> alter database move datafile '/tmp/wrongname.dbf' to '/tmp/almostrightname_but hashiddencontrolcharacter.dbf';

Database altered.

In the example above i had a 'hidden' character in the filename  which was still wrong - we can of course use the file number instead of the name to rename it again to the correct path.

SQL> alter database move datafile 5 to '/tmp/reallygotitrightthistime.dbf';

Database altered.


This is a nice fix for when you make typo's but is much more valuable for things like moving in to ASM or migrating files to a different filesstem attached to a different storage array for example. This is now completely online - fantastic.


Post a Comment