Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Fast exports - imports

    I'm using oracle 9i and am new(er) to Oracle.
    I've been wrestling with a method to export and import table data so that it takes place quickly.
    Currently I have two oracle servers, I wish to populate the data in Oracle2, Table2 with the data contained in Oracle1, Table1

    I utilize standard TOAD and their export creates one insert into statement for each row - in this case a little over 5800. This, as you can imagine , takes a long time to import.

    I could use ODBC and extract it into Access and then copy the table back, but Oracle2 server doesn't have Access.

    I know there has to be a relatively fast way to do this (or I just have a new million dollar idea and can retire soon).

    Anyone?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I wish to populate the data in Oracle2, Table2 with the data contained in Oracle1, Table1

    sqlplus scott/tiger@oracle1
    insert into table2@oracle2 (select * from table1);
    exit
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Ah but here's the rub...

    They are on separate machines.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >They are on separate machines.
    It appears you have no idea what SQL*Net is or does.
    Go to http://tahiti.oracle.com & RTFM called "Concepts Manual".
    YOYO!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    what a friendly comment.
    that narrows it down a bit...
    <sarcasm off>

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can't make them think.
    How sad it is.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    some people are helpful, others just think they know it all.
    it is sad.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    look up dblink.
    you can create a dblink between the boxes/databases through sql*net
    very handy
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks for the help...
    Much appreciated.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If dblink is not an option (maybe you have not control over the environment), you have three other options:

    Export using TOAD (or any other tool) into a plain text file (tab-separated). Then use SQL*Loader to import the file. SQL*Loader is exteremly fast. Importing 5800 rows probably takes less then 10 seconds (!)

    Export using exp (using the tables parameter to limit it to the desired table) then use imp to import that file into the target database. Not as fast as SQL*Loader but still faster then individual INSERT's

    Use the COPY command from SQL*Plus. Probably the easiest one because no intermediate files are required.
    Details can be found here:
    http://download-west.oracle.com/docs...a90842/apb.htm

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > If dblink is not an option
    [...snip...]
    > Use the COPY command from SQL*Plus.
    Exactly how does COPY succeed without using a dbink between 2 machince?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    > Exactly how does COPY succeed without using a dbink between 2 machince?
    As far as I remember you don't need a DBLink between the two servers, because SQL*Plus (or the COPY) command will simply connect to both machines and then copy the data over. So the data flows from server1 to the client (= SQL*Plus) and then to server2 (whereas with a DBLink this would be transferred directly from server1 to server2)

    "With the COPY command, you can copy data between databases in the following ways: [...]
    - Copy data from one remote database to another remote database

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    still using sql*net
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Of cours it is using SQL*net. I never said it won't. I said it won't need a DBLink which is something different:

    http://download-west.oracle.com/docs...6a.htm#2061507:

    "A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle system"

    With a DBLink jer99 could run the the following command while beeing connected to the Oracle2 instance:
    Code:
    INSERT INTO table2 (col1, col2, col3) 
    SELECT col1, col2, col3
    FROM  table1@oracle1
    or the other way round (connected to Oracle1):
    Code:
    INSERT INTO table2@Oracle2 (col1, col2, col3)
    SELECT col1, col2, col3
    FROM table1
    But setting up a DBLink ist not really easy and it requires administrator privilege on at least one of the servers.

    The COPY command does not require any additional setup other then the "normal" tnsnames.ora stuff which is needed anyway to connect using SQL*Plus, nor any additional privileges. Nor do the EXP/IMP or SQL*Loader solutions required any additional configuration on either server.

Posting Permissions

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