Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gyan Bahadur.

Asked: March 15, 2017 - 8:48 am UTC

Last updated: March 15, 2017 - 8:55 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

I have export the schema for testing as:

expdp test/test directory=test_dir dumpfile=tbltest1.dmp logfile=tbltest1.log schemas=hr
done
now i'm trying to import just only specified rows from the dump file as

impdp test/test directory=test_dir dumpfile=tabtest1.dmp logfile=tabtest1.log tables=hr.jobs content=data_only remap_schema=hr:test query=hr.jobs:"where job_id='HR_REP'"
impdp does not work and error: unknown parameter job_id, but from desc hr.jobs show the job_id column, how to solve this issue??

Regards,
Gyan

and Connor said...

The most common reason for that is the way command line options are parsed. You often need to escape quotes etc.

Put the options in a parameter file is my advice.

C:\Users\hamcdc>expdp mcdonac/****** directory=tmp dumpfile=scott logfile=scott schemas=scott

Export: Release 12.1.0.2.0 - Production on Wed Mar 15 16:50:49 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MCDONAC"."SYS_EXPORT_SCHEMA_01":  mcdonac/******** directory=tmp dumpfile=scott logfile=scott schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "SCOTT"."DEPT"                              6.023 KB       4 rows
. . exported "SCOTT"."EMP"                               8.773 KB      14 rows
. . exported "SCOTT"."EMP$"                              8.789 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.953 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "MCDONAC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_SCHEMA_01 is:
  C:\TMP\SCOTT.DMP
Job "MCDONAC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 15 16:51:14 2017 elapsed 0 00:00:25


and then the import of the just the 1 row in 1 table

C:\Users\hamcdc>impdp mcdonac/******* parfile=c:\tmp\x.par

Import: Release 12.1.0.2.0 - Production on Wed Mar 15 16:54:12 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  mcdonac/******** parfile=c:\tmp\x.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."EMP"                                  8.773 KB       1 out of 14 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"SH"."FK_DEPTNO" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SH"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SH"."DEPT" ("DEPTNO") ENABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Mar 15 16:54:24 2017 elapsed 0 00:00:12


and c:\tmp\x.par was pretty much as per yours

directory=tmp 
dumpfile=scott 
logfile=scott 
tables=scott.emp
remap_schema=scott:sh 
query=scott.emp:"where ename='KING'"


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Gyan Bahadur Tamang, March 15, 2017 - 9:19 am UTC


Gyan Bahadur Tamang, March 15, 2017 - 11:45 am UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.