Home » RDBMS Server » Server Utilities » DataPump errors on EXCLUDE table syntax (11.2.0.3.0 , RHL 6.2)
DataPump errors on EXCLUDE table syntax [message #668533] Wed, 28 February 2018 10:47 Go to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I do as stated in the documentation:


https://docs.oracle.com/cloud/latest/db112/SUTIL/dp_export.htm#SUTIL884

section "Data-Only Unload of Selected Tables and Rows"

But it does not work for some reason.... does anyone know where did I go wrong with this ?

Thanks in advance...her'es my code:



SQL> create user a identified by a;

User created.

SQL> grant dba to a;

Grant succeeded.

SQL> create table a.a as select * from dba_objects;

Table created.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@my_machine mydb]$ expdp system schemas=a directory=DATA_PUMP_DIR dumpfile=a.dmp content=DATA_ONLY EXCLUDE=TABLE:"IN ('A')"

Export: Release 11.2.0.3.0 - Production on Wed Feb 28 18:36:24 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00936: missing expression

Re: DataPump errors on EXCLUDE table syntax [message #668535 is a reply to message #668533] Wed, 28 February 2018 10:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to properly escape the quotes etc.
I would rather just use the parfile option.


Edit:
The URL you referred uses a parfile.
 
oracle@kapi#showTables  xxx dbadmin

OWNER			       TABLESPACE_NAME				TABLE_NAME
------------------------------ ---------------------------------------- ------------------------------
DBADMIN 		       USERS					A
DBADMIN 		       USERS					DEPT
DBADMIN 		       USERS					DFINSERT
DBADMIN 		       USERS					EMP
DBADMIN 		       USERS					SYS_EXPORT_FULL_01

oracle@kapi#cat parfile.par
dumpfile=a.dmp  content=DATA_ONLY EXCLUDE=TABLE:"IN ('A')"
oracle@kapi#expdp dbadmin/xxxxx parfile=parfile.par

Export: Release 12.2.0.1.0 - Production on Wed Feb 28 11:56:53 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DBADMIN"."SYS_EXPORT_SCHEMA_01":  dbadmin/******** parfile=parfile.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . exported "DBADMIN"."EMP"                             8.742 KB      14 rows
. . exported "DBADMIN"."DFINSERT"                        7.289 KB       3 rows
. . exported "DBADMIN"."DEPT"                            6.031 KB       4 rows
. . exported "DBADMIN"."SYS_EXPORT_FULL_01"                  0 KB       0 rows
Master table "DBADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBADMIN.SYS_EXPORT_SCHEMA_01 is:
  /u01/base/admin/rmandb/dpdump/a.dmp
Job "DBADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 28 11:56:59 2018 elapsed 0 00:00:05

[Updated on: Wed, 28 February 2018 11:02]

Report message to a moderator

Re: DataPump errors on EXCLUDE table syntax [message #668536 is a reply to message #668535] Wed, 28 February 2018 10:59 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

How can I know what is proper and what is not ?

I didn't find a place that shows it correctly...
Re: DataPump errors on EXCLUDE table syntax [message #668537 is a reply to message #668536] Wed, 28 February 2018 11:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>How can I know what is proper and what is not ?
It is an OS thing. Oracle has nothing to do with it.

oracle@kapi# hostnamectl  | grep Oper
  Operating System: Red Hat Enterprise Linux Server 7.2 (Maipo)
For above OS, this worked.
Using escape
oracle@kapi#expdp dbadmin/xxxxx directory=expdpdir dumpfile=a.dmp  content=DATA_ONLY EXCLUDE=TABLE:\"IN \(\'A\'\)\"

Export: Release 12.2.0.1.0 - Production on Wed Feb 28 12:04:18 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DBADMIN"."SYS_EXPORT_SCHEMA_01":  dbadmin/******** directory=expdpdir dumpfile=a.dmp content=DATA_ONLY EXCLUDE=TABLE:"IN ('A')"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . exported "DBADMIN"."EMP"                             8.742 KB      14 rows
. . exported "DBADMIN"."DFINSERT"                        7.289 KB       3 rows
. . exported "DBADMIN"."DEPT"                            6.031 KB       4 rows
. . exported "DBADMIN"."SYS_EXPORT_FULL_01"                  0 KB       0 rows
Master table "DBADMIN"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DBADMIN.SYS_EXPORT_SCHEMA_01 is:
  /oraBackup/dumps/a.dmp
Job "DBADMIN"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Feb 28 12:04:22 2018 elapsed 0 00:00:04

[Updated on: Wed, 28 February 2018 11:12]

Report message to a moderator

Re: DataPump errors on EXCLUDE table syntax [message #668538 is a reply to message #668537] Wed, 28 February 2018 11:17 Go to previous messageGo to next message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

Thank you for the assistance, Mahesh.

However, I would expect Oracle to say something about it, as this surely happens to many DBA's and deserves some explanation...
Re: DataPump errors on EXCLUDE table syntax [message #668539 is a reply to message #668538] Wed, 28 February 2018 11:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10690
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It does.
https://docs.oracle.com/cloud/latest/db112/SUTIL/dp_export.htm#BEHJHGHB

As said, Oracle has nothing to do with this.
As it is possible to customize the escape characters for each OS, it is not possible to document all.
Using the parfile option is the alternative.

[Updated on: Wed, 28 February 2018 11:25]

Report message to a moderator

Re: DataPump errors on EXCLUDE table syntax [message #668541 is a reply to message #668539] Wed, 28 February 2018 11:43 Go to previous message
Andrey_R
Messages: 309
Registered: January 2012
Location: Israel
Senior Member

My bad, you are right Smile

I guess I should consider working with parfile.

Thanks.
Previous Topic: ORA-00984: column not allowed here. date. sql loader.
Next Topic: Export/Import Unused column
Goto Forum:
  


Current Time: Mon Aug 19 20:50:30 CDT 2019