Home » RDBMS Server » Server Utilities » exp/imp error (V10.2.0.1 on RH AS4)
exp/imp error [message #307703] Wed, 19 March 2008 10:30 Go to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Guys,

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!
Re: exp/imp error [message #307705 is a reply to message #307703] Wed, 19 March 2008 10:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just a wild shot.
Possibly internal bug 1488174 as discussed with a solution in metalink note 144808.1
Re: exp/imp error [message #307707 is a reply to message #307703] Wed, 19 March 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you datatabase national character set is AL16UTF16, NCHAR/NVARCHAR2 is limited to 2000.
Precreate the table before import.

Regards
Michel
Re: exp/imp error [message #307717 is a reply to message #307703] Wed, 19 March 2008 12:01 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
THANKS ALL.

The solution which I was hoping to avoid was to drop the database
& create it using UTF8 characterset.

+ 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 09:53:58 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 UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing INLOGIC's objects into INLOGIC
. importing INLOGIC's objects into INLOGIC
. . importing table              "TBLCUSTOMREPORT"       1474 rows imported
IMP-00017: following statement failed with ORACLE error 942:
 "ALTER TABLE "TBLCUSTOMREPORT" ADD CONSTRAINT "CR_ORGID_FK" FOREIGN KEY ("OR"
 "GANIZATIONID") REFERENCES "TBLORGANIZATION" ("ORGANIZATIONID") ON DELETE CA"
 "SCADE ENABLE NOVALIDATE"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 2430:
 "ALTER TABLE "TBLCUSTOMREPORT" ENABLE CONSTRAINT "CR_ORGID_FK""
Import terminated successfully with warnings.
+ echo Wed Mar 19 09:53:58 PDT 2008
Wed Mar 19 09:53:58 PDT 2008
++ date
+ END='Wed Mar 19 09:57:42 PDT 2008'
Previous Topic: SQL Loader Entire Job Commit
Next Topic: export 10g & import 9i
Goto Forum:
  


Current Time: Sat Dec 10 06:46:11 CST 2016

Total time taken to generate the page: 0.08359 seconds