Transfer An Oracle Data File "*. DBF"

Transfer Process:
1. log on to sqlplus first:
C:/Documents and Settings/jbdu> sqlplus "/As sysdba"
2. Change the tablespace to offline:
SQL> alter tablespace users offline;
3. Copy tablespace files
Copy
D:/Oracle/product/10.2.0/oradata/orclado/users01.dbf
H:/Oracle/product/10.2.0/oradata/orclado/users01.dbf

4. Modify the Oracle tablespace pointing address
SQL> alter database rename file 'd:/Oracle/product/10.2.0/oradata/orclado/users01.dbf 'to' H:/Oracle/product/10.2.0/oradata/orclado/users01.dbf ';

5. Change the tablespace to online.
SQL> alter tablespace users online;
The details are as follows:
1. view the tablespace:
Select tablespace_name from dba_tablespaces;

The name of the tablespace.

2. Only some tablespaces can be transferred in this way, and system, temp, sysaux, and other tablespaces can be transferred in 3rd ways.
Alter tablespace users offline;
Alter tablespace example offline;
Alter tablespace cmd_ts offline;

Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/example01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/example01.dbf ';
Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/users01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/users01.dbf ';

Alter tablespace users online;
Alter tablespace example online;
Alter tablespace into _ts online;
3. For system, temp, undotbs1, sysaux, and other tablespaces, first shut down the database, then start to the Mount state, and then modify the data file name.
Shutdown immediate;

Startup Mount;

Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/sysaux01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/sysaux01.dbf ';
Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/system01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/system01.dbf ';
Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/temp01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/temp01.dbf ';
Alter database rename file 'd:/Oracle/product/10.2.0/oradata/orcl/undotbs01.dbf 'to 'H:/Oracle/product/10.2.0/oradata/orcl/undotbs01.dbf ';
Note: if an error is reported
Ora-01113
Ora-01110
Run
Recover datafile'H:/Oracle/product/10.2./oradata/orcl/undotbs01.dbf';

Alter database open;
Run again
SQL> alter tablespace users/example/... online;

% Alter tablespace system offline;
% Alter tablespace undotbs1 offline;
% Alter tablespace sysaux offline;
% Alter tablespace temp offline;

Alter tablespace system online;
Alter tablespace undotbs1 online;
Alter tablespace sysaux online;
Alter tablespace temp online;

4. query the data file name:
Select file_name from SYS. dba_data_files;