一、 12C前(需要停机)
1. 变更前检查数据及临时文件所在路径
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/UAT/system01.dbf
/u01/oracle/oradata/UAT/sysaux01.dbf
/u01/oracle/oradata/UAT/undotbs01.dbf
/u01/oracle/oradata/UAT/users01.dbf
/u01/oracle/oradata/UAT/example01.dbf
select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/UAT/temp01.dbf
2. 关闭数据库
SQL> shutdown immediate
3. 将数据文件复制至目标目录
cp -r /u01/oracle/oradata/UAT/ /data/UAT/
4. 启动数据库至mount状态
SQL> startup mount;
5. 重命名待迁移数据文件
alter database rename file
'/u01/oracle/oradata/UAT/sysaux01.dbf',
'/u01/oracle/oradata/UAT/users01.dbf',
'/u01/oracle/oradata/UAT/system01.dbf',
'/u01/oracle/oradata/UAT/undotbs01.dbf',
'/u01/oracle/oradata/UAT/example01.dbf',
'/u01/oracle/oradata/UAT/temp01.dbf'
to
'/data/UAT/sysaux01.dbf',
'/data/UAT/users01.dbf',
'/data/UAT/system01.dbf',
'/data/UAT/undotbs01.dbf',
'/data/UAT/example01.dbf',
'/data/UAT/temp01.dbf';
6. 打开数据库
SQL> alter database open;
7. 迁移后检查
-- 查看数据库状态
SQL> select open_mode from v$database;
-- 查看修改后的数据文件位置
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
二、 12c开始(可以在线迁移)
注意ALTER DATABASE MOVE DATAFILE命令不可用于临时文件
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -------------------------------------------------------------------------------------
1 /opt/app/oracle/oradata/ORA12C/datafile/system.dbf
3 /opt/app/oracle/oradata/ORA12C/datafile/sysaux.dbf
4 /opt/app/oracle/oradata/ORA12C/datafile/undotbs1.dbf
6 /opt/app/oracle/oradata/ORA12C/datafile/users.dbf
SQL> alter database move datafile 6 to '/tmp/users.dbf';
Database altered.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -------------------------------------------------------------------------------------
1 /opt/app/oracle/oradata/ORA12C/datafile/system.dbf
3 /opt/app/oracle/oradata/ORA12C/datafile/sysaux.dbf
4 /opt/app/oracle/oradata/ORA12C/datafile/undotbs1.dbf
6 /tmp/users.dbf
参考
http://blog.itpub.net/23249684/viewspace-1155861/
https://oracle-base.com/articles/12c/online-move-datafile-12cr1