oracle通过dblink抽取数据处理

就是通过select * from user_sequence和select * from all_tables 来获取sequence和table的名字进行拼串

最怕的就是当前账号的权限不够。还好是测试环境，我能随便折腾

create or replace package extraction_data is
  -- Author  : ADMINISTRATOR
  -- Created : 2010-12-15 下午 03:36:28
  -- Purpose : 抽取数据
  
  Cursor TNAME is SELECT table_name AS table_name  FROM all_tables@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM where   owner   =upper('tieba');
  
  PROCEDURE EXT_DATA;
  
  PROCEDURE EXT_SEQ;
end extraction_data;
/
create or replace package body extraction_data is

PROCEDURE EXT_DATA IS
BEGIN
FOR  T IN TNAME LOOP
/*  dbms_output.put_line('执行insert过程'||T.table_name);*/
  --执行表的删除操作，防止重复插入数据
  begin
  EXECUTE IMMEDIATE 'drop table '||T.table_name ;
  EXCEPTION WHEN OTHERS THEN NULL;
  end;
  
  --输出要执行的sql
  dbms_output.put_line('create table tieba.'||T.table_name||' as select * from '||T.table_name||'@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM');
  dbms_output.put_line('commit;');
  --将远程的表的数据拿到本地，可能执行不了呀
  /*EXECUTE IMMEDIATE 'create table tieba.'||T.table_name||' as select * from '||T.table_name||'@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM';*/
  
END LOOP;
END EXT_DATA;
--此过程没有成功执行。如果需要可以拼装sequence串
PROCEDURE EXT_SEQ IS
BEGIN
-- Create sequence 
--create sequence ADMIN_MESSAGE_SEQ minvalue 1 maxvalue 999999999 start with 100000 increment by 1 cache 20;
--select * from user_sequence;
FOR  T IN (select * from user_sequences@TB.REGRESS.RDBMS.DEV.US.ORACLE.COM) LOOP
/*  dbms_output.put_line('执行insert过程'||T.table_name);*/
  --执行表的删除操作，防止重复插入数据
  begin
  EXECUTE IMMEDIATE 'drop sequence '||T.sequence_name ;
  EXCEPTION WHEN OTHERS THEN NULL;
  end;
  
  --输出要执行的sql
  dbms_output.put_line('create sequence tieba.'||T.sequence_name||'  minvalue 1 maxvalue 999999999 start with '||T.LAST_NUMBER||' increment by 1 cache 20;');
/* EXECUTE IMMEDIATE 'create sequence tieba.'||T.sequence_name||'  minvalue 1 maxvalue 999999999 start with '||T.LAST_NUMBER||' increment by 1 cache 20;';*/
END LOOP;
 dbms_output.put_line('commit;');
END EXT_SEQ;
end extraction_data;
/

转载于:https://my.oschina.net/abcijkxyz/blog/722142