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_useris--通过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;create or replace procedure sysout( module_id varchar2 ,text varchar2) isbegin dbms_output.put_line( to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff3') || ' ['||module_id||'] ' || substr(text, 1, 1000) );end sysout;