[root@localhost ~]# su - oracle
[oracle@localhost ~]$ lsnrctl start
SQL> conn /as sysdba
Connected to an idle instance.
SQL> select file_name from sys.dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/yuchunju/users01.dbf
/u01/app/oracle/oradata/yuchunju/undotbs01.dbf
/u01/app/oracle/oradata/yuchunju/sysaux01.dbf
/u01/app/oracle/oradata/yuchunju/system01.dbf
/u01/app/oracle/oradata/yuchunju/example01.dbf
[root@localhost yuchunju]# cp users01.dbf test.dbf //以防万一
[root@localhost yuchunju]# mkdir /u01/data
[root@localhost yuchunju]# mv users01.dbf /u01/data/
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 973080568 bytes
Database Buffers 671088640 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/oradata/yuchunju/users01.dbf' to '/u01/data/users01.dbf';
Database altered.
SQL> alter database open;
Database altered.
SQL> select file_name from sys.dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/data/users01.dbf
/u01/app/oracle/oradata/yuchunju/undotbs01.dbf
/u01/app/oracle/oradata/yuchunju/sysaux01.dbf
/u01/app/oracle/oradata/yuchunju/system01.dbf
/u01/app/oracle/oradata/yuchunju/example01.dbf
数据库文件移动成功。
再测试一个
/u01/app/oracle/oradata/yuchunju/sysaux01.dbf
[root@localhost data]# mkdir /u02/data
[root@localhost data]# chown -R oracle:oinstall /u02
首先要关闭数据库
SQL> shutdown immediate
然后移动数据文件
[root@localhost data]# mv /u01/app/oracle/oradata/yuchunju/sysaux01.dbf /u02/data/
等待数据库装载
SQL> startup mount
通过alter语句,修改文件路径和名称。完成后打开数据库
SQL>alter database rename file '/u01/app/oracle/oradata/yuchunju/sysaux01.dbf' to '/u02/data/sysaux01.dbf';
打开数据库
SQL>alter database open;
SQL> select file_name from sys.dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/data/users01.dbf
/u01/app/oracle/oradata/yuchunju/undotbs01.dbf
/u02/data/sysaux01.dbf
/u01/app/oracle/oradata/yuchunju/system01.dbf
/u01/app/oracle/oradata/yuchunju/example01.dbf