In oracle 12c you can now move a datafile while it is online. This procedure will list the rules of moving or renaming a datafile online and show some examples.
Rules
- If a file of the same name already exists the ALTER DATABASE statement will fail with an error.
- If the REUSE clause is used with the ALTER DATABASE statement and the file already exists, the existing file will be overwritten.
- When the ALTER DATABASE MOVE DATAFILE statement is used a copy of the file is made during the process and adequate space must be available for both the original and the copy for operation to succeed.
- At the end of the operation, the original copy is deleted. Unless the KEEP clause is used.
Examples
- Logon as the oracle software owner and logon to sqlplus.
- Display all datafiles with command: select file_name from dba_data_files;
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oradata/orcl/orcl/system01.dbf
/u01/oradata/orcl/orcl/sysaux01.dbf
/u01/oradata/orcl/orcl/users01.dbf
/u01/oradata/orcl/orcl/undotbs01.dbf
SQL>
- Move the sysaux tablespace datafile from sysaux01.dbf to sysaux01_test.dbf.
SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’
TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’; 2
Database altered.
SQL>
- Display all datafiles with command: select file_name from dba_data_files; NOTE: dbf has been replaced with sysaux01_test.dbf.
SQL> select file_name from dba_data_files;
FILE_NAME
——————————————————————————–
/u01/oradata/orcl/orcl/system01.dbf
/u01/oradata/orcl/orcl/sysaux01_test.dbf
/u01/oradata/orcl/orcl/users01.dbf
/u01/oradata/orcl/orcl/undotbs01.dbf
SQL>
- To overwrite an existing file, add the REUSE clause.
ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’
TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ REUSE;
- To keep the old file, add the KEEP clause.
ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’
TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ KEEP;
- This completes movment of online datafiles.
Larry Catt
OCP