Regularly transferring data from other databases to Oracle

This is the problem we are facing: We want to design a big Oracle 8i database importing data from different smaller databases that are in Oracle, MS SQL Server or another format. The Oracle database will have to be updated regularly from the small databases every month. This will mean that ALL the corresponding data will be deleted and the new data will be imported to the database. The solution we were thinking of was to convert the database in XML (Java's libraries for XML SQL support would help). The problem is, though, that some of the databases will be very big (maybe up to 15GB), so this would mean a huge XML file. Do you know if this is a good way of doing this or is there any better way for transferring big amounts of data regularly from other types of databases to Oracle? Thank you very much in advance for your advice

For that amount of data, I'd look at either being able to pull the info directly from the other databases (check into Oracle's Transparent Gateway features) or writing some interface that would create flat text files from each of the databases you want to consolidate from and then use SQL*Loader or in 9i use external tables to retrieve the data into your Oracle database.

For More Information

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.