Overview of an interesting bug

 Often you may need to load data to the database under a different schema and different tablespace.

With Oracle Data Pump all this is possible in a very simple way. Among the various options of the routine impdp you can find:

REMAP_SCHEMA

REMAP_TABLESPACE

REMAP_SCHEMA allows us to load the exported objects under a different scheme;

REMAP_TABLESPACE allows us to load the exported objects under a different tablespace.

Obviously, options can be used separately.

For example if we have the table ADMIN.TAB1 on tablespace ADMIN_TBS (which is not the default tablespace of schema ADMIN):

SQL> select owner, segment_name, tablespace_name from dba_segments where segment_name=’TAB1′

OWNER        SEGMENT_NAME       TABLESPACE_NAME

———— —————— ——————————

ADMIN        TAB1               ADMIN_TBS

We export ADMIN schema (which for convenience we imagine contain only the table TAB1) using a parfile like:

DIRECTORY=MYDIR

DUMPFILE=expdp_ADMIN.dmp

LOGFILE=expdp_ADMIN.log

SCHEMAS=ADMIN

At this point you can make imports (even on the same database) making ADMIN recreate the objects under the schema TEST and tablespace TEST_TBS.

The import can be done with a parfile like:

DIRECTORY=MYDIR

DUMPFILE=expdp_ADMIN.dmp

LOGFILE=impdp_ADMIN.log

REMAP_SCHEMA=ADMIN:TEST

REMAP_TABLESPACE=ADMIN_TBS:TEST_TBS

In a situation like this happened to me that the recreation of the objects to be successful, but that the procedure remains in hang.

All this is related to bug 6807289 documented in the Oracle metalink note “Bug 6807289: IMPDP REMAP_SCHEMA WITH TABLE AND STATISTICS AT REMAP_TABLESPACE HANGS”

The bug is present from version 10.2.0.3 has not yet been fixed.

As a workaround you need to add in the import’s parameters:

EXCLUDE=STATISTICS

and make the statistics manually after completing the import.

In this way is the import ends and the final result in the case of an import on the same database is as follows:

SQL> select owner, segment_name, tablespace_name from dba_segments where segment_name=’TAB1′

OWNER        SEGMENT_NAME       TABLESPACE_NAME

———— —————— ——————————

ADMIN        TAB1               ADMIN_TBS

TEST         TAB1               TEST_TBS