Oracle11gr2 streams replication in a two database

（流复制-单向复制-单表复制）

9. 支持大对象clob,blob的操作

 复制ddl clob字段

sqlplus hr/hr@dbmaster

alter table hr.employees add conxt clob;

sqlplus hr/hr@dbsnap

SQL> desc hr.employees;

Name           Type          Nullable Default Comments

-------------- ------------- -------- ------- --------

EMPLOYEE_ID    NUMBER(6)                              

FIRST_NAME     VARCHAR2(30)  Y                        

LAST_NAME      VARCHAR2(25)  Y                         

EMAIL          VARCHAR2(25)  Y                        

PHONE_NUMBER   VARCHAR2(20)  Y                        

HIRE_DATE      DATE          Y                        

JOB_ID         VARCHAR2(10)  Y                        

SALARY         NUMBER(8,2)   Y                        

COMMISSION_PCT NUMBER(2,2)   Y                        

MANAGER_ID     NUMBER(6)     Y                        

DEPARTMENT_ID  NUMBER(4)     Y                        

TEXT           VARCHAR2(100) Y                        

CONXT          CLOB          Y             

复制dml clob字段

sqlplus hr/hr@dbmaster

insert into hr.employees (employee_id,conxt) values(300,'iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii ling inglang inendgn ding');

sqlplus hr/hr@dbsnap

select employee_id,conxt from hr.employees where employee_id=300;

       EMPLOYEE_ID CONXT

      300            <CLOB>

查看conxt 的内容：

iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii ling inglang inendgn ding

sqlplus hr/hr@dbmaster

更新bonxt的内容为jpg图片.

1.  创建临时表，把需要加载的大对象插入到临时表

create table temp_20130728_xiangyang  (

    name       varchar2(30),

    content blob) tablespace USERS;

2. 创建外部表目录，把大对象，如jpg文档

create or replace directory dir1 as 'c:\file\lob';

3. 执行脚本，把大对象xiangyang.jpg插入临时表

 declare

   l_bfile bfile;

   l_blob  blob;

   l_str   varchar2(1000);

  begin

     insert into temp_20130728_xiangyang (name,content)

      values ('xiangyang.jpg',empty_blob())

    returning content into l_blob;

        l_bfile := bfilename('DIR1','xiangyang.jpg');

        dbms_lob.fileopen(l_bfile);

        dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));

         dbms_lob.fileclose(l_bfile);

         commit;

       exception when others then

         l_str:=sqlerrm(sqlcode);

      dbms_output.put_line(l_str);

 end;

/

4. 查询临时表大对象

 select * from temp_20130728_xiangyang;

5.更新业务表大对象

  update hr.employees

    set bonxt = (select content

                     from temp_20130728_xiangyang

                    where name='xiangyang.jpg')

                    where employee_id='300'

图片成功加载到源库，通过streams，应用到目标库。