今天要将一台机器的数据导入到另外一台机器,两个机器有数据库链路连接,写了几个sql语句来导数据,具体步骤是:
1.sequence同步
2.所有外键失效
3.倒数据
4.所有外键生效
语句如下:
sequence同步语句:
declare
v_sequence_owner varchar2(30);
v_sequence_name varchar2(30);
v_seq_num number;
v_seq_num1 number;
v_seq_num2 number;
begin
for i in (select sequence_owner,sequence_name,last_number from all_sequences@bddb_prod where sequence_owner IN ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDOU','BILLING','BUSI_META','CLIENT','DATA_TRANSFORM','OPEN_SERV','PERMISSION','PRODUCT','UCDB','WORK_FLOW')) loop
execute immediate 'select '||i.sequence_owner||'.'||i.sequence_name||'.nextval from dual' into v_seq_num;
for j in v_seq_num..i.last_number loop
execute immediate 'select '||i.sequence_owner||'.'||i.sequence_name||'.nextval from dual' into v_seq_num2;
end loop;
end loop;
end;
外键失效生效、删除数据和倒数据语句:
select 'alter table '||owner||'.'||table_name||' DISABLE CONSTRAINT '||owner||'.'||constraint_name||';' from all_constraints where owner in ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDOU','BILLING','BUSI_META','CLIENT','DATA_TRANSFORM','OPEN_SERV','PERMISSION','PRODUCT','UCDB','WORK_FLOW');
select 'delete from '||owner||'.'||table_name||';' from all_tables where owner in ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDOU','BILLING','BUSI_META','CLIENT','DATA_TRANSFORM','OPEN_SERV','PERMISSION','PRODUCT','UCDB','WORK_FLOW');
select 'insert into '||owner||'.'||table_name||' select * from '||owner||'.'||table_name||'@bddb_prod ;' from all_tables where owner in ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDOU','BILLING','BUSI_META','CLIENT','DATA_TRANSFORM','OPEN_SERV','PERMISSION','PRODUCT','UCDB','WORK_FLOW');
select 'alter table '||owner||'.'||table_name||' enable CONSTRAINT '||owner||'.'||constraint_name||';' from all_constraints where owner in ('ANALYSIS_DW','ANALYSIS_JOB','ANALYSIS_ODS','ANALYSIZ','BD_USER','BEIDOU','BILLING','BUSI_META','CLIENT','DATA_TRANSFORM','OPEN_SERV','PERMISSION','PRODUCT','UCDB','WORK_FLOW');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/41451/viewspace-1060043/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/41451/viewspace-1060043/