Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    58

    Unanswered: table export import

    Hello,

    I have two tables in two seperate schemas. Both are on HP-UX with Oracle 8.1.7 as d/b.Both have equal number of columns. The structure is same but for one column which appears at different positions (as seen in toad). Eg: If first table is A B C D E F, second table is A B D E F C. I am using TOAD.

    I need to import data from A to B. They are on different databases and at different locations. One attempt
    has already been made to transfer data of about a million records. It took 2 days to just transfer 1/2 million records. I don't know how that was made. Could you please address these? Should it be done by SQL*LOADER? Are there any serious issues or concerns?

    Many thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    SQL*Loader ? are you trying to dump data into text file from one DB and loading into another DB ?
    Try exp imp. Drop/disable all indexes and use direct path.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Did you consider creating a DB link to connect those databases, write a procedure that would select data from one DB/table and insert them into another? Because of the large table, rollback segment can't bear such a weight so I guess some kind of a cursor loop (having a commit statement within) could do the work.

    I doubt export/import utility could help as your tables don't have the same description. I tried to reorder columns in a table, but with no success ... Even though I used Oracle Designer (and went down to ER model, generating table into the repository and next into the database), I couldn't rearrange the columns without dropping the table.

    SQL*Loader could do it, I guess ... why not. Perhaps it is the easies solution after all.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    column order (from your standpoint) makes little difference.

    as long as each table has the same column-names then you can
    export DIRECT=Y table 'A' and import into table 'B'. This is assuming you will have no duplicate keys.

    If you expect duplicate keys and want to load ALL the data then disable the PK constraint.

    If you expect duplicate keys and want to avoid loading them, then
    import will filter out the duplicates for you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    May 2003
    Posts
    58
    Hi,
    One quick question. Would Export import just transfer table rows OR drop an already existing table, create a new one? In other words, I do not want the table at target to be dropped. Will export import work?
    Thanks.

  6. #6
    Join Date
    Feb 2004
    Posts
    108
    Import will just dump the rows into the target table. If the constraints are not alive, then you might want to check the data after the import is done.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Read docs regarding export/import.
    The docs normally answer all questions.

    You'll need to refer to the docs in order to specify your parameters so
    give this a good read-through.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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