V10.2.0.1 exp/imp ORA-00910: specified length too long for its datatype

From: sol beach <sol.beach_at_gmail.com>
Date: Wed, 19 Mar 2008 08:34:16 -0700
Message-ID: <40a16b360803190834l298fc42dm4d1acff1bf55f86b@mail.gmail.com>


All,

I am stumped.
I am at a site where I did not do initial setup. Previous DBA departed.

I have used export from Production & am trying to import into new test DB. I have
ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both; & bounced the DB.

 sh -x test-imp.sh
+ date

Wed Mar 19 08:17:30 PDT 2008
+ export NLS_LANG=.UTF8
+ NLS_LANG=.UTF8

++ date

+ BEFORE='Wed Mar 19 08:17:30 PDT 2008'
+ echo 'Wed Mar 19 08:17:30 PDT 2008'
+ cd /u05/orasupp/orcl/datapump/
++ date

+ MID='Wed Mar 19 08:17:30 PDT 2008'
+ echo 'Wed Mar 19 08:17:30 PDT 2008'
+ export NLS_CHAR=UTF8
+ NLS_CHAR=UTF8
+ export NLS_CHARACTERSET=UTF8
+ NLS_CHARACTERSET=UTF8
+ export NLS_NCHAR_CHARACTERSET=UTF8
+ NLS_NCHAR_CHARACTERSET=UTF8
+ imp inlogic/inlogic file=/u05/orasupp/orcl/datapump/inlogic.dmp log=
test-imp123.log tables=TBLCUSTOMREPORT buffer=32000000

Import: Release 10.2.0.1.0 - Production on Wed Mar 19 08:17:30 2008

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

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

Export file created by EXPORT:V10.02.01 via direct path import done in UTF8 character set and AL16UTF16 NCHAR character set import server uses WE8ISO8859P1 character set (possible charset conversion) export server uses UTF8 NCHAR character set (possible ncharset conversion) . importing INLOGIC's objects into INLOGIC . importing INLOGIC's objects into INLOGIC IMP-00017: following statement failed with ORACLE error 910:  "CREATE TABLE "TBLCUSTOMREPORT" ("TITLE" NVARCHAR2(60) NOT NULL ENABLE, "SQL"
 "" NVARCHAR2(3000) NOT NULL ENABLE, "ORGANIZATIONID" NUMBER(*,0), "DESCRIPTI"
 "ON" NVARCHAR2(500)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE("
 "INITIAL 2097152 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPA"
 "CE "APP_INLOGIC_TABLESPACE" LOGGING NOCOMPRESS" IMP-00003: ORACLE error 910 encountered
ORA-00910: specified length too long for its datatype

Import terminated successfully with warnings.
+ echo Wed Mar 19 08:17:30 PDT 2008

Wed Mar 19 08:17:30 PDT 2008

++ date

+ END='Wed Mar 19 08:21:46 PDT 2008'
+ date
+ echo 'Wed Mar 19 08:21:46 PDT 2008'

I believe the error involves the SQL NCHAR2(3000) column. I suspect the fix is a 1 line fix, but at the present I don't know what needs to be changed.

Any ideas, input, suggestions would be most appreciated.

TIA!

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 19 2008 - 10:34:16 CDT

Original text of this message