1.在線修改

a.查一下表空間的當(dāng)前位置

SQL> select TABLESPACE_NAME from dba_tablespaces;

b.修改要移動表空間的狀態(tài)為offline

SQL> alter tablespace SYSAUX offline;

c.os下把datafile? sysaux01.dbf'? 移到目標(biāo)路徑 /data/ora_data01/INFA/ 下

d. 修改數(shù)據(jù)文件位置

SQL> alter tablespace SYSAUX rename datafile '/opt/oracle/oradata/INFA/sysaux01.dbf' to '/data/ora_data01/INFA/sysaux01.dbf';

e.修改表空間狀態(tài)為online

SQL> alter tablespace SYSAUX online;

f.檢查一下
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/INFA/system01.dbf
/data/ora_data01/INFA/sysaux01.dbf
/opt/oracle/oradata/INFA/undotbs01.dbf
/opt/oracle/oradata/INFA/users01.dbf
/data/ora_data01/SELADEVW/SELADEVW/infadom01.dbf
/data/ora_data01/SELADEVW/SELADEVW/infarep01.dbf

/data/ora_data01/SELADEVW/SELADEVW/dom0201.dbf

2.離線修改

a.shutdown 數(shù)據(jù)庫

shutdown immediate;

b.使用系統(tǒng)命令copy 數(shù)據(jù)文件到目標(biāo)位置

c.啟動數(shù)據(jù)庫到mount狀態(tài),

startup mount;

d.更改數(shù)據(jù)文件位置

alter database rename file?
'/opt/oracle/oradata/DAC/system01.dbf',
'/opt/oracle/oradata/DAC/sysaux01.dbf',
'/opt/oracle/oradata/DAC/undotbs01.dbf',
'/opt/oracle/oradata/DAC/users01.dbf',
'/data/ora_data01/SELADEVW/SELADEVW/dac01.dbf' to
'/data/ora_data01/DAC/system01.dbf',
'/data/ora_data01/DAC/sysaux01.dbf',
'/data/ora_data01/DAC/undotbs01.dbf',
'/data/ora_data01/DAC/users01.dbf',
'/data/ora_data01/DAC/dac01.dbf';

e.打開數(shù)據(jù)庫
SQL> alter database open;

Database altered.
f.檢查一下 OK
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/data/ora_data01/DAC/system01.dbf
/data/ora_data01/DAC/sysaux01.dbf
/data/ora_data01/DAC/undotbs01.dbf
/data/ora_data01/DAC/users01.dbf
/data/ora_data01/DAC/dac01.dbf

總結(jié)

以上是生活随笔為你收集整理的修改数据文件路径的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。