This content has been marked as final. Show 4 replies
insert /*+ append */ into target (c1, c2, c3) select col1, col2, col3 from source
You should use nologging and parallelism options
Please refer to Tom's answers:
- - - - - - - - - - - - - - - - - - - - -
Kamran Agayev A. (10g OCP)
[Step by Step install Oracle on Linux and Automate the installation using Shell Script |http://kamranagayev.wordpress.com/2009/05/01/step-by-step-installing-oracle-database-10g-release-2-on-linux-centos-and-automate-the-installation-using-linux-shell-script/]
If moving into the same datbase and same schema, your best option if using direct path insert /*+ APPEND */. Beside that I assume you will be using partitioning for target table and you will also have partitioning for source table. You can use compress and nologging option on target table.
You can use datapump and export /import as well to move data but fastest will be using direct path insert
insert /*+APPEND */ into target_part_table (col1, col2, col3) values (val1, val2, val3) where partition_key_column = 'value'; commit;
Another good idea might be to make sure the target table has no indexes (or row insert triggers) when you insert the rows from the source into it.
And after the data has been moved, build the indexes, re-enable the triggers.