| exp/imp error [message #307703] |
Wed, 19 March 2008 10:30  |
 |
BlackSwan
Messages: 26766
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 #307717 is a reply to message #307703] |
Wed, 19 March 2008 12:01  |
|
BlackSwan
Messages: 26766
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'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
