Oracle Tech Talk: Moving Oracle Datafiles from one location to another

Need for moving Oracle Datafiles

Sometimes, when managing Oracle Database, we fall short of disk space on one or more of our disks. Often, approvals and finance comes in the way of fast acquisition or sanction of more disk space. There could be budget constraints or client billing issues etc. Moreover, such disks have one or more Oracle Datafiles(for layman: Files storing actual RDBMS data on disk; internally in form of fixed or variable format plain text files); which are growing at a good rate.
So a solution could be to relocate the files to a new location where more disk space is available.

Often this could be something which might requyire hours of discussion. Yet we have a simpler solution at hand.

Moving Datafiles manually in OFFLINE NORMAL Mode

We can do the above required task- i.e moving Oracle Datafiles from one disk , or disk system(may be racks) to another where more space is available by the below methodology:

1)Put the tablespace which has these datafiles to OFFLINE NORMAL mode:

ALTER TABLESPACE example01 OFFLINE NORMAL;

Here the Tablespace example01 has the desired Datafiles.

2)Now, we need to physically move the physical files say from D drive folder ora11g to E drive folder ora11g_new

3)Then, we use the ALTER TABLESPACE .. RENAME DATAFILE .. command to rename the datafile(s) :

— Rename datafile Db command

ALTER TABLESPACE example01
RENAME DATAFILE ‘D:\ora11g\oradata\inventorydb\example01.dbf’
TO ‘E:\ora11g_new\oradata\inventorydb\example01.dbf’ ;

The above steps makes sure that the Oracle Database system registers the altered location information of the file(s) of the tablespace and works without any further interruption.

4)Next we run the following :

ALTER TABLESPACE example01 ONLINE;

The last step makes sure that the tablespace is fully available to the users and they can work uninterruptedly.

The above does leads to some downtime, but it is much less than the time it could take to do a fully offline operation.

Source: Oracle Base site:
https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

and own hands-on