欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

oracle经过dblink拷贝某个用户的数据到本地-sp_copy_datas

更新时间: 2014-01-05 02:05:13 责任编辑: Author_N1

 

oracle通过DBLINK拷贝某个用户的数据到本地-SP_COPY_DATAS
SP_COPY_DATAS暂不支持LONG类型数据的拷贝。
SP_COPY_DATAS
create or replace procedure SP_COPY_DATAS(
  to_user           varchar2
 ,is_create_table   varchar2 default 'N'
 ,is_truncate_table varchar2 default 'N'  
 ,from_dblink       varchar2
 ,from_user         varchar2 default null
 ,table_filter      varchar2 default null
) 
authid current_user
is
--通过DBLINK拷贝数据到本地
/*
create global temporary table GTMP_TABLES (
  TABLE_NAME VARCHAR2(50)
) on commit preserve rows;

create global temporary table GTMP_TEXT  (
  text VARCHAR2(4000),
  type varchar2(20)
) on commit preserve rows;
*/
  v_owner1 varchar2(33)  := (case when from_user is null then null else from_user || '.' end);
  v_module varchar2(100) := 'SP_COPY_DATAS';
  v_sql    varchar2(4000);
  v_date   date := sysdate;

  function get_copy_sql(tb_name varchar2) return varchar2 is 
  begin
    return 
    ('insert /*+append*/ into '||to_user||'.'||tb_name||' nologging
     select * from '||v_owner1 ||tb_name||'@'||from_dblink);
  end;  
  
  function get_create_sql(tb_name varchar2) return varchar2 is 
  begin
    return 
    ('create table '||to_user||'.'||tb_name||' nologging as 
     select * from '||v_owner1 ||tb_name||'@'||from_dblink||'
     where 1 = 0');
  end; 
begin
  --1.确定目标表
  delete from gtmp_tables;  
  if from_user is null then
    execute immediate 
   'insert into gtmp_tables (table_name) 
    select table_name from user_tables@'||from_dblink || '
    where 1 = 1 ' || (case when table_filter is null then null else ' and '||table_filter end);  
  else
    execute immediate 
   'insert into gtmp_tables (table_name) 
    select table_name from all_tables@'||from_dblink||'
    where owner = '''||upper(from_user)||'''' 
    ||(case when table_filter is null then null else ' and '||table_filter end);  
  end if;  

  --2.拷贝数据
  sysout(v_module,'BEGIN SP_COPY_DATAS(to_user='||to_user
   ||',from_dblink='||from_dblink
   ||',table_filter='||table_filter||')');  
  
  --2.1禁用约束
  delete from GTMP_TEXT;
  insert into GTMP_TEXT(TEXT,TYPE)
  select 'alter table '||owner||'.'||table_name||' disable constraint '||CONSTRAINT_NAME as text
    ,CONSTRAINT_TYPE
  from all_constraints  t
  where t.owner = upper(to_user)
    and t.CONSTRAINT_NAME not like 'BIN$%';

  sysout(v_module,'disable all constraints');
  for x in (select text from GTMP_TEXT order by type desc) loop
  begin
    execute immediate x.text;
  exception
    when others then     
      sysout(v_module, '..' || x.text || ' error: '||sqlerrm);
  end;  
  end loop;
  sysout(v_module,'all constraints disabled');
 
  --2.2 拷贝数据
  sysout(v_module,'copy tables begin');
  for x in (select table_name from gtmp_tables order by table_name) loop
  begin
    if is_create_table = 'Y' then
    begin
      execute immediate get_create_sql(x.table_name);
    exception 
      when others then null;
    end;
    end if;
   
    if is_truncate_table = 'Y' then
      execute immediate 'truncate table '||to_user||'.'||x.table_name;
    end if; 
            
    execute immediate get_copy_sql(x.table_name);
    commit;    
    sysout(v_module,'..copy ' || x.table_name || ' OK');    
  exception
    when others then 
      sysout(v_module,'..copy ' || x.table_name || ' error: ' || sqlerrm);
  end;   
  end loop;
  sysout(v_module,'copy tables end');
  
  --2.3 启用约束 
  sysout(v_module,'enable all constraints');
  for x in (select text from GTMP_TEXT order by type) loop
  begin  
    v_sql := replace(x.text,' disable constraint ',' enable constraint ');
    execute immediate v_sql;
  exception
    when others then     
      sysout(v_module, '..' || v_sql || ' error: '||sqlerrm);
  end;  
  end loop; 
  sysout(v_module,'all constraints enabled');
  
  sysout(v_module,'END SP_COPY_DATAS. Elapsed time: '||round((sysdate-v_date)*24*3600)||' seconds');
end SP_COPY_DATAS;


sysout
create or replace procedure sysout(
  module_id varchar2
 ,text      varchar2
) is
begin
   dbms_output.put_line(
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff3') 
    || ' ['||module_id||'] ' || substr(text, 1, 1000)
   );
end sysout;
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:05:13
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/5439/
本文WWW.DOC100.NET DOC100.NET版权所有。