Moving Datafile ( NOARCHIVELOG mode )

SQL> select name,bytes,status from v$datafile;

NAME BYTES STATUS
---------- -------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\USERS01.DBF 5242880 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE


SQL> Alter tablespace users offline;

Tablespace altered.

SQL> select name,bytes,status from v$datafile;

NAME BYTES STATUS
---------- -------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF 0 OFFLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE

Now copy the datafile to the new location using OS command or manually and follow the below steps:

SQL>ALTER TABLESPACE USERS RENAME DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\USERS01.DBF' TO 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF';

Tablespace altered.

SQL>
SQL> Alter tablespace users online;

Tablespace altered.

SQL> select name,bytes,status from v$datafile;

NAME BYTES STATUS
---------- -------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSTEM01.DBF 513802240 SYSTEM
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\UNDOTBS01.DBF 41943040 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\SYSAUX01.DBF 419430400 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\NEW\USERS01.DBF 5242880 ONLINE
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DB10G\EXAMPLE01.DBF 104857600 ONLINE

SQL> spool off;