文章內(nèi)容

Oracle通過(guò)dblink抽取數(shù)據(jù)處理

發(fā)布時(shí)間: 2012/9/17 17:09:36

就是通過(guò)select * from user_sequence和select * from all_tables 來(lái)獲取sequence和table的名字進(jìn)行拼串

最怕的就是當(dāng)前賬號(hào)的權(quán)限不夠。還好是測(cè)試環(huán)境，我能隨便折騰

-

create or replace package extraction_data is
-- Author : ADMINISTRATOR
-- Created : 2010-12-15 下午 03:36:28
-- Purpose : 抽取數(shù)據(jù)

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('執(zhí)行insert過(guò)程'||T.table_name);*/
--執(zhí)行表的刪除操作，防止重復(fù)插入數(shù)據(jù)
begin
EXECUTE IMMEDIATE 'drop table '||T.table_name ;
EXCEPTION WHEN OTHERS THEN NULL;
end;

--輸出要執(zhí)行的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;');
--將遠(yuǎn)程的表的數(shù)據(jù)拿到本地，可能執(zhí)行不了呀
/*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;
--此過(guò)程沒(méi)有成功執(zhí)行。如果需要可以拼裝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('執(zhí)行insert過(guò)程'||T.table_name);*/
--執(zhí)行表的刪除操作，防止重復(fù)插入數(shù)據(jù)
begin
EXECUTE IMMEDIATE 'drop sequence '||T.sequence_name ;
EXCEPTION WHEN OTHERS THEN NULL;
end;

--輸出要執(zhí)行的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;
/