Tuesday 12 August 2008

Relocating Oracle Data Files

Check for location
SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'TABLESPACE';

FILE_NAME
--------------------------------------------------------------------------------
BYTES
----------
/dbfiles/oradata03/loc/FILE_DATA01.dbf
4294967296


Take tablespace offline
SQL> ALTER TABLESPACE FILE_DATA OFFLINE NORMAL;

Tablespace altered.

Copy file to the new location.
cp /dbfiles/oradata03/loc/FILE_DATA01.dbf /dbfiles/oradata05/loc/FILE_DATA01.dbf
Check for file permission and owner.

Do it for Oracle
SQL> ALTER TABLESPACE FILE_DATA RENAME DATAFILE '/dbfiles/oradata03/loc/FILE_DATA01.dbf' to '/dbfiles/oradata05/loc/FILE_DATA01.dbf';

Tablespace altered.


Take tablespace online
SQL> ALTER TABLESPACE FILE_DATA ONLINE;

Tablespace altered.

No comments: