move tablespace datafile between filesystems

Move datafile to another filesystem on a separate mount point

Offline the tablespace
SQL>ALTER TABLESPACE DOMAIN OFFLINE;

Move datafile to new location
$mv /u01/domain01.dbf /opt/

Rename datafile
SQL>ALTER TABLESPACE DOMAIN RENAME DATAFILE '/u01/domain01.dbf' TO '/opt/domain01.dbf';

Online the tablespace again
SQL>ALTER TABLESPACE DOMAIN ONLINE;



For Temporary tablespace
SQL>ALTER DATABASE TEMPFILE '/u01/tempdom1.dbf' OFFLINE;

Drop the existing tablespace
SQL>DROP TABLESPACE TEMPDOMAIN INCLUDING CONTENTS;

Create new Temporary tablespace
SQL>CREATE TEMPORARY TABLESPACE TEMPDOMAIN
TEMPFILE '/opt/tempdom1.dbf' SIZE 200 M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;