| DataPump errors on EXCLUDE table syntax [message #668533] |
Wed, 28 February 2018 10:47  |
Andrey_R
Messages: 441
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  |
 |
Mahesh Rajendran
Messages: 10708
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 #668537 is a reply to message #668536] |
Wed, 28 February 2018 11:08 |
|
Mahesh Rajendran
Messages: 10708
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)
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 |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
