SQL> alter database tempfile 'E:\DMP\TEMP02.DBF' offline;
Database altered.
此时系统层面将E:\DMP\TEMP02.DBF复制到F:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下,并命名为TEMP02.DBF
SQL> alter database rename file 'E:\DMP\TEMP02.DBF' to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF';
Database altered.
SQL> alter database tempfile 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP02.DBF' online;
Database altered.
SQL> select file_name,autoextensible,bytes/1024/1024,tablespace_name from dba_temp_files;
2、更改数据文件位置
2.1、运用更改临时文件的方法更改数据文件位置
SQL> alter tablespace test add datafile 'E:\DMP\test03.DBF' size 30m autoextend on;
表空间已更改.
SQL> select file_name,autoextensible,tablespace_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_NAME AUTOEX TABLESPACE_NAME BYTES/1024/1024
------------------------------------------------------------ ------ -------------------- ---------------
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF NO TEST 100
F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST02.DBF NO TEST 50
E:\DMP\TEST03.DBF YES TEST 30
SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';
Total System Global Area 1.2583E+10 bytes
Fixed Size 12581272 bytes
Variable Size 4194304000 bytes
Database Buffers 8355053568 bytes
Redo Buffers 20971520 bytes
数据库装载完毕.
此时系统层面复制E:\DMP\TEST04.DBF到F:\APP\ADMINISTRATOR\ORADATA\ORCL\目录下
SQL> alter database rename file 'E:\DMP\test04.DBF' to 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST04.dbf';
数据库已更改.
SQL> alter database open;
数据库已更改.
SQL> select v.file#,t.file_name,v.status from dba_data_files t,v$datafile v where t.file_id=v.file# and t.tablespace_name='TEST';