oracle dblink 复制表,oracle经过DBLINK拷贝某个用户的数据到本地-SP_COPY_DATAS

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_user

is

--通过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;

sysout

create or replace procedure sysout(

module_id varchar2

,text varchar2

) is

begin

dbms_output.put_line(

to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff3')

|| ' ['||module_id||'] ' || substr(text, 1, 1000)

);

end sysout;