ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_FILE_TRANSFER 例子

DBMS_FILE_TRANSFER 例子

原创 Linux操作系统 作者:gaopengtttt 时间:2009-02-02 11:34:00 0 删除 编辑

 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>

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

请登录后发表评论 登录
全部评论
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解

注册时间:2008-10-13

  • 博文量
    715
  • 访问量
    3021644