Oracle trigger to sync remote database
I am trying to keep to tables on to different databases in sync. Every time an insert on the local table I want to insert the same record on the remort table. I do I write the trigger to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE MATERIALIZED VIEW your_materialized_view
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH sysdate+1/1440
NEXT sysdate + 1
WITH PRIMARY KEY
AS
SELECT col1, col2, col3, col4 FROM your_table@remote_db_link
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH sysdate+1/1440
NEXT sysdate + 1
WITH PRIMARY KEY
AS
SELECT col1, col2, col3, col4 FROM your_table@remote_db_link
the above will create a table on your database called "your_materialized_view"
and it will create a dbms_job that runs once per day that pulls 4 columns from a table on a remote db across a db link and puts them into the local table/materialized view.
There are a lot of options to building a materialized view (mv).
You can change the way it refreshes (complete or fast, which means just apply changes) you can change the frequency of the job which refreshes too.
Depending on how you want to use the data, you might be able to get away with a traditional view that queries across the db link for you.
create or replace my_view as
select col1,col2,col3,col4 from my_table@remote_db_link
and it will create a dbms_job that runs once per day that pulls 4 columns from a table on a remote db across a db link and puts them into the local table/materialized view.
There are a lot of options to building a materialized view (mv).
You can change the way it refreshes (complete or fast, which means just apply changes) you can change the frequency of the job which refreshes too.
Depending on how you want to use the data, you might be able to get away with a traditional view that queries across the db link for you.
create or replace my_view as
select col1,col2,col3,col4 from my_table@remote_db_link
Check the Advanced Replication manual for more information
Use Replication option provided by oracle, it can be configured by the following two methods
1) Using Oracle Enterprise Manager
2) writing PL/SQL code
http://www.orafaq.com/faqrepl.htm
http://www.databasejournal.com/features/oracle/article.php/3596971
1) Using Oracle Enterprise Manager
2) writing PL/SQL code
http://www.orafaq.com/faqrepl.htm
http://www.databasejournal.com/features/oracle/article.php/3596971
To perform your required task, you can use oracle streams
See oracle streams demo at the following link
http://www.oracle.com/technology/obe/obe9ir2/obe-intg/streams/streams.htm
See oracle streams demo at the following link
http://www.oracle.com/technology/obe/obe9ir2/obe-intg/streams/streams.htm
ASKER
Thanks
ASKER
Thanks
ASKER