If your database is distributed across a few servers, and you need to access the database across these servers, one way to improve performance is through the use of database links.
Here is a PL/SQL program that inserts records into a number of tables on one server from multiple different servers. Without using database links, this took hours to run the job -- now it only takes less than 5 minutes!
insert into table_a select * from remote_site1_table_a@db_link_site1.world; insert into table_b select * from remote_site1_table_b@db_link_site1.world; insert into table_a select * from remote_site2_table_a@db_link_site2.world; insert into table_b select * from remote_site2_table_b@db_link_site2.world;
For More Information
- What do you think about this tip? E-mail the Edtior at
- firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2001