在非归档模式下如何更改数据文件位置

来源：互联网发布：水果淘宝编辑：程序博客网时间：2024/06/04 19:35

如果数据库处于非归档模式下，可以通过如下步骤更改文件路径:
1.关闭数据库
2.系统级进行文件复制
3.启动数据库到mount状态
3.通过SQL修改数据文件位置
4.打开数据库

以下是实际操作的步骤示范：

1.数据库处于非归档模式

SQL> archive log list;Database log mode              No Archive ModeAutomatic archival             EnabledArchive destination            /opt/oracle/oradata/conner/archiveOldest online log sequence     150Current log sequence           153

2.需要移动test.dbf文件

SQL> select name from v$datafile;NAME------------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/conner/users01.dbf/opt/oracle/test.dbf

3.关闭数据库

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.

4.复制文件到新的位置

SQL> ! cp /opt/oracle/test.dbf /opt/oracle/oradata/conner/test.dbf

5.启动数据库到mount状态

SQL> startup mount;ORACLE instance started.Total System Global Area  101782828 bytesFixed Size                   451884 bytesVariable Size              37748736 bytesDatabase Buffers           62914560 bytesRedo Buffers                 667648 bytesDatabase mounted.SQL> select name from v$datafile;NAME-----------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/conner/users01.dbf/opt/oracle/test.dbf

6.修改文件位置

SQL> alter database rename file '/opt/oracle/test.dbf' to '/opt/oracle/oradata/conner/test.dbf';Database altered.SQL> alter database open;Database altered.SQL> select name from v$datafile;NAME----------------------------------------------------------------/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/conner/users01.dbf/opt/oracle/oradata/conner/test.dbfSQL>