Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: Fastest way to copy data?

    What's the fastest way to copy data from one table to another.
    (possibly in another database).

    Would the answer differ if only a subset of data is to be copied?
    Two methods I am aware of (for copying data) are

    - COPY command
    - Create a link and use SELECT/INSERT directly.
    - ???

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Posts
    4
    CREATE TABLE TMP_DETAIL_STRUCTURE_DONNEE AS SELECT * FROM DETAIL_STRUCTURE_DONNEE;

    copy a table into another one

    Else you have imp and exp oracle utility

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    CREATE TABLE xxx AS SELECT COLUMN1,COLUMN2 FROM xxxx@dblink
    can be a quick method, depending on the amount of data you are bringing across the line (and the speed of you line). CREATE TABLE xxx as SELECT is a NOLOGGING transaction (will not write to redos, so it is realitively quick....

    Export/import is another option ... on the database that you are importing into (if you can !!!) drop the indexes on the table to be imported and rebuild them after the data has been imported... Have a LARGE rollback segment ONLINE and take all other rollback segments OFFLINE (to ensure you get hold of the LARGE rollback)

    I guess the answer depends on the size of the data, the speed of your network and the control you have over the administration of the database ...

    HTH
    Gregg

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    use the /*+ APPEND */ hint when inserting and possibly change the table
    you are inserting into to NOLOGGING.
    - 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
  •