修改数据文件路径

1.在线修改

a.查一下表空间的当前位置

SQL> select TABLESPACE_NAME from dba_tablespaces;

b.修改要移动表空间的状态为offline

SQL> alter tablespace SYSAUX offline;

c.os下把datafile  sysaux01.dbf'  移到目标路径 /data/ora_data01/INFA/ 下

d. 修改数据文件位置

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

e.修改表空间状态为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 数据库

shutdown immediate;

b.使用系统命令copy 数据文件到目标位置

c.启动数据库到mount状态,

startup mount;

d.更改数据文件位置

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.打开数据库
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