Search code examples
oracleimportoracle11goracle12cimp

Import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set, but nothing happen?

I got no error when importing my XXX.DMP file using imp, but there is no database, schema, or table has imported and log file is empty too. Here what it's look :

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Import data only (yes/no): no >

Import file: EXPDAT.DMP > D:\xxx.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V11.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
C:\Users\User>

As you can see, it's just stop right there.

I using Oracle 11 and this file exported from Oracle 12. Then I changed the DMP file header from EXPORT:V12.01.00 to EXPORT:V11.02.00 manually because it won't work with the EXPORT:V12.01.00 before.

I've already try with impd or imported it with TOAD for Oracle, but neither is work.

Solution

  • I changed the DMP file header ... manually

    Don't do that. From the documentation:

    • Any export dump file can be imported into a later release of the Oracle database.

    • The Import utility cannot read export dump files created by the Export utility of a later maintenance release or version. For example, a release 9.2 export dump file cannot be imported by a release 9.0.1 Import utility.

    This isn't as simple as saying the version shown in the header is different. The binary format is proprietary and can (and presumably does) change all the time. Trying to manipulate the file is only going to cause you pain, and in your case is causing the import to abort very early on - which is probably a good thing; you could end up with corruption if it did manage to get any further.

    The correct thing to do is to use the 11gR2 client to perform the export from the 12cR1 database as well.

    The more correct thing to do is to use data pump tools (expdp and impdp) instead of the legacy import/export tools, with similar caveats.

    If your 12c database is no longer available and you can't do a new export then you're a bit stuck. The only thing you can really do is create a new 12c database, use your existing dump file to populate that, and then do an export from that using the 11g tools. Painful, but safe.