oracle通过dblink,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;

/

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。