Two methods of using the Oraclerename Data File

Description: red indicates the original path. Purple indicates the first path. altertablespaceusersrenamedatafile.

Two Methods for Oracle rename data files Note: red indicates the original path purple indicates the first type of Destination path alter tablespace users rename datafile

Two Methods for Oracle rename data files

Note: red indicates the original path. Purple indicates the target path.

First

Alter tablespace users rename datafile '=' '***';

In this way, the database must be in the open state, and the tablespace can be changed only in the offline state.

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf ', '/opt/ora10g/oradata/orcl/restart'/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf ';
Alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf ', '/opt/ora10g/oradata/orcl/Shanghai'/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01121: cannot rename database file 107-file is in use or recovery
ORA-01110: data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf'

SQL> alter tablespace users offline;
Tablespace altered.

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf ', '/opt/ora10g/oradata/orcl/restart'/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf ';
Alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf ', '/opt/ora10g/oradata/orcl/Shanghai'/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf'
*
ERROR at line 1:
ORA-01525: error in renaming data files
ORA-01141: error renaming data file 107-new file '/opt/ora10g/oradata/orcl/userrename1.dbf' not found
ORA-01110: data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL>!
[Oracle @ rac1 ~] $ Cp/opt/ora10g/oradata/orcl/user0100.dbf/opt/ora10g/oradata/orcl/userrename1.dbf [oracle @ rac1 ~] $ Cp/opt/ora10g/oradata/orcl/user099.dbf/opt/ora10g/oradata/orc
L/userrename2.dbf
[Oracle @ rac1 ~] $ Exit
Exit

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf ', '/opt/ora10g/oradata/orcl/restart'/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf ';
Tablespace altered.

SQL> alter tablespace users online;
Tablespace altered.