How to move tablespace to a new location

1)    Take the tablespace offline
ALTER TABLESPACE tablespace-name OFFLINE;
2)    Use the OS to Move the tablespace to the new location
3)    Run the following command:
ALTER TABLESPACE tablespace-name RENAME DATAFILE ‘OS path to old tablespace\tablespace-datafile- name.dbf’ TO ‘OS path to new location\tablespace-datafile-name.dbf’;
4)    Take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;
If the following error is displayed
ORA-01113: file n needs media recovery
ORA-01110: data file n: ‘new location file name’

Issue the the following command:
recover datafile ‘new location file name ‘;
and take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;

Share on RedditEmail this to someoneShare on Google+Share on LinkedInShare on FacebookDigg thisTweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *