Oracle 12c Datapump import error

Question

I am importing a database (.dmp) file using the 12c Data Pump import wizard. I created a user for the database and remapped the schema and tablespace. I get the following errors:

• ORA-39071: Value for NAME_EXPR is badly formed.
• ORA-01756: quoted string not properly terminated
• ORA-39012: Client detached before the job started.

The statement begins, "dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'IN(''ABSENCE_DURATION'', ... The statement includes a LONG list of tables and the number of characters is 32,659.

I'm thinking that this statement may have exceeded some limit. If that is the case, how would I break this statement into several statements?

Answer 1

You do not need to enumerate each object you want to export/import, you can pass a query that returns the list:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table tables_to_export(table_name varchar(128 char));

Table created.

SQL> insert into tables_to_export values ('T1');

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@o71 ~]$ expdp bp/bp include=table:\"in \(select table_name from tables_to_export\)\"

Export: Release 12.2.0.1.0 - Production on Thu Jun 15 21:18:57 2017

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 "BP"."SYS_EXPORT_SCHEMA_01":  bp/******** include=table:"in (select table_name from tables_to_export)"
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   9.535 MB   76511 rows
Master table "BP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/max/dpdump/expdat.dmp
Job "BP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 15 21:19:19 2017 elapsed 0 00:00:21