oracle数据文件离线和在线移动

此处案例为在12c下操作在线移动！！

1.oracle 12c之前移动，在线修改，不能offline system表空间

1）确认表空间和数据文件信息

select a.bytes/1024/1024/1024, a.* from dba_data_files a;

select * from dba_data_files a where a.tablespace_name='RA_CATALOG';

select * from v$datafile a where a.file# in('6','7') ;

2）确认目标磁盘组有足够空间

SELECT NAME, TOTAL_MB, FREE_MB FROM V$ASM_DISKGROUP WHERE NAME = 'DELTA';

3）offline表空间

alter tablespace RA_CATALOG offline;

4）移动数据文件

mv '+CATALOG/ZDLRA/DATAFILE/ra_catalog.265.964582299' '+DELTA';

5）rename数据文件

alter database rename file '+CATALOG/ZDLRA/DATAFILE/ra_catalog.265.964582299' to '+DELTA/ZDLRA/DATAFILE/ra_cf.269.964582303';

6）online表空间

alter tablespace RA_CATALOG online;

2.oracle 12c之后可以在线重命名和移动数据文件，迁移中表空间保持读写，业务不中断

1）迁移前文件路径如下

2025-04-09T16:15:03.623364+08:00
ALTER DATABASE MOVE DATAFILE '+CATALOG/ZDLRA/DATAFILE/ra_catalog.265.964582299' TO '+DELTA'
2025-04-09T16:15:03.673966+08:00
Moving datafile +CATALOG/ZDLRA/DATAFILE/ra_catalog.265.964582299 (6) to +DELTA
2025-04-09T16:21:26.474093+08:00
Completed: ALTER DATABASE MOVE DATAFILE '+CATALOG/ZDLRA/DATAFILE/ra_catalog.265.964582299' TO '+DELTA'

4.迁移进度查询 查看后台进程进度，message会显示迁移的字节数

SELECT sid, serial#, opname, sofar, totalwork, units ,start_time,message

FROM v$session_longops

WHERE opname LIKE '%data file move%';