How to Move Datafiles in Oracle Database 12c (12.1 & 12.2) Online

Prior to Oracle 12c, moving datafiles has always been an offline task. There were certain techniques you could employ to minimize that downtime, but you couldn’t remove it completely.

Oracle 12c includes an enhancement to the ALTER DATABASE command to allow datafiles to be moved online. This is one of feature which i liked in Oracle 12c, now if are facing space issue where datafiles reside we can move the datafiles while keeping our database open.

It increases the availability of the database.

Basic Syntax:

The text description of the syntax is shown below, but the syntax diagrams and a full description of the ALTER DATABASE command is available in the documentation here.

ALTER DATABASE MOVE DATAFILE ( ‘filename’ | ‘ASM_filename’ | file_number )
[ TO ( ‘filename’ | ‘ASM_filename’ ) ]
[ REUSE ] [ KEEP ]

The source file can be specified using the file number or name, while the destination file must be specified by the file name. The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.

When the source file is an OMF file the KEEP option can not be used. If the destination file is an OMF file, the TO clause can be omitted and the file will be created with an OMF name in the DB_CREATE_FILE_DEST location.

The file number can be queried from the V$DATAFILE and DBA_DATA_FILES views.

Startup the database where we are planning to change the datafiles location:


Find out if the database is CDB or not, so it says YES, find out the datafiles location for which we are planning to move. 2

Alter database command will move the file from 1 location to different location, we need to make sure that the location should exist where we are planning to move the file.

After the file is moved, original file will not be there because it has been moved to new location.

If we use KEEP command then file will be moved and we can find the original file as well on its location.3 4 5

Just verify the location from where we moved the files to where we kept the files.7 8

Hope it helps..

Thanks for viewing.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: