数据文件迁移

数据文件迁移有两种方法：
1  使表空间到脱机状态，然后重命名数据文件，然后再恢复表空间状态
2  关闭数据库，然后起到mount状态，重命名数据文件，然后把库开到open状态

第一种演示如下

点击(此处)折叠或打开

1. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
   
2. 
   
3.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
   
4. ---------- -------------------------------------------------- ---------- ---------------
   
5.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
   
6.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
   
7.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
   
8.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
   
9.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
   
10.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
    
11.          7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
    
12.          8 /u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf LXTBS 10
    
13.          9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10
    
14. 
    
15. 9 rows selected.
    
16. 
    
17. SQL> alter tablespace lxtbs offline;
    
18. 
    
19. Tablespace altered.
    
20. SQL> alter tablespace lxtbs rename datafile \'/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf\' to \'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf\';
    
21. 
    
22. Tablespace altered.
    
23. 
    
24. SQL> alter tablespace lxtbs online;
    
25. 
    
26. Tablespace altered.
    
27. 
    
28. SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
    
29. 
    
30.    FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
    
31. ---------- -------------------------------------------------- ---------- ---------------
    
32.          1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
    
33.          2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
    
34.          3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
    
35.          4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
    
36.          5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
    
37.          6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
    
38.          7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
    
39.          8 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf LXTBS 10
    
40.          9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10
    
41. 
    
42. 9 rows selected.

第二种
alter database rename file '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf' to
'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf';

alter database rename file '/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf' to
'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf';建议使用第一种办法，方便，且不需要关库，省掉了好多书面报告

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29598413/viewspace-1140964/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29598413/viewspace-1140964/