data export
A reader, May 27, 2008 - 7:24 pm UTC
Tom:
For a similar situation where we have
server 1 has oracle 9i R2.
server 2 has SQLite.
We need to take some data that in the SQLite and store it in a table on server 1 for reporting purposes.
What is the best way to accomplish this. Both servers can connect over same network.
export
A reader, May 28, 2008 - 5:38 pm UTC
Tom:
Thanks, I will read the chapter on generic connectivity and ODBC drivers.
In terms of ease of development would it be like this:
1. ODBC (generic connectivity)
2. external table
3. sqlldr
I think the XML dump option and parsing it would be "not easy".
database link from Oracle to Sqlite
RC, March 28, 2009 - 11:23 am UTC
March 30, 2009 - 3:56 pm UTC
that link seems to, well, show how to do it?
Source data
David Montgomery, February 03, 2012 - 10:56 am UTC
Hi Tom:
If the data in the source database is truncated and reloaded nightly what impact if any is there when datapump is used to move it from the source to the destination database? does the destination need to be truncated also or will datapump only import the new or changed data?
cheers,
David
February 03, 2012 - 11:50 am UTC
if you truncate and reload into source...
why not just do that into target as well??
why
a) read it and write it into source
b) read it from source and write it again to disk
c) read it and write it into target
when you can just
a) read it and write it into source
b) while at the same time read it and write it into target
??
At the very least - just transport it so you can
a) read it and write it into source
b) copy the data files over to the other database and attach them
I ask myself those questions every day...
David Montgomery, February 06, 2012 - 9:53 am UTC
Thanks for your response.
Essentially we do the last thing you describe using transportable tablespaces coupled with the cloning feature of our underlying storage array. I was hoping to simplify it a little by using data pump exclusively but I doubt it can match the array for speed.
I would like to use your response to lobby for a complete overhaul of the process if I may?
Some clarification
David Montgomery, February 06, 2012 - 10:07 am UTC
Sorry I should have added that we only move a subset of the data from the source database. After the trunc and load there is some ETL performed and the data from this process populates a tablespace for reporting purposes. Only that tablespace is replicated.
February 06, 2012 - 10:54 am UTC
ok, transporting would be the way to go in my opinion, given what I know about your situation. Just move the tables/indexes instead of unloading and reloading them.
You may feel free to share my opinion.