Problem solve Get help with specific problems with your technologies, process and projects.

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

This was last published in November 2002

Dig Deeper on Oracle database export, import and migration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.