数据文件迁移有两种方法：
1 使表空间到脱机状态，然后重命名数据文件，然后再恢复表空间状态
2 关闭数据库，然后起到mount状态，重命名数据文件，然后把库开到open状态
第一种演示如下
第二种
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';建议使用第一种办法，方便，且不需要关库，省掉了好多书面报告
1 使表空间到脱机状态，然后重命名数据文件，然后再恢复表空间状态
2 关闭数据库，然后起到mount状态，重命名数据文件，然后把库开到open状态
第一种演示如下
点击(此处)折叠或打开
-
SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
-
-
FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
-
---------- -------------------------------------------------- ---------- ---------------
-
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
-
2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
-
3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
-
4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
-
5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
-
6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
-
7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
-
8 /u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf LXTBS 10
-
9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10
-
-
9 rows selected.
-
-
SQL> alter tablespace lxtbs offline;
-
-
Tablespace altered.
-
SQL> alter tablespace lxtbs rename datafile \'/u01/app/oracle/oradata/PROD/disk4/lxtbs01.dbf\' to \'/u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf\';
-
-
Tablespace altered.
-
-
SQL> alter tablespace lxtbs online;
-
-
Tablespace altered.
-
-
SQL> select file_id , file_name , tablespace_name , bytes/1024/1024 from dba_data_files;
-
-
FILE_ID FILE_NAME TABLESPACE BYTES/1024/1024
-
---------- -------------------------------------------------- ---------- ---------------
-
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325
-
2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200
-
3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325
-
4 /u01/app/oracle/oradata/PROD/disk3/system02.dbf UNDOTBS2 10
-
5 /u01/app/oracle/oradata/PROD/disk4/undotbs02.dbf UNDOTBS3 10
-
6 /u01/app/oracle/oradata/PROD/disk3/user01.dbf USERS 15
-
7 /u01/app/oracle/oradata/PROD/disk4/user02.dbf USERS 10
-
8 /u01/app/oracle/oradata/PROD/disk3/lxtbs01.dbf LXTBS 10
-
9 /u01/app/oracle/oradata/PROD/disk3/lxtbs02.dbf LXTBS 10
-
- 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博客 ” ，链接：https://blog.itpub.net/29598413/viewspace-1140964/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
