导数据专用sql语句

今天要将一台机器的数据导入到另外一台机器，两个机器有数据库链路连接，写了几个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');

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41451/viewspace-1060043/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41451/viewspace-1060043/