两台oracle服务器通过dblink进行数据的备份

闲来无事，自己写了一个简易版的oracle通过dblink同步数据的存储过程，由于只是表数据的备份所以像存储过程，触发器都没有同步，可以进行多用户进行同步数据，遇到blog、clob数据貌似不行需要改下数据类型，尽量避免同步这样的数据类型：表名注意下oracle规定不能超过30貌似，同时附上运行过程中的一些问题与解决方法，无法修改：

--创建dblink
create public database link testserver connect to strmadmin  IDENTIFIED BY strmadmin 
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)(CONNECT_DATA =(SID= orcl)))';

--测试dblink
select * from act_ru_task@testserver;
--查询dblink
select * from dba_db_links a;

 

下面附上源码：

--添加Authid Current_User ORA-01031: insufficient privileges 错误,存储过程和序列必须在同一用户下
create or replace procedure synctable.proc_sync_tables(v_username_temp in varchar2,dblinkname in varchar2,is_bak number) Authid Current_User
/*
   usernames要同步的用户组合：'user1,user2'
   要同步的远端dblink的名称
   is_bak 是否开启表备份 1开头，其他不开启
   ORA-20000: ORU-10027: buffer overflow, limit of 1000 bytes 
   方法1：set serveroutput on size 10000000 
   方法2：exec dbms_output.enable(10000000); 
   方法3：tools->Preferences->Output: 
   注意：有long类型的数据会报错，解决方法改为blog类型
   外键是需要删除的要不然drop时会出问题，有外键约束   
*/
is
      --查找所有的tables
      v_user_name all_users.username%type;
      --表名称变量
      v_table_name all_tables.TABLE_NAME%type;
      --判断表是否存在
      v_table_exsits number(10);
      --判断备份表是否存在
      v_table_bak_exsits number(10);
      --动态执行创建表sql
      v_create_table_sql varchar2(2000);
      --备份表sql
      v_create_table_bak_sql varchar2(2000);
      --动态执行删除表sql
      v_drop_table_sql varchar2(2000);
      --备份表删除
       v_drop_table_bak_sql varchar2(2000);
      --判断字段是否数量相同源端数量
      --v_colmun_count_source number(10);
      --判断字段是否数量相同目标端数量
      --v_colmun_count_target number(10);
      --对usernames进行解析
      v_usernames varchar2(300);
      v_position number(10);
      usernames varchar2(100) := v_username_temp;
      --定义动态游标
      type ref_cursor_type is ref cursor;
      v_user_names ref_cursor_type;
      v_cursor_sql varchar2(2000);
      --定义表名游标
      v_tablename_cursor ref_cursor_type;
      v_tablename_sql varchar2(2000);
      --日志sql
      --v_log_sql varchar2(200);
      --删除外键，外键名称
      v_constraint_name varchar2(30);
      v_constraint_name_cursor ref_cursor_type;
      --删除外键sql
      v_drop_r_sql varchar2(2000);
      --时间戳
      v_datetime varchar2(50);
      
begin
        --启用日志输出调大缓冲区
        dbms_output.enable(10000000); 
      if usernames is null or usernames='' or  dblinkname is null or dblinkname=''  then
        dbms_output.put_line('参数异常，参数必须都有值');
        insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,10,null,null,null,sysdate,'参数异常，参数必须都有值');
        commit;       
      else
        select to_char(sysdate,'yyyymmddhh24miss') into v_datetime from dual;
        --解析usernames
        if instr(usernames,',')>0 then
          while instr(usernames,',')>0 loop
           v_position := instr(usernames,',');
           v_usernames := v_usernames||case when instr(v_usernames,',')=0 and length(v_usernames)>0 then ',' else '' end ||''''||substr(usernames,1,v_position-1)||'''';
           usernames := substr(usernames,v_position+1);
           end loop;
           --最后拼上剩余的那个值
            v_usernames := v_usernames||case when length(v_usernames)>0 then ',' else '' end||''''||usernames||'''';
         else
           v_usernames := ''''||usernames||'''';
        end if;
         dbms_output.put_line('v_usernames:'||v_usernames);
         v_cursor_sql := 'select username from all_users t where t.username in ('||v_usernames||')';
          dbms_output.put_line('v_cursor_sql='||v_cursor_sql);
         open v_user_names for v_cursor_sql;
         loop 
            fetch v_user_names into v_user_name;
            --找不到记录退出
            exit when v_user_names%notfound;
        --查询要同步的用户
        --for v_user_name in (select username from all_users t where t.username in (v_usernames)) loop
          --查询要同步的用户下面所拥有的所有的表
          v_tablename_sql := 'select TABLE_NAME from all_tables'||'@'||dblinkname||' where OWNER='''||v_user_name||''' and table_name not like ''%SYS%''';
          dbms_output.put_line('v_tablename_sql:'||v_tablename_sql);
           open v_tablename_cursor for v_tablename_sql;
           loop
              fetch v_tablename_cursor into v_table_name;
                 exit when v_tablename_cursor%notfound;
            --for v_table_name in (select TABLE_NAME from all_tables@WHFGJSERVER  where OWNER=v_user_name and table_name not like '%SYS%') loop

                select count(0) into v_table_exsits from all_tables where TABLE_NAME=v_table_name and OWNER=v_user_name;
                v_create_table_sql := 'create table '||v_user_name||'.'||v_table_name||v_datetime||' as select * from '||v_user_name||'.'||v_table_name||'@'||dblinkname;
                if v_table_exsits=0 then
                  --表不存在创建表并同步数据                
                  dbms_output.put_line('创建表：'||v_user_name||'.'||v_table_name||'sql语句为'||v_create_table_sql);
                  execute immediate v_create_table_sql;
                  insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,20,v_user_name,v_table_name,v_create_table_sql,sysdate,'创建表');   
                  commit;                 
                  else
                    --表存在，判断是否存在同样的列，如果不一样，直接drop表,先根据字段数量判断如果不一样则直接drop
                    --select count(0) into v_colmun_count_source from all_tab_cols where table_name=v_table_name and owner=v_user_name;
                    --select count(0) into v_colmun_count_target from all_tab_colss@WHFGJSERVER where table_name=v_table_name and owner=v_user_name;
                    --if v_colmun_count_source!=v_colmun_count_target then
                      if is_bak=1 then--是否需要备份
                         select count(0) into v_table_bak_exsits from all_tables atss where atss.TABLE_NAME=v_table_name||'_BAK' and atss.OWNER=v_user_name;
                           dbms_output.put_line('备份表是否存在：'||v_table_bak_exsits);
                          if v_table_bak_exsits=1 then
                          v_drop_table_bak_sql := 'drop table '||v_user_name||'.'||v_table_name||'_BAK ';
                          
                           execute immediate v_drop_table_bak_sql;
                           insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,30,v_user_name,v_table_name||'_BAK ',v_drop_table_bak_sql,sysdate,'drop备份表');    
                          end if;
                          --删除前备份表数据
                           v_create_table_bak_sql := 'create table '||v_user_name||'.'||v_table_name||v_datetime||'_bak as select * from '||v_user_name||'.'||v_table_name;
                           dbms_output.put_line('创建备份表：'||v_create_table_bak_sql);
                           execute immediate v_create_table_bak_sql;
                           insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,20,v_user_name,v_table_name||v_datetime||'_BAK',v_create_table_bak_sql,sysdate,'创建备份表');  
                         end if;   
                       v_drop_table_sql := 'drop table '||v_user_name||'.'||v_table_name;
                       dbms_output.put_line('drop table：'||v_drop_table_sql);
                       --删除表前，删除外键
                       --删除表之前先删除外键，防止外键引起的异常
                       v_cursor_sql := 'select c.constraint_name from dba_constraints c where c.constraint_type = ''R'' and c.owner = '''||v_user_name
                       ||''' and table_name='''||v_table_name||'''';
                       open v_constraint_name_cursor for v_cursor_sql;
                        loop 
                         fetch v_constraint_name_cursor into v_constraint_name;
                          exit when v_constraint_name_cursor%notfound;
                               v_drop_r_sql := 'ALTER TABLE '||v_user_name||'.'||v_table_name||' DROP CONSTRAINT '||v_constraint_name;
                                execute immediate v_drop_r_sql;
                                insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,40,v_user_name,v_table_name,v_drop_r_sql,sysdate,'删除外键'); 
                         end loop;
                       execute immediate v_drop_table_sql;
                       insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,30,v_user_name,v_table_name,v_drop_table_sql,sysdate,'drop表');    
                        dbms_output.put_line('创建表：'||v_user_name||'.'||v_table_name||v_datetime||'sql语句为'||v_create_table_sql);
                       execute immediate v_create_table_sql;
                       insert into synctable.t_sync_log values(synctable.seq_sync_log.nextval,20,v_user_name,v_table_name,v_create_table_sql,sysdate,'创建表');
                       commit;       
                    -- else--如果表字段数量一样，然后每个对标判断字段名称和类型

                     --end if;

                 end if;
            end loop;
        end loop;  
       close v_user_names;           
     end if; 
end;

--日志表序列
create table synctable.t_sync_log (
log_id number(10) primary key ,
opt_type number(2),
sync_user_name varchar2(100),
sync_table_name varchar2(100),
sysnc_sql varchar2(2000),
sync_date date,
msg varchar2(200)
);
comment on column synctable.t_sync_log.opt_type is '操作类型,10错误信息,20 创建表，30 drop表';
comment on column synctable.t_sync_log.sync_user_name is '用户名';
comment on column synctable.t_sync_log.sync_table_name is  '操作表';
comment on column synctable.t_sync_log.sysnc_sql is  '操作语句';
comment on column synctable.t_sync_log.sync_date is  '操作时间';
comment on column synctable.t_sync_log.msg is '描述信息';
create sequence synctable.seq_sync_log
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

--dblink查询错误ORA-02085: 数据库链接XXX与XXX相连结
--查看dblink报错是解决办法: 
--1.查看global_name 是否和创建的相同: 
select * from global_name; 
--2.设置 global_names=false 
--设置session域的可能没用,弄system级别的 
SHOW PARAMETER GLOBAL_NAMES 
--alter session set global_names=false; 
alter system set global_names=false scope=both; 
--测试多个用户test1,test2
begin 
    synctable.proc_sync_tables('test','test',1);
 end;