1

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?

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
  • Thanks. I have a sql file that was generated by the impdp command. This file creates a user ("sample"), grants it permissions and then uses it to create tables and sequences, etc. "sample"."table". Can I do a find-replace in the .sql file to make the user a local user, such as "C##sample" or is there a different approach? – John Faig Nov 6 '17 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.