Pavan DBA's Blog

The DBA Knowledge Store

How to resolve ORA-01400 during import(imp) in 11g database

Posted by Pavan DBA on July 12, 2014


Posting after long time…

Recently I am performing a refresh activity using export/import. Donno the reason, but DBA who handled this database in the past configured traditional export (exp) backup on 11.2.0.3 database.

I got a request to import that dump into UAT database which I started performing using imp utility.

For some of the tables, i received ORA-01400 error and detailed message will be as below

. . importing table “TEST”
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into (“ABC”.”TEST”.”C1″)
Column : 1
Column :

Note that schema, table and column names are changed for security reasons.

Immediately I verified the column C1 in TEST table and observed that it is defined as NOT NULL. Intially, I thought this might be the reason for the error that Oracle is trying to insert NULL value. I checked in the production database and found that there is no NULL values at all in C1 column which means the table structure, data everything is perfect.

After referring to MOS doc 826746.1, got to know that this is a known issue when you take export with exp command from 11gR1 onwards using DIRECT=Y option.

This is because there is behaviour change in exp utility. Prior to 11gR1 when you take export (exp) with DIRECT=Y, if a NULL value is there in column, Oracle used to replace that with default value what it will have in COL$ table. But, from 11gR1 onwards, the value is not replaced by its default but the NULL values is stored in export dump. This will produce ORA-1400 during import.

The solution for this problem is simple…. take the export in conventinal path only i.e don’t use DIRECT=Y or use datapump for your activity.

Even though it seems to be a problem, Oracle has not opened any bug on this because exp utility is desupported from 11g onwards and Oracle recommends to use datapump only.

Finally, i temporarily fixed the issue by taking TEST table export in expdp. Also, changed the daily export backup method from exp to expdp in the production database.

Hope this helps for some people….

Advertisements

3 Responses to “How to resolve ORA-01400 during import(imp) in 11g database”

  1. jacab said

    Oracle DBA Online Training
    Hyderabadsys Online Training gives continuous and arrangement centered Oracle DBA preparing. Our Oracle DBA course incorporates fundamental to cutting edge level and our Oracle DBA course is intended to get the arrangement in great MNC organizations. Hyderabadsys Online Training Oracle DBA coaches are prophet 10g 11g dba ensured specialists and experienced working experts with active continuous various Oracle DBA ventures information. We have planned our Oracle DBA course substance and syllabus focused around understudies necessity to accomplish everybody’s vocation goal

  2. Imaduddin said

    Thanks for the information.
    Great work and support.

  3. prakashvarma said

    Pavan , nice post, if we come across this error , mind strikes to your post 😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: