ORACLE 10G 现在支持在相同服务器的不通目录之间或者一个远程服务器与一个本地服务器之间复制二进制文件，可以使用dbms_file_transfer中的过程。包含3个过程
COPY_FILE PUT_FILE GET_FILE 具体作用和FTP 中的GET PUT相似。
试验COPY_FILE如下：（通过COPY_FILE复制文件改变表空间的数据文件位置）
1、查看现在USERS表空间数据文件位置为 /oradata/test/users01.dbf
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------
1 9 2005-6-30 19: 0 1 SYSTEM READ WRITE 485082 2009-1-22 11:51 0 446074 446075 2009-1-22 1 503316480 61440 0 8192 /oradata/test/system01.dbf 0 8192 NONE 0
2 444079 2005-6-30 19: 1 2 ONLINE READ WRITE 485082 2009-1-22 11:51 0 446074 446075 2009-1-22 1 47185920 5760 0 8192 /oradata/test/undotbs01.dbf 0 8192 NONE 0
3 6609 2005-6-30 19: 2 3 ONLINE READ WRITE 485082 2009-1-22 11:51 0 446074 446075 2009-1-22 1 241172480 29440 0 8192 /oradata/test/sysaux01.dbf 0 8192 NONE 0
4 10566 2005-6-30 19: 4 4 ONLINE READ WRITE 485082 2009-1-22 11:51 0 446074 446075 2009-1-22 1 5242880 640 0 8192 /oradata/test/users01.dbf 0 8192 NONE 0
5 453785 2009-1-22 11: 6 5 ONLINE READ WRITE 485082 2009-1-22 11:51 0 0 0 477954048 58344 104857600 8192 /oradata/test/pp.dbf 0 8192 NONE 0
2 使其离线
SQL> alter tablespace users offline;
Tablespace altered
3 创建源目录
SQL> create directory src_dir as '/oradata/test';
Directory created
4创建目标目录
SQL> create directory tgt_dir as '/home/oracle/oradata'
2 ;
Directory created
5 带入参数
SQL> execute dbms_file_transfer.copy_file(source_directory_object => 'src_dir',source_file_name => 'users01.dbf',destination_directory_object => 'tgt_dir',destination_file_name => 'user02.dbf');
PL/SQL procedure successfully completed
6 改变位置
SQL> alter database rename file '/oradata/test/users01.dbf'
2 to '/home/oracle/oradata/user02.dbf';
Database altered
7 使其上线
SQL> alter tablespace users online;
Tablespace altered
8 查看改变后位置为/home/oracle/oradata/user02.dbf
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------
1 9 2005-6-30 19: 0 1 SYSTEM READ WRITE 584717 2009-1-22 13:27 0 446074 446075 2009-1-22 1 555745280 67840 0 8192 /oradata/test/system01.dbf 0 8192 NONE 0
2 444079 2005-6-30 19: 1 2 ONLINE READ WRITE 584717 2009-1-22 13:27 0 446074 446075 2009-1-22 1 125829120 15360 0 8192 /oradata/test/undotbs01.dbf 0 8192 NONE 0
3 6609 2005-6-30 19: 2 3 ONLINE READ WRITE 584717 2009-1-22 13:27 0 446074 446075 2009-1-22 1 241172480 29440 0 8192 /oradata/test/sysaux01.dbf 0 8192 NONE 0
4 10566 2005-6-30 19: 4 4 ONLINE READ WRITE 603044 2009-1-22 13:31 0 561676 603044 2009-1-22 1 5242880 640 0 8192 /home/oracle/oradata/user02.dbf 0 8192 NONE 0
5 453785 2009-1-22 11: 6 5 ONLINE READ WRITE 584717 2009-1-22 13:27 0 0 0 492830720 60160 104857600 8192 /oradata/test/pp.dbf 0 8192 NONE 0
SQL>
