Posted by Abishek Kathiresan
Introduction:
In previous versions of Oracle when DBAs needed to move datafiles to new storage, or migrate between ASM and filesystem they needed to first take the datafile offline, move the physical file, rename the file, recover it, and bring it online. This usually required application outages, and to be safe DBAs often set the full tablespace read-only during this activity. In Oracle 12c it is not necessary to take the datafile offline for the move.
DEMO:
Step1: In the following example I create tablespace TEST.
SQL> create tablespace test datafile ‘/u01/app/oracle/oradata/test_1.dbf’ size 100M;
Tablespace created.
Step2: Then create a table and insert a record.
SQL> create table test (id integer) tablespace test;
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
Step3: In this case I am moving it within the same filesystem, but we could also move to another filesystem or ASM. I did not need to set tablespace to read-only, or take the datafile offline.
SQL> alter database move datafile ‘/u01/app/oracle/oradata/test_1.dbf’ to ‘/u01/app/oracle/oradata/test_2.dbf’;
Database altered.
SQL> select * from test;
ID
———-
1
