How to remove datafile path

一、No Archive Mode
    如果数据库处于非归档模式下，可以通过如下步骤更改文件路径:
    a.关闭数据库
    b.系统级进行文件复制
    c.启动数据库到mount状态
    d.通过SQL修改数据文件位置
    e.打开数据库

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

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

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/conner/archive
Oldest online log sequence     150
Current 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 bytes
Fixed Size                   451884 bytes
Variable Size              37748736 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database 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.dbf

SQL>

二、采用offline的方式

1. 先将表空间脱机
SQL> alter tablespace mars_base_data offline;
表空间已更改。

2. 在操作系统中将文件转移至需要的新地址
cp /opt/oracle/test.dbf /opt/oracle/oradata/conner/test.dbf

3. 更新控制文件
SQL> alter tablespace mars_base_data rename datafile 'D:\ORACLE\ORADATA\MARS_BAS
E_DATA03.DBF' to 'D:\ORACLE\ORADATA\HENRY\MARS_BASE_DATA03.DBF';
表空间已更改。

4.. 将表空间联机
SQL> alter tablespace mars_base_data online;
表空间已更改。

参考文献：
1. http://www.eygle.com/archives/2005/11/oracle_howt_move_datafile2.html
2. http://blog.**.com/html/89/750489-164106.html
3. http://hi.baidu.com/zidane_xue/blog/item/2e876d34667aff49241f1475.html

ITPub博客

how to remove datafile path

最新文章