Tuesday, July 8, 2008

LRM-00116: ORA-39001 ORA-39071 ORA-00907 in impdp

Problem Description:
------------------------------

While using special character without escape character causes error like LRM-00116: ORA-39001 ORA-39071 ORA-00907.

-bash-3.00$ expdp dba/dba exclude=table:"in('ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE')"

LRM-00116: syntax error at ')' following 'USA_IMAGE'

-bash-3.00$ expdp dba/dba exclude=table:"in\('ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE'\)"

Export: Release 10.2.0.2.0 - Production on Tuesday, 08 July, 2008 23:54:13

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00907: missing right parenthesis

Cause of The Problem:
------------------------------------------------

double quotes ("), single quotes (') parentheses () are termed as special character on unix system. To know the list of special character on unix please have a look at Special Character on Unix. In order to use specual character on unix you have to use escape characters. How you can use escape character are discussed on How does one escape special characters.
If you don't use escape characters while using special character above error will arise.

Solution of The problem:
---------------------------------

Use escape character before using special character. Like in stead of using
expdp dba/dba exclude=table:"in('ARJU_DATA','INDIA_ACTIVITY','USA_IMAGE')"

Use,
expdp prod/prod exclude=table:\"in\(\'ARJU_DATA\',\'INDIA_ACTIVITY\',\'USA_IMAGE\'\)\"

The details of the exclude and include parameters of datapump are described in DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

No comments:

Post a Comment