Depending on the situation, I can choose between two different methods. The first method is to use SQL*Plus to copy data from one remote table to a local table.
copy from <schema>/<password>@<host>INSERT <new_table>Using select * from <old_table>
Instead of INSERT, you can also use: APPEND, CREATE or REPLACE. For more information about the differences, please follow the link at the end of this post.
When multiple tables and/or triggers are involved, I use the second method. The second method uses the exp/imp tools provided by Oracle. First, an export file is created using exp. In this example, I migrate only two tables (table1 and tabl2). You can add more tables if you want.
exp <schema>/<password>@<host> file=export.dmp log=export.log tables=(table1, table2) rows=yes indexes=no
Finally, we load the export file into the new database. All the new tables and triggers are created automatically. We use imp to do the import.
imp <schema>/<password>@<host> file=export.dmp full=yes
Make sure the exp/imp tools are the same version as the database, or it won't work.
More info can be found here:
No comments:
Post a Comment