create database link dblink_dados connect to if68420030209 identified by
grupo9 using 'petrolina_dbgrad';
drop database link dblink_dados;
\
create snapshot snap2_domicilio_SP refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_domicilio@dblink_dados where estado in ('SP','RJ','ES','MG');

create snapshot snap2_pessoais_SP refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_pessoais@dblink_dados where rg in (select rg from snap2_domicilio_SP);

create snapshot snap2_profissional_SP refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_profissional@dblink_dados where rg in (select rg from snap2_domicilio_SP);

create snapshot snap2_social_SP refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_social@dblink_dados where rg in (select rg from snap2_domicilio_SP);

create snapshot snap2_resposta_SP refresh complete start with sysdate next
sysdate + 1/1440 as select * from resposta@dblink_dados where rg in (select rg from snap2_domicilio_SP);

########################################################################

create snapshot snap2_domicilio_PR refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_domicilio@dblink_dados where estado in ('PR','RS','SC');

create snapshot snap2_pessoais_PR refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_pessoais@dblink_dados where rg in (select rg from snap2_domicilio_PR);

create snapshot snap2_profissional_PR refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_profissional@dblink_dados where rg in (select rg from snap2_domicilio_PR);

create snapshot snap2_social_PR refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_social@dblink_dados where rg in (select rg from snap2_domicilio_PR);

create snapshot snap2_resposta_PR refresh complete start with sysdate next
sysdate + 1/1440 as select * from resposta@dblink_dados where rg in (select rg from snap2_domicilio_PR);

########################################################################

create snapshot snap2_domicilio_NE refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_domicilio@dblink_dados where estado in ('MA','CE','PI','RN','PB','PE','AL','SE','BA');

create snapshot snap2_pessoais_NE refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_pessoais@dblink_dados where rg in (select rg from snap2_domicilio_NE);

create snapshot snap2_profissional_NE refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_profissional@dblink_dados where rg in (select rg from snap2_domicilio_NE);

create snapshot snap2_social_NE refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_social@dblink_dados where rg in (select rg from snap2_domicilio_NE);

create snapshot snap2_resposta_NE refresh complete start with sysdate next
sysdate + 1/1440 as select * from resposta@dblink_dados where rg in (select rg from snap2_domicilio_NE);

########################################################################

create snapshot snap2_domicilio_AM refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_domicilio@dblink_dados where estado in ('AM','AP','PA','RO','RR','AC');

create snapshot snap2_pessoais_AM refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_pessoais@dblink_dados where rg in (select rg from snap2_domicilio_AM);

create snapshot snap2_Profissional_AM refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_Profissional@dblink_dados where rg in (select rg from snap2_domicilio_AM);

create snapshot snap2_social_AM refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_social@dblink_dados where rg in (select rg from snap2_domicilio_AM);

create snapshot snap2_resposta_AM refresh complete start with sysdate next
sysdate + 1/1440 as select * from resposta@dblink_dados where rg in (select rg from snap2_domicilio_AM);

########################################################################

create snapshot snap2_domicilio_DF refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_domicilio@dblink_dados where estado in ('DF','TO','MT','MS','GO');

create snapshot snap2_pessoais_DF refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_pessoais@dblink_dados where rg in (select rg from snap2_domicilio_DF);

create snapshot snap2_Profissional_DF refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_Profissional@dblink_dados where rg in (select rg from snap2_domicilio_DF);

create snapshot snap2_social_DF refresh complete start with sysdate next
sysdate + 1/1440 as select * from dados_social@dblink_dados where rg in (select rg from snap2_domicilio_DF);

create snapshot snap2_resposta_DF refresh complete start with sysdate next
sysdate + 1/1440 as select * from resposta@dblink_dados where rg in (select rg from snap2_domicilio_DF);

SELECT NAME FROM USER_snapshots ORDER BY NAME;
select name, update_log from user_snapshots;

select * from user_snapshots where name = 'snap2_pessoais_df';

select * from dados_pessoais, dados_profissional where dados_pessoais.rg = dados_profissional.rg;




#############################
drop snapshot snap_domicilio_SP;
drop snapshot snap_pessoais_SP;
drop snapshot snap_profissional_SP;
drop snapshot snap_social_SP;
drop snapshot snap_resposta_SP;
drop snapshot snap_domicilio_PR;
drop snapshot snap_pessoais_PR;
drop snapshot snap_profissional_PR;
drop snapshot snap_social_PR;
drop snapshot snap_resposta_PR;
drop snapshot snap_domicilio_NE;
drop snapshot snap_pessoais_NE;
drop snapshot snap_profissional_NE;
drop snapshot snap_social_NE;
drop snapshot snap_resposta_NE;
drop snapshot snap_domicilio_AM;
drop snapshot snap_pessoais_AM;
drop snapshot snap_Profissional_AM;
drop snapshot snap_social_AM;
drop snapshot snap_resposta_AM;
drop snapshot snap_domicilio_DF;
drop snapshot snap_pessoais_DF;
drop snapshot snap_Profissional_DF;
drop snapshot snap_social_DF;
drop snapshot snap_resposta_DF;

drop snapshot snap_alternativa;
drop snapshot snap_pergunta;
