vicker313 tech blog

January 7, 2010

Migrate Data using Transportable Tablespace in Oracle

Filed under: Oracle — Tags: , — vicker313 @ 8:00 am

There are few types of data migration methods: offline migration, migrate using exp and imp utilities, migrate using expdp and impdp utilities and of course using transportable tablespace. You can refer Oracle Database Cross Platform Transportable Tablespace for more details.

To perform migration using transportable tablespace:

  1. At source database, make the tablespace readonly
    alter tablespace users read only;
  2. Export it as SYSDBA
    exp file=exp.dmp log=exp.log tablespaces=users transport_tablespace=y
  3. Copy over the dump file and the tablespace data files to the destination
  4. At source database, put the tablespace back to read write mode
    alter tablespace users read write;
  5. If the same tablespace exists in the destination database, make  it offline and drop it.
    drop tablespace users including contents and datafiles;
  6. Lastly import the copied dump file:
    imp file=exp.dmp log=exp.log tablespaces=users transport_tablespace=y datafiles=('/u01/users01.dbf', '/u01/users02.dbf')

If there is error such as “Package DBMS_PLUGTS not exist”, try:

  1. Execute this SQL file to create the package in SYS user: {ORACLE_HOME}/rdbms/admin/dbmsplts.sql
  2. Log into the user you want to import the data, create a synonym to the package
    create synonym dbms_plugts for sys.dbms_plugts;
  3. Re-run the import command
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: