Moving datafiles has always been a pain. There are several steps, it’s fairly easy to
make a mistake and it requires the datafile to be offline. There are also different steps depending on
whether the database is in ARCHIVELOG mode or not. In ARCHIVELOG mode, the steps are…
1)
Take the tablespace containing the datafile
offline
2)
Copy/rename the datafile at the OS layer
3)
Use ALTER TABLESPACE…RENAME DATAFILE to rename
the datafile so that the controlfile will be aware of it
4)
Backup the database for recovery purposes
(recommended)
If the database is in NOARCHIVELOG mode, you have to shutdown the DB,
put it in the MOUNT state, etc, etc. That’s
certainly not that hard to do, but you get the feeling that there should be a
better way. Now in Oracle 12c, there is –
using the ALTER DATABASE MOVE DATAFILE command.
With this command, you can move a datafile, while it’s online, in one
simple step. Let’s set this up.
SQL> create
tablespace test datafile '/oracle/base/oradata/TEST1/
Tablespace
created.
SQL> create
table altdotoracle.tab1 (col1 number) tablespace test;
Table created.
SQL> insert
into altdotoracle.tab1 values (1);
1 row created.
SQL> commit;
Commit
complete.
Let’s go the extra mile and lock the table in that datafile in another
session.
SQL> lock
table altdotoracle.tab1 in exclusive mode;
Table(s)
Locked.
Now let’s use the command.
SQL> alter
database move datafile '/oracle/base/oradata/TEST1/
2 to
'/oracle/base/oradata/TEST1/
Database altered.
That’s all there is to it.
Datafile moved/renamed in one step while a table it contained was
locked.
SQL> select
file_name from dba_data_files where file_name like '%newtest%';
FILE_NAME
------------------------------
/oracle/base/oradata/TEST1/
