Update some tables on another server

[henrique290]

I work in my company with 0Racle 10g installed on an IBM and I have another Oracle in a Linux machine. As in IBM Oracle has very large traffic, and I only use five tables that are there, I saved these tables in Oracle that is on Linux. Until okay, so I'd like this process to be automatic and these tables were updated diaramette. Someone has any script example or some external program that makes this maintenance.

[Trevisolli]

Brother follows a tip:

Steps:

1) Create a PL / SQL procedure, comdblinhks, which check the difference of the tables and make their "merge".
2) Create a job, which processes this procedure from time to time.

About the creation of procedure with Merge, and Job, take a sought here in the forum, which will find interesting topics.

Anything, send it there.

[henrique290]

Friend,

I made this SQL with MERGE, but this error.
ORA-00969 In line 2 column 14 missing the ON
I to with the Oracle manual here and the syntax seems correct.
Here goes the script if you can help me in this case.

Select all

Merge 
INTO sisesgoto.stc_agua_tratada d USING stc.stc_agua.tratada s ON(s.cod_us = d.cod_us 
 AND s.cod_eta = d.cod_eta 
 AND s.dt_agua_trat = d.dt_agua_trat) 
WHEN matched THEN 
 
UPDATE 
SET d.cod_us = s.cod_us, 
  d.cod_eta = s.cod_eta, 
  d.dt_agua_trat = s.dt_agua_trat, 
  d.vol_agua_tratada = s.vol_agua_tratada, 
  d.qtd_filtros_servico = s.qtd_filtros_servico, 
  d.nro_filtro_analisado = s.nro_filtro_analisado, 
  d.cons_agua_lavagem = s.cons_agua_lavagem, 
  d.pe_cons_agua_lavagem = s.pe_cons_agua_lavagem, 
  d.dt_inclusao = s.dt_inclusao, 
  d.dt_alteracao = s.dt_alteracao, 
  d.usuario = s.usuario, 
  d.evento = s.evento, 
  d.hrs_recalque_hhh = s.hrs_recalque_hhh, 
  d.hrs_recalque_mm = s.hrs_recalque_mm, 
  d.carreira_md_filtros_lavado_hhh = s.carreira_md_filtros_lavado_hhh, 
  d.carreira_md_filtros_lavado_mm = s.carreira_md_filtros_lavado_mm 
 
WHEN NOT matched THEN 
INSERT 
VALUES(s.cod_us,   s.cod_eta,   s.dt_agua_trat,   s.vol_agua_tratada,   s.qtd_filtros_servico,   s.nro_filtro_analisado,   s.cons_agua_lavagem,   s.pe_cons_agua_lavagem,   s.dt_inclusao,   s.dt_alteracao,   s.usuario,   s.evento,   s.hrs_recalque_hhh,   s.hrs_recalque_mm,   s.carreira_md_filtros_lavado_hhh,   s.carreira_md_filtros_lavado_mm);

[Trevisolli]

Brother,

follows an example of the merge:

Select all

 
MERGE INTO bonuses D 
 USING (SELECT employee_id, salary, department_id FROM employees 
   WHERE department_id = 80) S 
 ON (D.employee_id = S.employee_id) 
 WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 
 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) 
  VALUES (S.employee_id, S.salary*0.1); 

really the on was not in the syntax that passed me.

[henrique290]

Dude, I did what you said but give me an error that I can not find the answer.

Select all

MERGE INTO sisesgoto.stc_agua_tratada1 D 
 USING (SELECT 
  cod_us, 
  cod_eta, 
  dt_agua_trat, 
  vol_agua_tratada, 
  qtd_filtros_servico, 
  nro_filtro_analisado, 
  qtd_filtros_lavado, 
  cons_agua_lavagem, 
  pe_cons_agua_lavagem, 
  dt_inclusao, 
  dt_alteracao, 
  usuario, 
  evento, 
  hrs_recalque_hhh, 
  hrs_recalque_mm, 
  carreira_md_filtros_lavado_hhh, 
  carreira_md_filtros_lavado_mm 
 
  FROM sisesgoto.stc_agua_tratada d 
  WHERE cod_us = 27) S 
  
 ON (s.cod_us=d.cod_us) /* erro acontece aqui */ 
 
 WHEN MATCHED THEN UPDATE SET 
 
  d.cod_us = s.cod_us, 
  d.cod_eta = s.cod_eta, 
  d.dt_agua_trat = s.dt_agua_trat, 
  d.vol_agua_tratada = s.vol_agua_tratada, 
  d.qtd_filtros_servico = s.qtd_filtros_servico, 
  d.nro_filtro_analisado = s.nro_filtro_analisado, 
  d.qtd_filtros_lavado= s.qtd_filtros_lavado, 
  d.cons_agua_lavagem = s.cons_agua_lavagem, 
  d.pe_cons_agua_lavagem = s.pe_cons_agua_lavagem, 
  d.dt_inclusao = s.dt_inclusao, 
  d.dt_alteracao = s.dt_alteracao, 
  d.usuario = s.usuario, 
  d.evento = s.evento, 
  d.hrs_recalque_hhh = s.hrs_recalque_hhh, 
  d.hrs_recalque_mm = s.hrs_recalque_mm, 
  d.carreira_md_filtros_lavado_hhh = s.carreira_md_filtros_lavado_hhh, 
  d.carreira_md_filtros_lavado_mm = s.carreira_md_filtros_lavado_mm 
 
  WHEN NOT MATCHED THEN INSERT 
  ( 
  d.cod_us, 
  d.cod_eta, 
  d.dt_agua_trat, 
  d.vol_agua_tratada, 
  d.qtd_filtros_servico, 
  d.nro_filtro_analisado, 
  d.qtd_filtros_lavado, 
  d.cons_agua_lavagem, 
  d.pe_cons_agua_lavagem, 
  d.dt_inclusao, 
  d.dt_alteracao, 
  d.usuario, 
  d.evento, 
  d.hrs_recalque_hhh, 
  d.hrs_recalque_mm, 
  d.carreira_md_filtros_lavado_hhh, 
  d.carreira_md_filtros_lavado_mm) 
 
  VALUES 
 
  (s.cod_us, 
  s.cod_eta, 
  s.dt_agua_trat, 
  s.vol_agua_tratada, 
  s.qtd_filtros_servico, 
  s.nro_filtro_analisado, 
  s.qtd_filtros_lavado, 
  s.cons_agua_lavagem, 
  s.pe_cons_agua_lavagem, 
  s.dt_inclusao, 
  s.dt_alteracao, 
  s.usuario, 
  s.evento, 
  s.hrs_recalque_hhh, 
  s.hrs_recalque_mm, 
  s.carreira_md_filtros_lavado_hhh, 
  s.carreira_md_filtros_lavado_mm 
    );

is the following

Select all

ON (s.cod_us=d.cod_us) /* erro acontece aqui */ 
com a linha como esta da erro de nome invalido de coluna.

but if I change here

Select all

ON (s.cod_us=27)  /* isso funciona mas não me serve */

should work

Select all

 s.cod_us=d.cod_us

see if a light.

[Trevisolli]

Brother, good morning.

post the mistake here to analyze.

[henrique290]

The error is this one.

Select all

Error at Command Line:2 Column:15 
Error report: 
SQL Error: ORA-00942: table or view does not exist 
00942. 00000 -  "table or view does not exist"

[henrique290]

Sorry, I posted the error incorrect, disregard the above.

Select all

Error at Command Line:25 Column:15 
Error report: 
SQL Error: ORA-00904: nome inválido de coluna 
00904. 00000 -  "%s: invalid identifier"

[Trevisolli]

Brother,

This table below:

Select all

 
sisesgoto.stc_agua_tratada1  

Do you have the COD_US field?

[henrique290]

Trevisolli,
face is the following, I was able to work, not as it should but
replaces the line where it should compare the number of US by these

Select all

 WHERE cod_us between 1 and 500) 
  
 
 ON(s.cod_us between 1 and 500)

99]
As I have 500 code of us, then I'll send all the changes. With the empty table it is easy and fast, by it just do the insert, but the update was time consuming. I'll review it.

Well here finished the development stage, now comes the difficult for me, I think.

I have two Oracle servers, one in IBM and another on Linux in distant places with differing TNS.

IBM

Select all

IBM.WORLD = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = IBM.oracle.CIA.CIA)(PORT = 1524)) 
    (CONNECT_DATA = (SID = IBM) (SERVER=DEDICATED))

Linux

Select all

linux.cia2.cia2 = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = servidorlinux)(PORT = 1521)) 
    ) 
    (CONNECT_DATA = 
      (SERVICE_NAME = linux) 
    ) 
  )

Question ??

How do I do the IBM tables merge to the tables on the serverLinux ??

The STC_AGUA_TRAATED table is in IBM and
the STC_AGUA_TRAATED table1 is on the serverLinux

How do I select the tables in due servers? ?
This here tried and did not work

Select all

ibm.stc_agua_tratada (IBM) 
linux.stc_agua_tratada1(linux)

obs. These machine names are examples (fictitious).

Here I want water !!! hehehehe

[Trevisolli]

Then Brother,

to perform this SELECT, on different bases, a database link) has to be created between the servers.

This should be analyzed with the Responsible DBA in the company, which should create this link to you.

Here in the forum, there are interesting examples of use and creation.

Look here by DBLink that will find interesting topics, such as [url=http://en.glufke.net/oracle/viewtopic.p ... ght=dblink]este

Any questions, send it there.

[henrique290]

Trevisolli,
It's all right with Merge, beauty worked. Just a doubt as to dblink, I just need to create a dblink so for the target server or for the two machines ??


Oracle in IBM I have the tables I want to select, so I must create the dblink for the Linux machine and run the script in Oracle IBM or vice versa or cry in the Linux machine where are the tables destinations a dblink for oracle IBM and squeeze the script on the linux machine ??

[henrique290]

Trevisoli,

Thanks for the tips and I would finalize this topic. I already created the dblink and the merge is worked and scheduled.

ATT
Henrique

[Trevisolli]

Brother, it took me a while because I was in a project ...
It's good that it worked, if you can, detail the steps here, for those who need help.
Thank you very much,