I've decided to dedicate my ODC Appreciation Day blog post to “Online Move Data File Operation” – a feature which has been introduced in Oracle 12c.
As a quick background to this blog post, ODC (Oracle Developer Community) appreciation day is Tim Hall’s (Oracle-Base) initiative which started last year (when it was OTN Appreciation Day).


So what is Online move data file operation?

Moving data files is a common-use case, for example, when the DBA would like to move data files to a faster/larger disk. Another common use case is when DBA would like to migrate the files from traditional file systems to Oracle's Automatic Storage Management (more information about Oracle ASM is available in my ODC published article : https://community.oracle.com/docs/DOC-995178)

Prior to Oracle 12c, there was no way to accomplish this task with zero downtime. Common solution was to take the entire tablespace or data file offline, move it to the new location, change the location using the "ALTER DATABASE" command (which  essentially changes the location in the Oracle's control file) and then bring the tablespace/file online again; however, during this operation the entire tablespace or datafile won't be accessible. Better approach to mitigate the downtime was to change the tablespace or data file to be in a read-only mode which will at least allow queries to be executed against the data which reside on that tablespace/file – but it's still not favorable because DMLs/DDLs cannot be executed during that operation.

Oracle 12c allows us to do this completely online with zero downtime – while the database is open and users are accessing the data file. I've presented this feature with several syntax examples as part of my IOUG Collaborate 16 presentation – "Best New Features of  Oracle Database 12c" (slide deck is available here: https://www.slideshare.net/PiniDibask/best-new-features-of-oracle-database-12c).

You could also find several examples and even a short video which demonstrates this cool feature in the Oracle documentation : https://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837


About the Author

Pini Dibask

Pini Dibask is an Oracle Database Technologist and Architect with more than 10 years of experience. During these years Pini has worked as an Oracle DBA and Oracle DBA team leader. He is the Product Manager for Databases Solutions at Quest Software. Pini is an Oracle Database Certified Professional and also OPN Certified Specialist. See his blog at: OracleDBPro.BlogSpot.com

Start the discussion at forums.toadworld.com