Move data files!

A data file needs to be moved due to space problems.

The procedure is simple!

SQL> select file_name from dba_data_files;

File_name
--------------------------------------------------------------------------------

D: \ oracle \ oradata \ orcl \ system01.dbf
D: \ oracle \ oradata \ orcl \ sysaux01.dbf
D: \ oracle \ oradata \ orcl \ undotbs01.dbf
D: \ oracle \ oradata \ orcl \ users01.dbf
D: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf
D: \ oracle \ product \ oradata \ orcl \ datasml01.dbf
D: \ oracle \ product \ oradata \ orcl \ datausr01.dbf
D: \ oracle \ product \ oradata \ orcl \ indexlr1_1.dbf
D: \ oracle \ product \ oradata \ orcl \ indexsml01.dbf

9 rows selected.

SQL> alter database rename file 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf't
O 'f: \ oradata \ datalrg01.dbf ';
Alter database rename file 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf 'to 'f:
\ Oradata \ datalrg01.dbf'
*
Error at line 1:
ORA-01511: Error in renaming log/data files
ORA-01121: Cannot rename Database File 5-file is in use or recovery
ORA-01110: Data File 5: 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf'

SQL> alter database datafile 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf 'offl
INE;
Alter database datafile 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf' offline
*
Error at line 1:
ORA-01145: offline immediate disallowed unless media recovery Enabled

SQL> archive log list;
Database Log mode No archive Mode
Automatic Archival disabled
Archive destination use_db_recovery_file_dest
Oldest online log sequence 151
Current Log sequence 153
SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup nomount
Oracle instance started.

Total system global area 644468736 bytes
Fixed size 1376520 bytes
Variable Size 398462712 bytes
Database buffers 239075328 bytes
Redo buffers 5554176 bytes

SQL> alter database Mount;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database datafile 5 offline;

Database altered.

 

Cut/paste the file to DEST location.

Wait until it is done or will raise the error like this.

SQL> alter tablespace datalrg rename datafile 'd: \ oracle \ product \ oradata \ orcl \ da
Talrg01.dbf 'to 'f: \ oradata \ datalrg01.dbf ';
Alter tablespace datalrg rename datafile 'd: \ oracle \ product \ oradata \ orcl \ datalrg
01. DBF 'to 'f: \ oradata \ datalrg01.dbf'
*
Error at line 1:
ORA-01525: Error in renaming data files
ORA-01141: Error renaming Data File 5-New File 'f: \ oradata \ datalrg01.dbf' not
Found
ORA-01110: Data File 5: 'd: \ oracle \ product \ oradata \ orcl \ datalrg01.dbf'
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) the process cannot access the file because it is being used
By another process.

When it is done then run the command.
SQL> alter tablespace datalrg rename datafile 'd: \ oracle \ product \ oradata \ orcl \ da
Talrg01.dbf 'to 'f: \ oradata \ datalrg01.dbf ';

Tablespace altered.

SQL> show user
User is "sys"
SQL> alter database datafile 5 online;
Alter database datafile 5 online
*
Error at line 1:
ORA-01113: File 5 needs media recovery
ORA-01110: Data File 5: 'f: \ oradata \ datalrg01.dbf'

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.

SQL> select file_name from dba_data_files;

File_name
--------------------------------------------------------------------------------

D: \ oracle \ oradata \ orcl \ system01.dbf
D: \ oracle \ oradata \ orcl \ sysaux01.dbf
D: \ oracle \ oradata \ orcl \ undotbs01.dbf
D: \ oracle \ oradata \ orcl \ users01.dbf
F: \ oradata \ datalrg01.dbf
D: \ oracle \ product \ oradata \ orcl \ datasml01.dbf
D: \ oracle \ product \ oradata \ orcl \ datausr01.dbf
D: \ oracle \ product \ oradata \ orcl \ indexlr1_1.dbf
D: \ oracle \ product \ oradata \ orcl \ indexsml01.dbf

9 rows selected.

SQL>