Imp error: ORACLE error 1400 encountered

Printable View

05-12-2010, 12:26 PM
mahajanakhil198

Imp error: ORACLE error 1400 encountered

I am facing the following error repeatedly while doing import using imp:

. . importing table "MAM_ASSETS"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("QUARKDMS"."MAM_ASSETS"."INDEX_SOURCE")
Column 1 413212
Column 2 65678
Column 3 65780
Column 4 21
Column 5
Column 6 2
Column 7 0
Column 8 1
Column 9 1
Column 10 1
Column 11 0
Column 12 412553
Column 13 4
Column 14
Column 15 7 5 E 20 19
Column 16 7 5 e 20 19
Column 17
Column 18
Column 19 25902
Column 20 2
Column 21 30-AUG-2002:13:26:54
Column 22
Column 23 30-AUG-2002:13:26:54
Column 24
Column 25 10-SEP-2002:10:14:13
Column 26 JPEG
Column 27 14-OCT-2005:14:49:58
Column 28 jpeg
Column 29 8BIM
Column 30
Column 31 8bim
Column 32 65780
Column 33 65715
Column 34 65874
Column 35 65617
Column 36 005400010108000000010000000100000009158700000A3C00...
Column 37
Column 38 14-OCT-2005:14:49:58
Column 39 1
Column 40 65874
Column 41 02-SEP-2002:13:28:51
Column 42
Column 43
Column 44 005400010208000000010000000100000025B2330008AA8200...
Column 45 US
IMP-00019: row rejected due to ORACLE error 1400

I searched google, it's of no help in my case. I also disabled constraints import. Though constraints get enabled after importing rows, still I cleared my doubt. What else can be done?
05-12-2010, 12:29 PM
davey23uk

the error is perfectly clear

cannot insert NULL into ("QUARKDMS"."MAM_ASSETS"."INDEX_SOURCE")

stop trying to insert nullls into the column
05-12-2010, 12:33 PM
mahajanakhil198

Quote:

Originally Posted by davey23uk

the error is perfectly clear

cannot insert NULL into ("QUARKDMS"."MAM_ASSETS"."INDEX_SOURCE")

stop trying to insert nullls into the column

It's import which is inserting. How can I control it. The column is BLOB type.

Structure of mam_assets is:

Code:

ID NOT NULL NUMBER(10) ACL_ID NOT NULL NUMBER(10) ASSET_GROUP_XID NOT NULL NUMBER(10) NEW_VERSION_ASSET_ID NUMBER(10) CONTAINED_IN_COUNT NOT NULL NUMBER(6) CONTAINER_OF_COUNT NOT NULL NUMBER(6) RELATED_WITH_COUNT NOT NULL NUMBER(6) ARCHIVE_STATUS NOT NULL NUMBER(1) IS_CURRENT_VERSION NOT NULL NUMBER(1) STORAGE_CHANGE_REQUEST NOT NULL NUMBER(1) HAS_HISTORY NOT NULL NUMBER(1) FAMILY_ID NOT NULL NUMBER(10) CHECKED_OUT_BY NUMBER(10) NAME VARCHAR2(255) LC_NAME VARCHAR2(255) CHECKOUT_LOCATION VARCHAR2(255) LC_CHECKOUT_LOCATION VARCHAR2(255) ASSET_SIZE NUMBER REVISION NUMBER FILE_DATE_LAST_MODIFIED DATE FILE_DATE_CREATED DATE FILE_EXTENSION VARCHAR2(255) FILE_TYPE VARCHAR2(255) FILE_CREATOR_APPLICATION VARCHAR2(255) LC_FILE_EXTENSION VARCHAR2(255) LC_FILE_TYPE VARCHAR2(255) LC_FILE_CREATOR_APPLICATION VARCHAR2(255) DATE_CREATED DATE DATE_LAST_MODIFIED DATE DATE_CHECKED_OUT DATE OWNER NUMBER(10) CREATED_BY NUMBER(10) LAST_MODIFIED_BY NUMBER(10) DESTINATION_ID NUMBER(10) LAST_ACTIVITY_ID NUMBER(10) LAST_ACCESSED_BY NUMBER(10) DATE_LAST_ACCESSED DATE PREVIOUS_REVISION NUMBER FIRST_VERSION_CREATED_BY NUMBER(10) FIRST_VERSION_DATE_CREATED DATE FTS_TEXT_MC CHAR(1) FTS_TEXT_UC CHAR(1) FTS_TEXT NOT NULL CLOB FTS_LANG NOT NULL VARCHAR2(3) INDEX_SOURCE NOT NULL BLOB
05-12-2010, 12:58 PM
PAVB

Quote:

Originally Posted by mahajanakhil198

INDEX_SOURCE NOT NULL BLOB[/CODE]

Either fix the data or make column nullable - only you know if this is acceptable according to business rules.
05-12-2010, 01:00 PM
davey23uk

crikey - why cant you understand it - you are trying to insert nulls into a not null common

this is very very basic
05-13-2010, 09:10 AM
mahajanakhil198

Dave and PAVB, I understood.

I separately created the table MAM_ASSETS and removed NOT NULL from problematic columns. Then I used ignore=y option to import. But somewhere while importing, it creates NOT NULL constraints on these columns and I again start receiving the same error.

What can I do here? How could I remove the code from import that creates NOT NULL constraints?

Or as a workaround, I am able import only MAM_ASSETS table. But now the problem is that I don't want to import MAM_ASSETS while importing the rest of schema. IMP doesn't provide something like exclude as in IMPDP.
05-13-2010, 09:29 AM
skhanal

Can you drop the existing table and let import create the table and import data.
05-13-2010, 09:36 AM
vnktummala

Quote:

IMP doesn't provide something like exclude as in IMPDP.

Akhil - look into constraints=n and indexes=n imp options.

how many tables you are trying to import? if you don't want to use constraints=n indexes=n then provide individual table names to imp excluding the one you have already imported.

Thanks,
05-13-2010, 10:18 AM
mahajanakhil198

Quote:

Originally Posted by vnktummala

Akhil - look into constraints=n and indexes=n imp options.

how many tables you are trying to import? if you don't want to use constraints=n indexes=n then provide individual table names to imp excluding the one you have already imported.

Thanks,

Around 98 tables are there.

Is there a way to import constraints only later on If I use constraints=no?

I was thinking of some other workaround. Firstly import only MAM_ASSETS table with no constraints and indexes. Then, use import with ignore=n. When table MAM_ASSETS is reached, it will be skipped with error and its indexes and constraints should get imported. I wonder if it would work.
05-13-2010, 10:32 AM
vnktummala

yes, you have multiple ways to do that.

but, first place I am wordaring why imp is trying to insert null values. Because I am sure the NOT NULL constraint is there on the source table so no chance of having null values in source. Does it mean that the BLOB column data is not exported??

as the other poster said, drop that table with cascade constraints and let imp create the table and see how it goes.

Thanks,
05-13-2010, 10:45 AM
mahajanakhil198

Quote:

Originally Posted by vnktummala

yes, you have multiple ways to do that.

but, first place I am wordaring why imp is trying to insert null values. Because I am sure the NOT NULL constraint is there on the source table so no chance of having null values in source. Does it mean that the BLOB column data is not exported??

as the other poster said, drop that table with cascade constraints and let imp create the table and see how it goes.

Thanks,

I am arranging some more disk space as I am running out of it. Though I dropped the entire schema and tried import again.
I will try to do it the way as I suggested in my previous post and then let you know.
05-14-2010, 02:02 PM
mahajanakhil198

1.I am not even able to import only MAM_ASSETS table without indexes and constraints. It gets stuck with the same error, probably because create table clause for MAM_ASSETS contains NOT NULL clause for columns INDEX_SOURCE and FTS_TEXT.

2. However, I also got the CREATE TABLE MAM_ASSETS using SHOW=Y option of imp. Using this, I manually created the MAM_ASSETS table and deleted the NOT NULL clauses for problematic columns. Then I tried to re-import the remaining data using IGNORE=Y option of IMP. Ideally, it should have imported the rows in MAM_ASSETS table but even it also returns the same error as posted in my first post.
Following this, I saw the description of MAM_ASSETS table to see if there is no NOT NULL constraint on the MAM_ASSETS table. To my surprise, these columns had NOT NULL constraint present on them. Should it have happened? I think no because the create table statement should have been ignored without errors and the rows should have gotten imported.

Any help would he highly appreciated.
05-14-2010, 03:31 PM
PAVB

Lets try a step-by-step guide...

Please follow guide and spool off eveything you do - in case something happens we will appreciate to see whole session.

1- Drop MAM_ASSETS table
2- Create MAM_ASSETS - be sure no column has NOT NULL option
3- desc MAM_ASSETS
4- import MAM_ASSETS with options... ignore=y indexes=n constraints=n
5- desc MAM_ASSETS
05-17-2010, 09:38 AM
mahajanakhil198

1 Attachment(s)

Quote:

Originally Posted by PAVB

Lets try a step-by-step guide...

Please follow guide and spool off eveything you do - in case something happens we will appreciate to see whole session.

1- Drop MAM_ASSETS table
2- Create MAM_ASSETS - be sure no column has NOT NULL option
3- desc MAM_ASSETS
4- import MAM_ASSETS with options... ignore=y indexes=n constraints=n
5- desc MAM_ASSETS

Still receiving the same error.
But the NOT NULL constraints for columns don't come after importing rows of table. Attached is the spool file.
Some 8,28,000 rows get imported and the following error gets generated repetitively for 20-25 times during import.

Import: Release 10.1.0.2.0 - Production on Mon May 17 17:35:34 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "MAM_ASSETS"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("QUARKDMS"."MAM_ASSETS"."INDEX_SOURCE")
Column 1 413212
Column 2 65678
Column 3 65780
Column 4 21
Column 5
Column 6 2
Column 7 0
Column 8 1
Column 9 1
Column 10 1
Column 11 0
Column 12 412553
Column 13 4
Column 14
Column 15 7 5 E 20 19
Column 16 7 5 e 20 19
Column 17
Column 18
Column 19 25902
Column 20 2
Column 21 30-AUG-2002:13:26:54
Column 22
Column 23 30-AUG-2002:13:26:54
Column 24
Column 25 10-SEP-2002:10:14:13
Column 26 JPEG
Column 27 14-OCT-2005:14:49:58
Column 28 jpeg
Column 29 8BIM
Column 30
Column 31 8bim
Column 32 65780
Column 33 65715
Column 34 65874
Column 35 65617
Column 36 005400010108000000010000000100000009158700000A3C00...
Column 37
Column 38 14-OCT-2005:14:49:58
Column 39 1
Column 40 65874
Column 41 02-SEP-2002:13:28:51
Column 42
Column 43
Column 44 005400010208000000010000000100000025B2330008AA8200...
Column 45 US

No NOT NULL on INDEX_SOURCE, still it is not allowing import of rows.
PS: INDEX_SOURCE is a BLOB column.
05-17-2010, 09:56 AM
PAVB

Now that we can see... we can help better.
Are you running Ora10g 10.1? this matches bug #3467567
Check Metalink... if running on Ora10g 10.1 Oracle support may recommend to upgrade to Ora10g R2 - just my guess.
05-17-2010, 11:24 AM
mahajanakhil198

Quote:

Originally Posted by PAVB

Now that we can see... we can help better.
Are you running Ora10g 10.1? this matches bug #3467567
Check Metalink... if running on Ora10g 10.1 Oracle support may recommend to upgrade to Ora10g R2 - just my guess.

Yes, it is 10g R1. I will check metalink.

Though, I forgot to thank you. Thanks for giving me this Bug ID.
05-17-2010, 11:45 AM
PAVB

Quote:

Originally Posted by mahajanakhil198

Yes, it is 10g R1. I will check metalink.

Though, I forgot to thank you. Thanks for giving me this Bug ID.

Glad to help.