Skip to Main Content
  • Questions
  • Export Tables from Oracle-12c to Oracle-10g

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Akshay.

Asked: November 10, 2018 - 6:39 am UTC

Last updated: November 16, 2018 - 3:32 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Why the following table is not being Exported from Oracle-12c to Oracle-10g

Table :
create table stock(ModID varchar(20) primary key, Name varchar(30), Type varchar(15) ,mQty number, cmpID number, price number, Warranty number);


I tried to export this table as follow,

C:\Users\Dell 7559>expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=table:\"in (\'STOCK\')\" version=10.2

Export: Release 12.1.0.2.0 - Production on Sat Nov 10 02:29:48 2018

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 "SYSTEM"."SYS_EXPORT_SCHEMA_07":  system/********@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=table:"in (\'STOCK\')" version=10.2
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39097: Data Pump job encountered unexpected error -904
ORA-39065: unexpected master process exception in DISPATCH
ORA-00904: "TAG": invalid identifier

Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" stopped due to fatal error at Sat Nov 10 02:30:19 2018 elapsed 0 00:00:31


C:\Users\Dell 7559> 


________________

If I tried to export same Table without specifying version, it works.


C:\Users\Dell 7559>expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=table:\"in (\'STOCK\')\"

Export: Release 12.1.0.2.0 - Production on Sat Nov 10 02:27:19 2018

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 "SYSTEM"."SYS_EXPORT_SCHEMA_07":  system/********@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=table:"in (\'STOCK\')"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SYSTEM"."STOCK"                            8.257 KB       5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_07 is:
  E:\JAVA FILE OPERATIONS\DB BACKUP\BACKUP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully completed at Sat Nov 10 02:27:42 2018 elapsed 0 00:00:17


C:\Users\Dell 7559>


So, what to do? Why I'm not able to export this table?
How can I export this table to *.DMP file?

and Chris said...

As the export works without the version parameter, at a guess your table uses one of the many features added after 10.2. e.g.

- Virtual columns
- Sequence defaults
- Invisible columns
- ...

But I'm not sure why you're getting that error instead of "ORA-39166: Object ... was not found or could not be exported or imported."

What is the DDL for the table you're exporting? What happens when you try and run this on a 10.2 database?

Rating

  (1 rating)

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

Comments

Problem Solved

Akshay Parulekar, November 16, 2018 - 1:09 pm UTC

I dropped table and created it again with same definition and inserted same data. It worked !

Thanks for answering !
:)
Chris Saxon
November 16, 2018 - 3:32 pm UTC

Cool, glad you got it sorted.

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.