Tuesday, April 27, 2010

QUERY and SCHEMAS Data Pump Conflict

Stumbled across this over the weekend. Runing impd with a QUERY clause that appeared to be too complex threw this error:

UDI-00014: invalid value for parameter, 'schemas'

The syntax looked correct:

$ impdp 'scott/tiger' DIRECTORY=DATA_PUMP_DIR \
NETWORK_LINK=remote_db \
TABLE_EXISTS_ACTION=APPEND \
CONTENT=DATA_ONLY \
TABLES=SCOTT.EMP \
'QUERY="WHERE id IN (SELECT id FROM (SELECT id, ROWNUM rnum FROM scott.emp ORDER BY id ASC) WHERE rnum >= 85000000 AND rnum < 170000000)"'

When trying to set up a test case on my local environment I accidentally stumbled on the error with only the subquery:

impdp andy/andy DIRECTORY=DATA_PUMP_DIR DUMPFILE=
andy.dmp TABLE_EXISTS_ACTION=truncate CONTENT=DATA_ONLY QUERY=\"WHERE id IN (SELECT ID from ANDY.EMP where ID < 50)\"

Still threw:

UDI-00014: invalid value for parameter, 'schemas'

I resolved this by removing the ANDY schema qualifier.

impdp andy/andy DIRECTORY=DATA_PUMP_DIR DUMPFILE=
andy.dmp TABLE_EXISTS_ACTION=truncate CONTENT=DATA_ONLY QUERY=\"WHERE id IN (SELECT ID from EMP where ID < 50)\"

So, I learned that if a table is being referenced and resides outside of the schema running the impdp session you can resolve this by creating a synonym.