一次Oracle数据迁移

时间：2024-08-20 19:06:38

目标数据库：Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

源数据库  ： Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

1.首先想到的是用expdp,impdp。

通过交流发现无法得到源数据库的操作系统密码，这样一来expdp,impdp就不好使了。

2.其次想到的是用plsql developer 来导出数据，但是导出的时候报错了。

一次Oracle数据迁移

放弃此种方法。

3.由于数据量不是很大，考虑使用数据库链的方式来完成。

a.在目标数据库上创建数据库链，链接到目标数据库。

create public database link

　　to_168_bi22 connect to "bi41" identified by "bi41" using

'(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =172.21.1.68)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )';

b.得到用户对象的定义语句 dbms_metadata.get_ddl('TABLE','ACT_GE_BYTEARRAY')

在此纠结了一下，是直接用过程脚本来创建表，还是先生成建表语句，之后再循环灌数据。

c.选择先创建用户对象，之后再插入数据。

这个选择给后续的数据插入带来了一些麻烦，就是索引和约束，特别是外键约束。

在脚本中加入了禁用约束的语句

FOR i IN (SELECT table_name, constraint_name --disable first the foreign key

                FROM user_constraints

               WHERE constraint_type = 'R'

                 AND status = 'ENABLED') LOOP

      EXECUTE IMMEDIATE 'alter table "' || i.table_name || '" disable constraint ' ||

                        i.constraint_name;

    END LOOP i;

    FOR i IN (SELECT table_name, constraint_name -- then disable all constraints

                FROM user_constraints

               WHERE status = 'ENABLED') LOOP

      EXECUTE IMMEDIATE 'alter table "' || i.table_name || '" disable constraint ' ||

                        i.constraint_name;

    END LOOP i;

感觉OK的时候，又报错了。

ERROR:  ORA-22992: cannot use LOB locators selected from remote tables

原来表中有些是LOB字段，无法通过数据链访问直接访问。

最后想到用Oracle的全局临时表的方式将数据抽取过来。

这里也有一个小插曲，就是有50多个表都是有lob字段的。

想写一个循环利用动态SQL来创建global temporary table，并且每次创建之前删除掉改全局临时表。

v_sql := '

    create global temporary table table1

        ON COMMIT PRESERVE ROWS as

    select * from ' || v_table_name;

    execute immediate v_sql;

    v_sql := 'insert into table1 ' || '

    select * from  ' || v_table_name || '@to_168_bi';

    execute immediate v_sql;

    v_sql := 'insert into  ' || v_table_name || '

        select * from  table1';

    execute immediate v_sql;

    commit;

这样创建的临时表默认是 COMMIT delete ROWS 的。

发现数据没有抽取过来，初步怀疑是动态执行SQL的时候自动提交了，这里有些没有想清楚。

之后创建全局临时表的时候加上了ON COMMIT PRESERVE ROWS。

数据是抽过来了，但是drop的时候会报错：ORA-14452: 试图创建, 变更或删除正在使用的临时表中的索引

declare

  v_table_name varchar2(32);

  v_sql        varchar2(2000);

  v_cnt number;

  cursor cur is

    select t.TABLE_NAME

      from user_tables t

     where t.TABLE_NAME

           in (select st.table_name

                 from user_tab_columns st

                where st.DATA_TYPE in ('CLOB', 'BLOB'))

     order by t.TABLE_NAME;

begin

  for i in cur loop

    v_table_name := i.table_name;

    v_sql        := 'truncate table ' || v_table_name;

    execute immediate v_sql;

    select count(1) into v_cnt from user_tables t

    where t.TABLE_NAME=upper('table1');

    if v_cnt >0 then

       execute immediate 'drop table table1';

    end if; 

    v_sql := 'create global temporary table table1

    ON COMMIT PRESERVE ROWS

    as' || '

    select * from  ' || v_table_name ||'@to_168_bi';

    execute immediate v_sql;

    v_sql := 'insert into  ' || v_table_name || '

    select * from table1';

    execute immediate v_sql;

    commit;

  end loop;

exception

  when others then

    dbms_output.put_line(v_table_name || ':' || sqlcode || ':' || sqlerrm);

end get_data_from168;

之后测试了一下，

SQL> create global temporary table table1 as select * from employees;

Table created.

SQL> drop table table1;

Table dropped.

SQL> create global temporary table table1 on commit preserve rows as select * from employees;

Table created.

SQL> drop table table1;

drop table table1

           *

ERROR at line 1:

ORA-14452: attempt to create, alter or drop an index on temporary table already

in use

实验证明创建glob temporary table 的时候如果添加了on commit preserve rows在session没有退出的情况下是没发drop的。

之后这样写在SQLplus 中执行却没什么问题，plsql developer 的test procedure的方式是多session？

declare

  v_table_name varchar2(32);

  v_sql        varchar2(2000);

  v_cnt number;

  cursor cur is

    select t.TABLE_NAME

      from user_tables t

     where t.TABLE_NAME

           in (select st.table_name

                 from user_tab_columns st

                where st.DATA_TYPE in ('CLOB', 'BLOB'))

     order by t.TABLE_NAME;

begin

  for i in cur loop

    v_table_name := i.table_name;

    v_sql        := 'truncate table ' || v_table_name;

    execute immediate v_sql;

    select count(1) into v_cnt from user_tables t

    where t.TABLE_NAME=upper('table1');

    if v_cnt >0 then

       execute immediate 'drop table table1';

    end if; 

   /* v_sql := 'create global temporary table table1

    ON COMMIT PRESERVE ROWS

    as' || '

    select * from  ' || v_table_name ||'@to_168_bi';

    execute immediate v_sql;*/

        v_sql := 'create global temporary table table1

    as' || '

    select * from  ' || v_table_name;

    execute immediate v_sql;

        execute immediate ('insert into table1 select * from  '||v_table_name||'@to_168_bi');

    v_sql := 'insert into  ' || v_table_name || '

    select * from table1';

    execute immediate v_sql;

    commit;

  end loop;

exception

  when others then

    dbms_output.put_line(v_table_name || ':' || sqlcode || ':' || sqlerrm);

end get_data_from168;

后来想想，如果用Kettle的话会更方便，结果用Kettle测试了一下，Kettle对于lob字段处理的也非常好。

一次Oracle数据迁移

之后Kettle会自动生成每个表对应的转换。

测试运行也没什么问题，非常方便。

相关文章