Wednesday, February 8, 2012 15:10

Moving tablespace to a new location

Tagged with:
Posted by on Friday, February 20, 2009, 9:55
This news item was posted in Oracle category and has 0 Comments so far.

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;

Leave a Reply

You can leave a response, or trackback from your own site.