Several Methods for migrating data files in an OPEN Oracle Database

Sometimes, when the disk space is full and part of the tablespace or data files need to be migrated to the location, we will introduce three methods to move the data file next time. The same is true for moving the tablespace.

1. Use the OS MV mode.

2. Use the backup as copy method.

3. Use RESTORE

The steps for each method are as follows:

1. Use the OS MV Mode

1. 1.1 System for viewing data files
2. SQL> @ files. SQL
3. Type Tablspace Filename File Size Status Sequence Arch
4. --------------------------------------------------------------------------------------------------------------------------------
5. Data TEST1 D: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST1.DBF 10,485,760 Available 0
6. 1.2 put the data file OFFLINE
7. SQL> alter tablespace test1 offline;
8. The tablespace has been changed.
9. SQL> host mv D: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST1.DBF D: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST2.DBF
10. 1.3 modify the data file path
11. SQL> alterdatabase rename file 'd: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST1.DBF 'to 'd: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST2.DBF ';
12. The database has been changed.
13. 1.4 online file
14. SQL> alter tablespace test1 online;
15. The tablespace has been changed.
16. 1.5 view the modified path
17. SQL> @ files. SQL
18. Type Tablspace Filename File Size Status Sequence Arch
19. --------------------------------------------------------------------------------------------------------------------------------
20. Data TEST1 D: \ APP \ LUOPING \ ORADATA \ WINORCL \ TEST2.DBF 10,485,760 Available 0
21. The modification has been completed successfully.

1 2 3 Next Page