Moving Data Files Between Disk Groups With ALTER DATABASE

You can use the ALTER DATABASE MOVE DATAFILE SQL statement to move data files between disk groups while the database is open and users are accessing the data files.

Example 5-3 shows how you can move a data file from the DATA disk group to the DATA2 disk group.

See Also:

Oracle Database Administrator's Guide for more information about using the ALTER DATABASE MOVE DATAFILE SQL statement to move data files
Oracle Database SQL Language Reference for information about using the ALTER DATABASE SQL statement.
"Moving Data Files Between Oracle ASM Disk Groups Using RMAN" for information about using RMAN to move data files between disk groups

Example 5-3 Moving data files online with ALTER DATABASE MOVE DATAFILE

/* Create a new disk group DATA2 using ASMCA, ASMCMD, or SQL */
/* Then create appropriate directories in the DATA2 disk group */

ALTER DISKGROUP data2 ADD DIRECTORY '+DATA2/ORCL';

ALTER DISKGROUP data2 ADD DIRECTORY '+DATA2/ORCL/DATAFILE';

/* Move the EXAMPLE data file in DATA to EXAMPLE_STORAGE in DATA2
ALTER DATABASE MOVE DATAFILE '+DATA/ORCL/DATAFILE/EXAMPLE.266.798707687' 
      TO '+DATA2/ORCL/DATAFILE/EXAMPLE_STORAGE';

SELECT SUBSTR(name,1,64) FROM V$DATAFILE;
 
SUBSTR(NAME,1,64)
----------------------------------------------------------------
+DATA/ORCL/DATAFILE/system.258.798707555
+DATA2/ORCL/DATAFILE/example_storage
+DATA/ORCL/DATAFILE/sysaux.257.798707491
+DATA/ORCL/DATAFILE/undotbs1.260.798707621
+DATA/ORCL/DATAFILE/users.259.798707621