Thursday, April 9, 2009

How to move/rename a datafile while database is online but tablespace is required to be taken offline

1-) First take tablespace offline to which datafile belongs:

ALTER TABLESPACE MYTABLESPACE OFFLINE NORMAL;

2-) Move datafile to new location

mv /datadir/datafile.dbf /newdatadir/datafile.dbf

3-) Rename datafile name in database

ALTER TABLESPACE MYTABLESPACE RENAME DATAFILE '/datadir/datafile.dbf' TO '/newdatadir/datafile.dbf';

4-) Bring tablespace online:

ALTER TABLESPACE MYTABLESPACE ONLINE;

No comments: