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

Method other than DB link for large data transfer

We are working on the development of a datamart (in 9i) which takes data from two source systems. Since this is a transaction system, there is a lot of data. Currently we are doing incremental loads using a spool file and external tables. But the spool file size is huge and takes a long time and a lot of space on the server. Are there any other ways we can do this large data transfer?

We are working on the development of a datamart (in 9i) which takes data from two source systems (one 9i and one 8i). We need to do a full load of data. Since this is a transaction system, there is a lot of data. Currently we are doing incremental loads using a spool file and external tables. But due to so much data to be taken care of in full loads, the spool file size is huge and it will take a long time to do spooling, as well as taking a lot of space on the server. Copy is not allowed, nor is a DB link as it is deprecated in 9i. Are there any other ways we can do this large data transfer? This activity is supposed to be carried on every week. Let me know your views.

The challenge of transmitting large volumes of data to a warehouse is always one that must be carefully considered....

Your options are limited in some ways since you feel that creating flat files is not an option, and based on your description, this is probably a good idea. You also mention a DB Link -- this is another nice option, but again not an option for you. So what else do we have? My suggestion would be transportable tablespaces (TT). The concept behind TT is that you can do a VERY quick export of the metadata and then you can copy the export file and the data files from your source system and then attach them to your database. Another option would be to export and import data; you could pipe the export and improve your performance. You could also create a standby database, but you would still have to transmit data to the warehouse.

I hope this gives you some food for thought.

This was last published in May 2006

Dig Deeper on Oracle MDM and metadata

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close