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




  • PEEUSH TRIKHA

    Recent Posts

    Heart Attack Causes and its Solution

    What is the Main Cause of a Heart Attack? What is its Solution? A heart attack is the blockage of… Read More

    6 months ago

    Understanding the Debt Ceiling: Its Impact, Importance, and Implications

    In the vast economic arena, one term that often takes center stage, inciting extensive debates and discussions, is the "debt… Read More

    11 months ago

    De-Dollarization: The New World Order of Currency and Its Global Impact

    De-Dollarization: The Changing Face of Global Finance The financial landscape is in a state of flux, with an intriguing economic… Read More

    12 months ago

    Unstoppable Bayern Munich: The Story Behind Their 11th Consecutive Bundesliga Title

    The curtains closed on a dramatic Bundesliga season with Bayern Munich standing tall once again, clinching their 11th straight title.… Read More

    12 months ago

    Celine Dion Cancels Concert Tour Due to Deteriorating Stiff-Person Syndrome

    The Unfolding Story of Celine Dion's Health In recent news that has left fans across the globe stunned, iconic singer… Read More

    12 months ago

    Navigating the Crossroads: LeBron James, Anthony Davis, and the LA Lakers’ Uncertain Future

    As the echoes of the recent NBA season start to fade, the attention of enthusiasts is firmly glued to one… Read More

    1 year ago