当前位置：首页 -> AI编程基础 -> 数据库编程

使用DBMS_FILE_TRANSFER迁移ASM中的表空间和数据文件(二)

2014-11-24 16:08:12 · 作者: · 浏览: 1
标签: 使用 DBMS_FILE_TRANSFER 迁移 ASM 空间 数据 文件
VARCHAR2 IN
SOURCE_DATABASE VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
PROCEDURE PUT_FILE
ArgumentName Type In/Out Default
----------------------------------------------------- ------ --------
SOURCE_DIRECTORY_OBJECT VARCHAR2 IN
SOURCE_FILE_NAME VARCHAR2 IN
DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN
DESTINATION_FILE_NAME VARCHAR2 IN
DESTINATION_DATABASE VARCHAR2 IN
--开始传输文件
SQL> begin
2 dbms_file_transfer.copy_file('dir2','gtlions.263.808393465','dir1','gtlions01.dbf');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_file_transfer.copy_file('dir2','gtlions.262.808393513','dir1','gtlions02.dbf');
3 end;
4 /
PL/SQL procedure successfully completed.
--重新定位文件位置
SQL> alter database rename file'+DATA01/gt10g/datafile/gtlions.263.808393465' to'/u01/oracle/10g/oradata/gt10g/gtlions01.dbf';
Database altered.
SQL> alter database rename file'+DATA01/gt10g/datafile/gtlions.262.808393513' to '/u01/oracle/10g/oradata/gt10g/gtlions02.dbf';
Database altered.
SQL> alter tablespace gtlions online;
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/10g/oradata/gt10g/system01.dbf
/u01/oracle/10g/oradata/gt10g/undotbs1.dbf
/u01/oracle/10g/oradata/gt10g/sysaux01.dbf
/u01/oracle/10g/oradata/gt10g/users01.dbf
/u01/oracle/10g/oradata/gt10g/gtlions01.dbf
/u01/oracle/10g/oradata/gt10g/gtlions02.dbf
6 rows selected.
需要注意的目标文件如果是存放在ASM中，那么指定生成的文件是alias，实际还是
会生成固定格式的文件：
[oracle@gtser1 ~]$ asmcmd ls -l data01/gt10g/datafile
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE FEB 26 13:00:00 Y COPY_FILE.262.808407481
DATAFILE UNPROT COARSE FEB 26 13:00:00 Y COPY_FILE.263.808407501
DATAFILE UNPROT COARSE FEB 25 16:00:00 Y GTLIONS.256.808328331
DATAFILE UNPROT COARSE FEB 25 15:00:00 Y GTLIONS.258.808326729
DATAFILE UNPROT COARSE FEB 25 15:00:00 Y GTLIONS.265.808326737
DATAFILE UNPROT COARSE FEB 25 16:00:00 Y GTLIONS.281.808328327
DATAFILE UNPROT COARSE FEB 25 16:00:00 Y SYSAUX.272.808328281
DATAFILE UNPROT COARSE FEB 25 16:00:00 Y SYSTEM.271.808328265
DATAFILE UNPROT COARSE FEB 25 16:00:00 Y UNDOTBS1.273.808328327
N gtlions01.dbf =>+DATA01/GT10G/DATAFILE/COPY_FILE.262.808407481
N gtlions02.dbf =>+DATA01/GT10G/DATAFILE/COPY_FILE.263.808407501
-The End-