Oracle SQL Auto-updating Table From a Different Database

Question

I have two tables from different database, db1.names and db2.names_rep. db1.names is constantly getting new data and I need it to reflect in db2.names_rep.

I've created a dblink connecting for db1 in db2 named dblink_db1.

CREATE DATABASE LINK dblink_db1
CONNECT TO user IDENTIFIED BY pass
  USING '(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
  (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SID)))';

and a trigger that calls the db1.names table

create or replace trigger "names_trigger"
    after insert or update or delete on "names@dblink_db1"
for each row
begin
    if inserting then
          insert into "names_rep" (name_id, student_names)
          values (:NEW.name_id, :NEW.student_names);
    elsif updating then
          update "names_rep"
          set name_id=:NEW.name_id, student_names=:NEW.student_names
          where name_id=:NEW.name_id;
    elsif deleting then
          delete from "names_rep"
          where name_id=:OLD.name_id;
    end if;
end;

The dblink is working as i can invoke this query successfully in db2

select * from names@dblink_db1

I'm receiving an error that says this

Error report: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action:

Lastly, I've read about Oracle Streams. Is that an alternative to Triggers? I'm hoping I don't have to specify each operation as to what is done in Trigger

Oracle GoldenGate is probably what you'd want to investigate, if there are multiple tables/schemas to be replicated. If it's just this one single table, I'd stick with triggers; far more lightweight and less costly. Regarding your error, have you tried granting insert/update/delete/select privileges on the remote db to the user specified in the database link in the local db? — Boneist
Sound likes you may be re-inventing the wheel. Oracle has Advanced Replication which will do this for you. See this docs.oracle.com/cd/E18283_01/server.112/e10706/repoverview.htm. Streams mines redo logs, and will be something much more DBA dependant than PLSQL based Advanced Replication. — TenG

WW. WW. · Accepted Answer · 2019-04-18T06:57:54

You are trying to create a trigger in database db2 on a table in db1. I'm not sure that's possible.

I think the trigger needs to be created in db1, and to insert/update/delete rows over a link to db2.

Oracle SQL Auto-updating Table From a Different Database

1 Answers