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

Hi,

Did you check what NLS_LENGTH_SEMANTICS is set to in your production DB ?

The disconnect I see in your output:

Export was done with AL16UTF16 NCHAR character set The length of NVARCHAR2 column in the dump file is 3000 which is over 2000 allowable if NLS_LENGTH_SEMANTICS is set to CHAR .

--romas

On 3/19/08, sol beach <sol.beach_at_gmail.com> 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!
>

--
http://www.freelists.org/webpage/oracle-l