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

  • From: Riku Räsänen <riku.rasanen@xxxxxxxxxxxxxxxx>
  • To: sol.beach@xxxxxxxxx, oracle_l <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 19 Mar 2008 17:45:05 +0200

You are creating too long column for NVARCHAR2. From SQL-reference:

"The maximum column size allowed is 4000 bytes."
In this case you have AL16UTF16 which means 2 bytes per character and your column is NVARCHAR2 (3000) = 6000 bytes. 






"NVARCHAR2 Datatype
The NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.
The maximum length of the column is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support. 
"




On Wed, 19 Mar 2008 17:34:16 +0200, sol beach <sol.beach@xxxxxxxxx> wrote:


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!




--
Riku Räsänen
Kantamestarit OY
riku.rasanen@xxxxxxxxxxxxxxxx
+358 (0)50 548 0589
--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2008