• Post Reply Bookmark Topic Watch Topic
  • New Topic

Problem in creating a copy of Oracle data using Access  RSS feed

 
Hussein Baghdadi
clojure forum advocate
Bartender
Posts: 3479
Clojure Mac Objective C
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.
I have on Oracle datasource registered with Microsoft ODBC resources.
Usually, when I want to create a copy of my data, I use MS Access.
(You know, creating a new mdb file then right click on the wizard, choosing Import option then you choose the desired datasource).
Every thing is ok, but recently when I'm trying to connecting to Oracle database, Access is freezing.
(My Oracle database contains about 17K records).
Any help ?
 
Travis Hein
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It could be something with the ODBC adapter access uses to connect to Oracle is doing something like trying to query all rows when it first tries to establish the connection. Does waiting it out ever come back, or does it just go south?

I used to use Oracle's "imp" and "exp" command line import and export tools to backup and migrate data between databases, I remember we had this one table, with 30k records, and it sometimes took a looong time to run, I guesss it would depend on the number of users connected and the activity that was currently going on with this table.

I would assume that would be the fastest way to copy data from Oracle would be to use Oracles no frills exp tool, but I found even with that having a lot of content sometimes just takes a long time.

Aside from using Oracle exp tool instead of access, would you be able to create views in oracle that would segment or partition your data, perhaps a view to only show records by year, or some other time stamp, or by a block of primary keys where id between x and y.
This would reduce the raw number of records from the large row set in one table, at least to see if it helps to then have Access read from these views instead.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!