方法一：在sqlplus中操作（需要手工拷贝）
首先要在数据库open状态下操作，将需要变动的表空间数据文件offline；操作系统层面拷贝；rename
点击(此处)折叠或打开
1. SQL> select file_name,file_id,tablespace_name from dba_data_files;
3. FILE_NAME FILE_ID TABLESPACE
4. -------------------------------------------------- ---------- ----------
5. /u01/app/oracle/oradata/PROD1/users01.dbf 4 USERS
6. /u01/app/oracle/oradata/PROD1/undotbs01.dbf 3 UNDOTBS1
7. /u01/app/oracle/oradata/PROD1/sysaux01.dbf 2 SYSAUX
8. /u01/app/oracle/oradata/PROD1/system01.dbf 1 SYSTEM
9. /u01/app/oracle/oradata/PROD1/example01.dbf 5 EXAMPLE
10. /home/oracle/test01.dbf 6 TEST01
11. /u01/app/oracle/oradata/PROD1/test02.dbf 7 TEST02
13. 7 rows selected.
15. SQL> alter database datafile 6 offline;
17. Database altered.
19. SQL> !cp /home/oracle/test01.dbf /u01/app/oracle/oradata/PROD1/test01.dbf
20. SQL> alter tablespace test01 rename datafile '/home/oracle/test01.dbf' to '/u01/app/oracle/oradata/PROD1/test01.dbf';
22. Tablespace altered.
24. SQL> select file_name,file_id,tablespace_name from dba_data_files;
26. FILE_NAME FILE_ID TABLESPACE
27. -------------------------------------------------- ---------- ----------
28. /u01/app/oracle/oradata/PROD1/users01.dbf 4 USERS
29. /u01/app/oracle/oradata/PROD1/undotbs01.dbf 3 UNDOTBS1
30. /u01/app/oracle/oradata/PROD1/sysaux01.dbf 2 SYSAUX
31. /u01/app/oracle/oradata/PROD1/system01.dbf 1 SYSTEM
32. /u01/app/oracle/oradata/PROD1/example01.dbf 5 EXAMPLE
33. /u01/app/oracle/oradata/PROD1/test01.dbf 6 TEST01
34. /u01/app/oracle/oradata/PROD1/test02.dbf 7 TEST02
36. 7 rows selected.
37. SQL> alter database datafile 6 online;
或者数据库在mount状态下
点击(此处)折叠或打开
1. SQL> startup mount;
2. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
3. ORACLE instance started.
5. Total System Global Area 217157632 bytes
6. Fixed Size 2251816 bytes
7. Variable Size 159384536 bytes
8. Database Buffers 50331648 bytes
9. Redo Buffers 5189632 bytes
10. Database mounted.
11. SQL> !cp /u01/app/oracle/oradata/PROD1/test01.dbf /home/oracle/test01.dbf
13. SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/test01.dbf' to '/home/oracle/test01.dbf';
15. Database altered.
17. SQL> alter database open;
19. Database altered.
21. SQL> select file_id,file_name, status from dba_data_files;
23. FILE_ID FILE_NAME STATUS
24. ---------- -------------------------------------------------- ---------
25. 4 /u01/app/oracle/oradata/PROD1/users01.dbf AVAILABLE
26. 3 /u01/app/oracle/oradata/PROD1/undotbs01.dbf AVAILABLE
27. 2 /u01/app/oracle/oradata/PROD1/sysaux01.dbf AVAILABLE
28. 1 /u01/app/oracle/oradata/PROD1/system01.dbf AVAILABLE
29. 5 /u01/app/oracle/oradata/PROD1/example01.dbf AVAILABLE
30. 6 /home/oracle/test01.dbf AVAILABLE
31. 7 /u01/app/oracle/oradata/PROD1/test02.dbf AVAILABLE
33. 7 rows selected.
方法二：rman自动拷贝
点击(此处)折叠或打开
1. RMAN> run{
2. 2> allocate channel c1 device type disk;
3. 3> sql "alter tablespace test01 offline immediate";
4. 4> set newname for datafile '/home/oracle/test01.dbf' to '/u01/app/oracle/oradata/PROD1/test01.dbf';
5. 5> restore tablespace test01;
6. 6> recover tablespace test01;
7. 7> sql "alter tablespace test01 online";
8. 8> }
10. using target database control file instead of recovery catalog
11. allocated channel: c1
12. channel c1: SID=33 device type=DISK
14. sql statement: alter tablespace test01 offline immediate
16. executing command: SET NEWNAME
18. Starting restore at 06-APR-17
20. channel c1: starting datafile backup set restore
21. channel c1: specifying datafile(s) to restore from backup set
22. channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test01.dbf
23. channel c1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1
24. channel c1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1 tag=TAG20170406T091318
25. channel c1: restored backup piece 1
26. channel c1: restore complete, elapsed time: 00:00:01
27. Finished restore at 06-APR-17
29. Starting recover at 06-APR-17
31. starting media recovery
32. media recovery complete, elapsed time: 00:00:00
34. Finished recover at 06-APR-17
36. sql statement: alter tablespace test01 online
37. released channel: c1
验证
点击(此处)折叠或打开
1. SQL> select file_id,file_name,status from dba_data_files;
3. FILE_ID FILE_NAME STATUS
4. ---------- -------------------------------------------------- ---------
5. 4 /u01/app/oracle/oradata/PROD1/users01.dbf AVAILABLE
6. 3 /u01/app/oracle/oradata/PROD1/undotbs01.dbf AVAILABLE
7. 2 /u01/app/oracle/oradata/PROD1/sysaux01.dbf AVAILABLE
8. 1 /u01/app/oracle/oradata/PROD1/system01.dbf AVAILABLE
9. 5 /u01/app/oracle/oradata/PROD1/example01.dbf AVAILABLE
10. 6 /home/oracle/test01.dbf AVAILABLE
11. 7 /u01/app/oracle/oradata/PROD1/test02.dbf AVAILABLE
13. 7 rows selected.
16. SQL> create table t3 (x) tablespace test01 as select rownum from dual connect by rownum<= 10;
18. Table created

ITPub博客

更换数据文件的位置/名字（system表空间数据文件除外）

最新文章