Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    7

    Unanswered: Transfer large data from Sybase to Oracle

    Hi,

    I need to transfer data from 12 tables in Sybase database to 12 tables in Oracle 9i database.
    The volume of data is huge (millions).
    Also, the data transfer should be controlled based on some SQL queries i.e., I do not want to transfer entire data from the tables, I'll issue some SQL queries, and the data obtained through those queries should only be transferred.
    And in case there is a break in between, I should be able to report the point of break, and be able to continue from the last stopped point.

    What is the best way to achieve this?
    I'm currently writing a Java + JDBC program to do this, but I'm worried about the performance, reliability etc.

    Can I get some suggestions for this?

    Thanks,
    Sriram

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    How often will you export data? Do you need full automated process?

    1) create temp table on Sybase which will be populated using Select statement (here you can limit records which shouldn't be exported)

    2) export this table to CVS file. I'm not familiar with Sybase, but I believe there is some tool which allowed do that. If the tool is powerfull you can skip step 1 and limit records here.

    3) import CVS file into Oracle using SQL Loader

    If there's a tool in Sybase which can be called from OS command line you'll be able create this as a full automated process (some shell script or batch file). I think it's better solution than Java coding.
    Last edited by madafaka; 11-11-05 at 08:07.

  3. #3
    Join Date
    Nov 2005
    Posts
    7
    Thanks for the reply.

    There are few limitations.

    1. I can't create any temp tables on Sybase as I have only read permissions on that db.
    2. The process is to be done daily. Also, the user is non-technical and we need to provide a GUI to the user to perform this job. That is the reason why I've started off with Java + JDBC.

    So, in this scenario, what would be the better option?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I think that GUI shouldn't be primary goal - it can be done, as Madafaka told you, using a batch file or something like that, and the user doesn't have to know anything about the process. 'Click here' should be enough.

    But behind 'Click here' button are two or three steps; just to mention them again, as I completely agree with Madafaka's suggestion:

    - create an ASCII text file using SELECT statements in Sybase. Although you are not allowed to write into this database, you should be able to write to the hard disk.
    - if Oracle database is elsewhere, FTP this file to another loaction. If it is on the same machine, you won't have to do that.
    - use SQL*Loader and import data into the Oracle database

    P.S. What is the meaning of the "CVS" file? I searched the Acronym finder, but didn't see anything suitable. I'd just say that CVS - in "our" context - isn't "Cyclic Vomiting Syndrome" (although sometimes it might be)

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    my mistake. it should be CSV (Comma Separated Values) not CVS.
    BTW for CVS I found it's Graphics (Canvas) file extension.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •