方法一:
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';