Moving Datafile in NOARCHIVELOG mode

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;