We help IT Professionals succeed at work.

Oracle trigger to sync remote database

2,816 Views
Last Modified: 2013-12-26
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?
Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
How do I use materialized view ?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
Check the Advanced Replication manual for more information

Commented:
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 

Commented:
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

Author

Commented:
Thanks

Author

Commented:
Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.