下面介绍移动oracle数据文件的两种方法。# 该方法，可以移动任何表空间的数据文件。*关闭数据库*SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.
*移动数据文件，用oracle用户操作*[oracle@test ~]$ mv /u01/app/oracle/oradata/test/system01.dbf /oracledb/test/system01.dbf[oracle@test ~]$ mv /u01/app/oracle/oradata/test/sysaux01.dbf /oracledb/test/sysaux01.dbf[oracle@test ~]$ mv /u01/app/oracle/oradata/test/undotbs01.dbf /oracledb/test/undotbs01.dbf[oracle@test ~]$ mv /u01/app/oracle/oradata/test/users01.dbf /oracledb/test/users01.dbf[oracle@test ~]$ mv /u01/app/oracle/oradata/test/temp01.dbf /oracledb/test/temp01.dbf[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo03.log /oracledb/test/redo03.log[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo02.log /oracledb/test/redo02.log[oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo01.log /oracledb/test/redo01.log
*启动到mount状态*SQL> startup mountORACLE instance started.
Total System Global Area 1.0122E+10 bytesFixed Size 2237088 bytesVariable Size 1610616160 bytesDatabase Buffers 8489271296 bytesRedo Buffers 19468288 bytesDatabase mounted.
SQL> alter database rename file '/u01/app/oracle/oradata/test/system01.dbf' to '/oracledb/test/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/sysaux01.dbf' to '/oracledb/test/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/oracledb/test/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/users01.dbf' to '/oracledb/test/users01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/temp01.dbf' to '/oracledb/test/temp01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/redo01.log' to '/oracledb/test/redo01.log';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/redo02.log' to '/oracledb/test/redo02.log';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/test/redo03.log' to '/oracledb/test/redo03.log';
Database altered.
SQL> alter database open;
Database altered.
*重启验证*SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.
SQL> startupORACLE instance started.
Total System Global Area 1.0122E+10 bytesFixed Size 2237088 bytesVariable Size 1610616160 bytesDatabase Buffers 8489271296 bytesRedo Buffers 19468288 bytesDatabase mounted.Database opened.# 该方法，不能移动system表空间，回滚段表空间和临时段表空间的数据文件。*offline system表空间时报错*SQL> alter tablespace system offline;alter tablespace system offline*ERROR at line 1:ORA-01541: system tablespace cannot be brought offline; shut down if necessary报错：说明system表空间不能offline***由此说明一下system表空间的特性–不能脱机offline –不能置为只读read only –不能重命名 –不能删除
SQL> alter tablespace sysaux offline;
Tablespace altered.
[oracle@test ~]$ cp /oracledb/test/sysaux01.dbf /u01/app/oracle/oradata/test/sysaux01.dbf
SQL> alter tablespace sysaux rename datafile '/oracledb/test/sysaux01.dbf' to '/u01/app/oracle/oradata/test/sysaux01.dbf';
Tablespace altered.
SQL> alter tablespace sysaux online;
Tablespace altered.
*offline UNDO表空间时报错*SQL> alter tablespace UNDOTBS1 offline;alter tablespace UNDOTBS1 offline*ERROR at line 1:ORA-30042: Cannot offline the undo tablespace
*offline TEMP表空间时报错*SQL> alter tablespace TEMP offline;alter tablespace TEMP offline*ERROR at line 1:ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
把需要移动的数据文件对应的表空间offlineSQL> alter tablespace USERS offline;
Tablespace altered.
移动数据文件至目标位置[oracle@test ~]$ cp /oracledb/test/users01.dbf /u01/app/oracle/oradata/test/users01.dbf
修改表空间中数据文件的位置SQL> alter tablespace USERS rename datafile '/oracledb/test/users01.dbf' to '/u01/app/oracle/oradata/test/users01.dbf';
Tablespace altered.
把表空间onlineSQL> alter tablespace users online;
Tablespace altered.#
- alter database方法可以移动任何表空间的数据文件，但其要求数据库必须处于mount状态，故此种方法更适合做整体数据库的迁移。
- alter tablespace方法需要数据库处于open状态，表空间在offline的状态下才可更改。但其不能移动system表空间，undo表空间和temp表空间的数据文件，故此种方法更适合于做用户数据文件的迁移。