Thursday, December 4, 2008

Import fails with ORA-39005, ORA-31600: invalid input value NULL for parameter

Problem Description
I used both remap_schema(import operation will be performed in another user than the user whose data to be imported) and remap_tablespace(tablespace to be changed while importing) and the import operation failed with ORA-39005 and ORA-31600.
$host impdp directory=d dumpfile=user1_metadata04-12-08.dmp remap_schema=user1:user2 table_exists_action=skip logfile=user1_metadata04-12-08.log \
remap_tablespace=user1_SPC:user2_SPC \
remap_tablespace=user1_HISTORY_DETAIL_SPC: user2_HISTORY_DETAIL_SPC \
remap_tablespace=user1_HISTORY_SPC:user2_HISTORY_SPC \
remap_tablespace=user1_ACC_SPC:user2_SPC \
remap_tablespace=user1_HISTORY_DETAIL_INDX_SPC:user2_HIS_DETAIL_INDX_SPC \
remap_tablespace=user1_HISTORY_INDX_SPC:user2_HISTORY_INDX_SPC

Import: Release 10.2.0.1.0 - 64bit Production on Thursday, 04 December, 2008 19:17:08

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39005: inconsistent arguments
ORA-31600: invalid input value NULL for parameter VALUE in function DBMS_DATAPUMP.METADATA_REMAP

Cause of the Problem
A NULL or invalid value was supplied for the parameter. Here a NULL value was supplied for the parameter remap_tablespace. The new_value was considered as NULL because of space after colon.

There was a space in the line user1_HISTORY_DETAIL_SPC: user2_HISTORY_DETAIL_SPC.
The space is just after colon and hence error comes.

Solution of the Problem
Ensure that parameters are not supplied as value NULL. Rewrite the above script as below will solve the problem.

impdp directory=d dumpfile=user1_metadata04-12-08.dmp remap_schema=user1:user2 table_exists_action=skip logfile=user1_metadata04-12-08.log \
remap_tablespace=user1_SPC:user2_SPC \
remap_tablespace=user1_HISTORY_DETAIL_SPC:user2_HISTORY_DETAIL_SPC \
remap_tablespace=user1_HISTORY_SPC:user2_HISTORY_SPC \
remap_tablespace=user1_ACC_SPC:user2_SPC \
remap_tablespace=user1_HISTORY_DETAIL_INDX_SPC:user2_HIS_DETAIL_INDX_SPC \
remap_tablespace=user1_HISTORY_INDX_SPC:user2_HISTORY_INDX_SPC

No comments:

Post a Comment