oracle .dbf文件过大_oracle表空间数据文件迁移

方法一：

1、删除表空间

drop tablespace MYTEMP including contents and datafiles;

2.修改表空间为Offline：

SQL> alter temporary tablespace temp offline;

3.拷贝表空间文件

cp /ora_files/datafiles/temp01.dbf /oradata/gzy/oradata/temp01.dbf

cp /archivelog/gzy/oradata/temp02.dbf /oradata/gzy/oradata/temp02.dbf

4.修改oracle表空间指向地址

alter database rename file '/ora_files/datafiles/temp01.dbf' to '/oradata/gzy/oradata/temp01.dbf'

alter database rename file '/archivelog/gzy/oradata/temp02.dbf' to '/oradata/gzy/oradata/temp02.dbf'

alter database rename file '/ora_files/datafiles/BIUSER_TMP01.dbf' to '/oradata/gzy/oradata/BIUSER_TMP01.dbf'

alter database rename file '/ora_files/datafiles/BIUSER_TMP02.dbf' to '/oradata/gzy/oradata/BIUSER_TMP02.dbf'

5.修改表空间为Online

SQL> alter tablespace temp online;

6、验证

select name from v$tempfile;

select file_name, tablespace_name from dba_temp_files where tablespace_name='BIUSER_TMP';

select status from V$instance;

7. 手动删除表空间物理文件

rm -rf /ora_files/datafiles/temp01.dbf

rm -rf /archivelog/gzy/oradata/temp02.dbf

方法二：

1、关闭数据库

shutdown immediate

2、复制数据文件到新的位置

cp /ora_files/datafiles/temp01.dbf /oradata/gzy/oradata/temp01.dbf

cp /archivelog/gzy/oradata/temp02.dbf /oradata/gzy/oradata/temp02.dbf

3、启动数据库到mount状态

startup mount;

4、修改数据文件位置

alter database rename file '/ora_files/datafiles/temp01.dbf' to '/oradata/gzy/oradata/temp01.dbf'

alter database rename file '/archivelog/gzy/oradata/temp02.dbf' to '/oradata/gzy/oradata/temp02.dbf'

alter database rename file '/ora_files/datafiles/BIUSER_TMP01.dbf' to '/oradata/gzy/oradata/BIUSER_TMP01.dbf'

alter database rename file '/ora_files/datafiles/BIUSER_TMP02.dbf' to '/oradata/gzy/oradata/BIUSER_TMP02.dbf'

5、打开数据库

alter database open;

6、检查数据文件

select file_name, tablespace_name from dba_temp_files where tablespace_name='BIUSER_TMP';