Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

white space in parm files foils query for dp export

stevepenceAug 7 2009 — edited Aug 7 2009

Hi,

The data pump QUERY option does very strange things with extra white space in parameter files.

If I have any space or a tab between the table name and the colon e.g.
QUERY=UTL_DATA_SYS.LOG_SYSTEM_ERROR :"WHERE 1=2"
I get the following error:

ORA-39001: invalid argument value
ORA-31658: specifying a schema name requires a table name

If I put the white space after the colon, it applies the filter to EVERY TABLE !!!! e.g.

QUERY=UTL_DATA_SYS.LOG_SYSTEM_ERROR: "WHERE 1=2"

. . exported "UTL_DATA_SYS"."SSO_AUDIT_LOG_ARCHIVE" 8.640 KB 0 rows
. . exported "UTL_DATA_SYS"."LOG_SYSTEM_ERROR" 8.921 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_META_DATA" 7.492 KB 0 rows
. . exported "UTL_DATA_SYS"."SSO_AUDIT_LOG" 8.632 KB 0 rows
. . exported "UTL_DATA_SYS"."LOG_APEX_ACTIVITY_ARCHIVE" 11.92 KB 0 rows
. . exported "UTL_DATA_SYS"."LOG_APEX_ACTIVITY_PG_DAY_SUM" 18.06 KB 0 rows
. . exported "UTL_DATA_SYS"."LOG_APEX_ACTIVITY_USR_WK_SUM" 9.054 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_IR_COLUMN" 6.515 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_ITEM_USE" 7.273 KB 0 rows
. . exported "UTL_DATA_SYS"."ALL_WBT_TABLES" 9.617 KB 0 rows
. . exported "UTL_DATA_SYS"."ALL_WBT_USERS" 5.25 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_META_DATA_KEYWORD" 5.257 KB 0 rows
. . exported "UTL_DATA_SYS"."APPLICATION_PARAMETER" 6.625 KB 0 rows
. . exported "UTL_DATA_SYS"."A_APPLICATION_PARAMETER" 7.929 KB 0 rows
. . exported "UTL_DATA_SYS"."STANDARD_ABBREVIATION" 5.242 KB 0 rows
. . exported "UTL_DATA_SYS"."SYSTEM_LOG_LEVEL_DEF" 5.914 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_APPL_DETAIL" 0 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_OBJECT_REL" 0 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_WBT_INVALID_REFER" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded

If I get everything JUST RIGHT e.g.
QUERY=UTL_DATA_SYS.LOG_SYSTEM_ERROR:"WHERE 1=2"

I DO get the desired result

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "UTL_DATA_SYS"."SSO_AUDIT_LOG_ARCHIVE" 402.1 MB 5121006 rows
. . exported "UTL_DATA_SYS"."LOG_SYSTEM_ERROR" 8.921 KB 0 rows
. . exported "UTL_DATA_SYS"."APEX_META_DATA" 188.5 MB 1472587 rows
. . exported "UTL_DATA_SYS"."SSO_AUDIT_LOG" 82.19 MB 895951 rows
. . exported "UTL_DATA_SYS"."LOG_APEX_ACTIVITY_ARCHIVE" 28.49 MB 177253 rows
etc

Is it unreasonable to ask Oracle to trim white space before it processes these commands? I really appreciate all the power of the data pump, but this is one area that some added work would be appreciated to reduce fragility and avoid unexpected behavior.

Thanks
Steve