Hi there.
I'm trying ot migrate database objects between 2 instances via network link.
Source database is 11.2.0.3 version
Target database is 12.1.0.2 version
On both systems exists a schema named TAGDATI with DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE role granted
Everything is fine until I try to transfer a single object (eg: a table)
impdp TAGDATI/hiddenpassword directory=dpdump network_link=link_tagdati tables=tagdati.export_tagdati logfile=impdp.log remap_schema=tagdati:tagdati
But when I try to use the INCLUDE parameter, with or without parameters file, the job fails with error:
ORA-39165: Schema TAGDATI was not found.
ORA-39097: Data Pump job encountered unexpected error -904
ORA-39065: unexpected master process exception in DISPATCH
ORA-00904: "TAG": invalid identifier
As said before, the schema exists on both databases with right grants (or I think so)
This is my parameters file:
NETWORK_LINK=LINK_TAGDATI
SCHEMAS=TAGDATI
INCLUDE=TABLE:"IN('export_tagdati')"
REMAP_SCHEMA=TAGDATI:TAGDATI
here's how i run impdp:
impdp tagdati/hiddenpassword directory=dpdump PARFILE=./test.par logfile=impdp.log
Any idea?
Object names in the include and exclude clauses are case-sensitive:
The name that you supply for the name_clause must exactly match, including upper and lower casing, an existing object in the database. For example, if the name_clause you supply is for a table named EMPLOYEES, then there must be an existing table named EMPLOYEES using all upper case. If the name_clause were supplied as Employees or employees or any other variation, then the table would not be found. http://docs.oracle.com/database/122/SUTIL/oracle-data-pump-export-utility.htm#SUTIL853 Note that even if you used lowercase when you created the table, unless you placed the name in double quotes it's stored in the data dictionary in uppercase:
SQL> create table t as
2 select * from all_objects;
Table created.
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
C:\windows\system32>
C:\windows\system32>expdp chris/xxxxx@db12c include=TABLE:"IN('t')" schemas=chris directory=tmp dumpfile=exp.dmp
Export: Release 12.1.0.2.0 - Production on Fri Feb 24 11:29:01 2017
Copyright (c) 1982, 2014, 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
Starting "CHRIS"."SYS_EXPORT_SCHEMA_01": chris/********@db12c include=TABLE:IN('t') schemas=chris directory=tmp dumpfile=exp.dmp
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39165: Schema CHRIS was not found.
ORA-39168: Object path TABLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "CHRIS"."SYS_EXPORT_SCHEMA_01" completed with 3 error(s) at Fri Feb 24 03:29:10 2017 elapsed 0 00:00:06
C:\windows\system32>expdp chris/xxxxx@db12c include=TABLE:"IN('T')" schemas=chris directory=tmp dumpfile=exp.dmp
Export: Release 12.1.0.2.0 - Production on Fri Feb 24 11:29:11 2017
Copyright (c) 1982, 2014, 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
Starting "CHRIS"."SYS_EXPORT_SCHEMA_01": chris/********@db12c include=TABLE:IN('T') schemas=chris directory=tmp dumpfile=exp.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CHRIS"."T" 9.600 MB 90407 rows
Master table "CHRIS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHRIS.SYS_EXPORT_SCHEMA_01 is:
/tmp/exp.dmp
Job "CHRIS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Feb 24 03:29:25 2017 elapsed 0 00:00:11