Move a Datafile Online with ALTER DATABASE MOVE DATAFILE

calendar Aug 28, 2026 / · 10 min read · Oracle DBA ALTER DATABASE Move Datafile Online Datafile Move OMF Oracle 12c Datafile Management Oracle Administration  ·
Share on: linkedin copy

Move a Datafile Online with ALTER DATABASE MOVE DATAFILE

Purpose

Oracle 12.1 closed a gap that had existed since the earliest releases: moving or renaming a datafile always meant taking something offline first. A DBA had three choices — switch the tablespace offline, switch the individual datafile offline, or shut the whole instance down to a mounted state — and in every case, an operating-system copy command had to run in the middle of the outage before the database (or the tablespace, or the file) could come back online. ALTER DATABASE's MOVE DATAFILE clause replaced all three procedures with one statement that runs while the database stays open in read/write mode, with no data loss and no maintenance window required.

Under the hood, the command still does the same physical work an OS cp command would: Oracle makes a full copy of the datafile at the destination, and only once that copy has completed successfully does it update the control file's pointers to the new location and remove the old file. That sequencing is why the operation needs roughly twice the size of the file in free space on the source and destination filesystems while it runs — the old and new copies briefly coexist. It is also why the move is not something Flashback Database can undo: once the pointer switch happens, the datafile has permanently moved, and any existing RMAN backups still reference the old path until a new backup is taken.

A DBA reaches for this command in a handful of recurring situations: a mount point running low on space that needs to shed a large datafile to a different volume, a datafile created in the wrong location by mistake, a migration from filesystem storage to Oracle Managed Files or ASM, or a standard tablespace reorganization across storage tiers. This post covers the syntax — including the KEEP and REUSE options and Oracle Managed Files (OMF) auto-naming — the scoping rules inside a multitenant container database, the Data Guard standby procedure, and the errors that show up when the command is pointed at a file type it does not support.

Code

 1-- Step 1: find the file number and current path before moving anything
 2SELECT file#, name FROM v$datafile ORDER BY file#;
 3
 4SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;
 5
 6-- Step 2a: basic move/rename, source and destination both given by name
 7ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf'
 8  TO '/tmp/system01.dbf';
 9
10-- Step 2b: move by file number instead of name, and keep the original copy
11ALTER DATABASE MOVE DATAFILE 1
12  TO '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP;
13
14-- Step 2c: omit TO and let Oracle Managed Files name the destination
15ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata/cdb1';
16ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf';
17
18-- Step 2d: overwrite a file that already exists at the destination path
19ALTER DATABASE MOVE DATAFILE '/u01/oradata/DBTEST/demo01.dbf'
20  TO '/u02/oradata/DBTEST/demo01.dbf' REUSE;
21
22-- Step 3: inside a CDB, a PDB's own datafile must be moved from that PDB
23ALTER SESSION SET container = pdb2;
24ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf'
25  TO '/tmp/system01.dbf' REUSE;
26
27-- RAC: resync a node's SGA-cached datafile status from the control file
28ALTER SYSTEM CHECK DATAFILES GLOBAL;

Code Breakdown

Step 1: locating the file number and current path

V$DATAFILE.FILE# and DBA_DATA_FILES.FILE_ID return the same identifying number for a given datafile, and either one can be used as the MOVE DATAFILE source in place of the full path. Pulling this first is worth the extra query: specifying the source by number instead of by a long path string removes any risk of a typo in a path that has to match exactly.

Step 2a: basic move by name

ALTER DATABASE MOVE DATAFILE '<source>' TO '<destination>' is the core form. The source can be given by filename, ASM filename, or file number; the destination must always be given as a filename or ASM filename — there is no destination-by-number form. By default, once the copy completes, the original file is deleted and the operation will not overwrite an existing file at the destination path.

Step 2b: KEEP

Adding KEEP retains the original file on disk after the move instead of deleting it — useful when the old copy needs to be kept as an ad hoc backup or reviewed before being removed manually. KEEP cannot be used when the source file is an Oracle Managed File; if it is, the keyword is silently ignored and the OMF-named original is deleted anyway, so a script depending on KEEP should first confirm the source is not OMF-named before relying on it.

Step 2c: OMF auto-naming

Leaving off the TO clause is valid only when DB_CREATE_FILE_DEST is set. In that case Oracle creates the destination file in that directory with a generated Oracle Managed Files name rather than requiring the DBA to construct one — the same naming convention OMF uses for datafiles created any other way.

Step 2d: REUSE

REUSE tells Oracle it is acceptable to overwrite a file that already exists at the destination path. Without it, MOVE DATAFILE will not silently overwrite a pre-existing file — which is the safer default when moving into a directory that might already hold a stale copy from a previous attempt.

Step 3: PDB scoping in a multitenant database

A container database's root cannot move a datafile that belongs to a pluggable database — issuing the command from the root against a PDB's file returns an error stating the file number does not exist, because the root's view of V$DATAFILE does not include that PDB's files in this context. The session has to first switch into the target PDB with ALTER SESSION SET container = <pdb_name> before the same MOVE DATAFILE syntax will work against that PDB's own datafile.

RAC: ALTER SYSTEM CHECK DATAFILES

This is a separate, older command from MOVE DATAFILE, but it solves an adjacent problem on Real Application Clusters: it resynchronizes each instance's SGA-cached view of datafile status against the control file. It is the fix when a datafile change made from one RAC node — a permission or ownership change, for instance — leaves another node still reading stale cached state and failing to recognize the file. GLOBAL (the default) applies the resync to every open instance; LOCAL restricts it to the instance issuing the command.

Key Points

  • The destination must always be a filename — the source can be given by file number, but there is no destination-by-number syntax.
  • KEEP is ignored on an OMF source file. The original is deleted regardless of the keyword when the source datafile is Oracle Managed.
  • REUSE is what allows overwriting an existing destination file; without it, a pre-existing file at the target path blocks the move.
  • The operation needs roughly double the file's size in free space while it runs, since Oracle copies the file before removing the original.
  • Tempfiles, redo log files, and control files cannot be moved with this command — attempting it returns an ORA-01516 error for tempfiles.
  • A PDB's datafile must be moved from inside that PDB, not from the CDB root — ALTER SESSION SET container = <pdb> first.
  • It does not work on an offline datafile, and it works in both ARCHIVELOG and NOARCHIVELOG mode with no extra setup.
  • Flashback Database will not undo the move — once the pointer swap happens, the relocation is permanent.

Insights and Best Practices

Plan the free-space math before starting

Because Oracle copies the file before removing the source, the destination filesystem needs enough room for the full new copy, and the source filesystem needs to retain the original until the copy finishes — effectively double the datafile's size in free space across the two locations for the duration of the move. For a large datafile, checking both filesystems' free space before issuing the command avoids a mid-copy failure from exhausted disk space.

A moved datafile still needs a fresh backup

The move is transparent to running sessions, but it is not transparent to RMAN. Existing backups reference the old path, so restoring from a backup taken before the move — without first moving the datafile back — will fail or restore to the wrong location. Treat a MOVE DATAFILE operation the same as any other structural database change: schedule a new full backup afterward rather than assuming the existing backup schedule already covers it.

Standby databases need their own MOVE DATAFILE run

Moving a datafile on the primary database does not move the corresponding file on a physical standby — the standby's copy stays exactly where it was. To relocate it there too, the redo apply process on the standby has to be paused first, the same ALTER DATABASE MOVE DATAFILE statement issued against the standby's own copy of the file (by connecting to the standby instance directly), and apply resumed once the move on the standby completes. Skipping the standby side leaves primary and standby storage layouts out of sync, which is easy to miss since replication itself keeps working normally.

Use OMF naming when the exact filename doesn't matter

For routine relocations where the specific destination filename isn't significant — moving off a full mount point onto a fresh volume, for example — setting DB_CREATE_FILE_DEST and omitting the TO clause removes one source of typing errors from the operation and keeps the new file consistent with how OMF names every other managed datafile in that destination.

On RAC, resync with CHECK DATAFILES rather than guessing

If a datafile change made on one RAC node doesn't appear correctly to another node — commonly after a permission or path change outside of MOVE DATAFILE itself — ALTER SYSTEM CHECK DATAFILES GLOBAL resyncs every open instance's cached view from the control file in one step, rather than bouncing an instance to force the refresh.

When to Use This

  • A mount point is running low on space and a large datafile needs to move to a different volume without an outage.
  • A datafile was created in the wrong location by mistake and needs correcting before it grows further.
  • Migrating datafiles from filesystem storage to Oracle Managed Files naming, or between ASM disk groups.
  • Standardizing storage tiering — moving older or less-active tablespace files to slower storage without downtime.
  • Correcting a Data Guard standby's file layout to match a change already made on the primary.
  • Any relocation where the earlier offline-tablespace or shutdown-and-mount procedures would otherwise force a maintenance window.

Troubleshooting Common Issues

ORA-01516: nonexistent log file, data file, or temporary file appears in two common situations: attempting to move a tempfile (not supported by this command at all), or attempting to move a PDB's datafile from the CDB root instead of from inside that PDB. Confirm the file type first, and for a PDB file, switch into that container with ALTER SESSION SET container = <pdb_name> before retrying.

KEEP appears to have no effect. Check whether the source file is Oracle Managed — KEEP is silently ignored on an OMF source, and the original is removed regardless of the keyword.

A destination file already exists and the move fails. Add REUSE to explicitly permit overwriting the existing file at the destination path, after confirming that file is safe to discard.

The standby database's datafile is still in the old location after moving it on the primary. This is expected — the move does not propagate automatically. Pause redo apply on the standby, connect to the standby instance directly, run the same MOVE DATAFILE statement there, and resume apply once it completes.

A restore from an older backup fails to find the datafile. The backup predates the move and still points at the old path. Either move the datafile back to its pre-move location before restoring, or restore into the current location and adjust accordingly — and take a fresh full backup after any future MOVE DATAFILE operation.

References

Posts in this series