Oracle 12c Datapump import error

datapumpimportoracle-12c

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?

Best Answer

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

Related Solutions

Oracle Datapump: modify fully qualified objects when cloning a schema

if you grant privileges (as you mention above), the problem may seem to go away, but your code executing in SCHEMA_B may be accessing data from tables in SCHEMA_A, unless that is what you want.

Unfortunately there are no easy answers to this problem, since remap_schema option of datapump doesn't really support what you are trying to do. However you may try following option (I have done it before),

Extract all plsql code and/or view definition (datapump can do this for you using impdp with sqlfile option),
use sed or any other tool of your choice and replace SCHEMA_A. to SCHEMA_B. where you see objects prefixed. Be careful, occasionally unwanted lines might get changed, so you will have to track that and implement workarounds.
impdp tables,indexes,constraints to SCHEMA_B
using sqlplus connect to SCHEMA_B and run the modified scripts from step 2 to load all plsql code, create views and other dependent objects etc.

It is cumbersome, but short of fixing your source schema, I don't think you have too many choices. Luckily if you are in a situation where code doesn't change that often, you can hold on to converted files and avoid these manual steps until something breaks.

Oh btw, if your pl/sql code looks like "create or replace schema_a.package_name", bad luck, remap_schema won't support that either (until 11.2, I have not tested in 12c yet).

Using Oracle Datapump when target table has additional column

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value. So something like this;

The following syntax should work on 11g

impdp user/password directory=import_directory dumpfile=data.dmp  REMAP_TABLE=schema.source_table_name:staging_table_name

Then, simply do something like this;

INSERT INTO target_table (column1, column2, column3, column4, column5)
SELECT column1, column2, column3, 'New Data', 'New Data2'
FROM staging_table_name

After which you can drop the staging table.

Best Answer

Related Solutions

Oracle Datapump: modify fully qualified objects when cloning a schema

Using Oracle Datapump when target table has additional column

Related Question