Copy a Remote Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Copy a Remote Table

  1. #1
    Join Date
    Feb 2002
    Posts
    18
    Hi,
    What would be the fastest/better way to copy a table from a remote DB?


    Remote table has 8,000,000 records
    O/S Windows NT
    From Oracle8i to Oracle 7 (Release 7.3.4)

  2. #2
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    create a db link and use the

    create table as select from ..... command
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  3. #3
    Join Date
    Feb 2002
    Posts
    18
    SQLPLUS copy is a good idea?
    In this case I can break the transaction into smaller entries.
    But I am not sure,the amount of data is big.

    Thanks

  4. #4
    Join Date
    Feb 2002
    Posts
    18
    That was my first thought but if I use :
    create my_table
    unrecoverable
    as
    select * from table@remote;

    I will have a big transaction ,the commit will take place after all the 8,000,000 have been inserted.
    Or I might be wrong?!!

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by monna
    That was my first thought but if I use :
    create my_table
    unrecoverable
    as
    select * from table@remote;

    I will have a big transaction ,the commit will take place after all the 8,000,000 have been inserted.
    Or I might be wrong?!!

    You dont have to worry about commit/rollback because its a DDL.

    No rollback will be generated for this.

    Hope this helps
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by ronnie
    You dont have to worry about commit/rollback because its a DDL.

    No rollback will be generated for this.

    Hope this helps [/B]
    Big temp is requiered for this operation. I think in your case is not the best option. Use copy, or just export the table in origin and import it in destiny with ignore=yes, constraints=no, indexes=no.

    Cheers

    Angel

  7. #7
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by aarroyob
    Originally posted by ronnie
    You dont have to worry about commit/rollback because its a DDL.

    No rollback will be generated for this.

    Hope this helps
    Big temp is requiered for this operation. I think in your case is not the best option. Use copy, or just export the table in origin and import it in destiny with ignore=yes, constraints=no, indexes=no.

    Cheers

    Angel [/B]

    You are right about temp angel. I should have mentioned that.

    And yes export/import can be another way to do it.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  8. #8
    Join Date
    Feb 2002
    Posts
    18
    Please do not take me wrong, I do not want to discover that the earth is round but is something that is missing.

    You say that when:
    create table as select * from table@remote has nothing to do with the rollback seg.
    I think there is an “insert “ involved in this create table so there is a transaction.
    How the data is “moved” into a new table?

    thanks

  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by monna
    Please do not take me wrong, I do not want to discover that the earth is round but is something that is missing.

    You say that when:
    create table as select * from table@remote has nothing to do with the rollback seg.
    I think there is an “insert “ involved in this create table so there is a transaction.
    How the data is “moved” into a new table?

    thanks

    I am not aware as to how this happens internally but you can do a small test

    use the create table as select * from ... command and then issue a rollback . You will notice that the data is still there in the table which got created. Its not rolled back. This shows that there is no before image of the data.



    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Mar 2001
    Posts
    144
    You're best bet is doing a sqlplus copy command with the commit parameter.

    All three options, import/export, create table as select, and sqlplus copy, will be slow with such a big table. But with the copy command set with the commit parameter at least you will not waste all of the time in the copying of the data if something happens.

Posting Permissions

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


Click Here to Expand Forum to Full Width