Monday, March 22, 2010

Migrating Oracle database content

There are many times that I have to migrate data from one Oracle database to another.

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