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;
- /
评论暂时关闭