oracle 修改数据文件位置（路径）（移动）

一、关库修改

select file_name,tablespace_name from dba_data_files;

select name from v$datafile;

show parameter standby_file_management

alter system set standby_file_management=‘MANUAL’ scope=both;

shutdown immediate;

mv xxxx /xxx/xxx/

SQL> startup mount;

select ‘ALTER DATABASE RENAME FILE ‘||’’‘’||name || ‘’‘’ ||’ to ’ ||‘’‘’ || ‘/backup/oradata/hisdb_dg’ || substr(name,instr(name,‘/’,-1)) ||‘.dbf’|| ‘’‘’ || ‘;’ from v$datafile;

select ‘ALTER DATABASE RENAME FILE ‘||’’‘’||name || ‘’‘’ ||’ to ’ ||‘’‘’ || ‘/backup/oradata/hisdb_dg’ || substr(name,instr(name,‘/’,-1)) ||‘.dbf’|| ‘’‘’ || ‘;’ from v$tempfile;

alter database rename file ………………

alter database open;

二、在线修改（停表空间）

不能动system表空间

select file_name,tablespace_name,online_status from dba_data_files;

1.offline表空间

alter tablespace tablespace_name offline;

2.移动数据文件

mv /oracle/app/orcldata/orcl/ts_mcc_1.dat /data/ts_mcc_1.dat

3.rename数据文件

select ‘ALTER DATABASE RENAME FILE ‘||’’‘’||name || ‘’‘’ ||’ to ’ ||‘’‘’ || ‘/backup/oradata/hisdb_dg’ || substr(name,instr(name,‘/’,-1)) ||‘.dbf’|| ‘’‘’ || ‘;’ from v$datafile;

4.online表空间

alter tablespace TS_MCC online;

三、在线修改（停部分数据文件）

1).offline表空间

alter database datafile 7 offline;

2).移动数据文件

mv /aaa/abc.ora to /bbb/abc.ora

3).rename数据文件(改一下，不要后面加.dbf)

select ‘ALTER DATABASE RENAME FILE ‘||’’‘’||name || ‘’‘’ ||’ to ’ ||‘’‘’ || ‘/backup/oradata/hisdb_dg’ || substr(name,instr(name,‘/’,-1)) ||‘.dbf’|| ‘’‘’ || ‘;’ from v$datafile;

Alter database rename file ‘/aaa/abc.ora’ to ‘/bbb/abc.ora’;

4).recover

Alter database recover datafile 92;

5).online表空间

alter database datafile 7 online;

四、在线修改（12c及以上使用）

12c开始可以用move来在线重命名和移动数据文件（19c适用）

ALTER DATABASE MOVE DATAFILE ‘/data/ultradb01.dbf’ TO ‘/data/ultradb02.dbf’;

加keep参数可以保留原始dbf

ALTER DATABASE MOVE DATAFILE ‘/data/ultradb01.dbf’ TO ‘/data/ultradb02.dbf’ keep;