Home » RDBMS Server » Server Utilities » import/export query
import/export query [message #287019] Mon, 10 December 2007 23:14 Go to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
hi am trying to do an imp and i get the following error. i've also added the steps followed during exp

root@skinner{~}: export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

root@skinner{~}: imp system/manager full=y file=db1.exp log=imp.log

Import: Release 9.2.0.7.0 - Production on Mon Dec 10 15:15:01 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
IMP-00046: using FILESIZE value from export file of 2147483648
.importing SYSTEM's objects into SYSTEM
.importing NETACT's objects into NETACT
.importing SYS_OWNER's objects into SYS_OWNER
.importing table "FM_ACTIVE_EVENTS" 0 rows imported
.importing table "FM_EVENT_LOG" 4486 rows imported
.importing table "INV_CARDS" 3437 rows imported
.importing table "INV_CARD_PARAMS" 20622 rows imported
.importing table "INV_CARD_PROFILES" 0 rows imported
.importing table "INV_CARD_TYPES" 1052 rows imported
.importing table "INV_CLIENT_SUBIF_IPSPOOF_CHK" 1010644 rows imported
.importing table "INV_CLIENT_SUB_IF_PARAMS" 0 rows imported
.importing table "INV_CLIENT_SUB_IF_PROFILES" 0 rows imported
.importing table "INV_CLIENT_SUB_IF_TYPES" 0 rows imported
.importing table "INV_CLIENT_SUB_INTERFACES"
IMP-00019: row rejected due to ORACLE error 1401
IMP-00003: ORACLE error 1401 encountered
ORA-01401: inserted value too large for column

Report message to a moderator

Re: import/export query [message #287023 is a reply to message #287019] Mon, 10 December 2007 23:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
1. Why you import full database ?

Report message to a moderator

Re: import/export query [message #287027 is a reply to message #287019] Mon, 10 December 2007 23:27 Go to previous messageGo to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
its a migration from one release to another .

Report message to a moderator

Re: import/export query [message #287028 is a reply to message #287027] Mon, 10 December 2007 23:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
>its a migration from one release to another .
Then don't use full export/import.
only export required schemas and import to new database on new oracle version.

I guess your database size is small otherwise you should use another upgrade option.

Report message to a moderator

Re: import/export query [message #287044 is a reply to message #287019] Tue, 11 December 2007 00:20 Go to previous messageGo to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
so its not the problem with the exp or imp statement ??

Report message to a moderator

Re: import/export query [message #287074 is a reply to message #287019] Tue, 11 December 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

import server uses AL32UTF8 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)

You convert to AL32UTF8, some characters that were on 1 byte now take 2 or 3 bytes thus the error.

Regards
Michel

[Updated on: Tue, 11 December 2007 01:06]

Report message to a moderator

Re: import/export query [message #287075 is a reply to message #287044] Tue, 11 December 2007 01:06 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
I can say only

IMP-00046: it is just information message.

ORA-01401: inserted value too large for column

Cause: The value entered is larger than the maximum width defined for the column.

Action: Enter a value smaller than the column width or use the MODIFY option with ALTER TABLE to expand the column width.

Report message to a moderator

Re: import/export query [message #287082 is a reply to message #287019] Tue, 11 December 2007 01:14 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Try by changing database character set, or precreate table with higher column length and the perform import.

Report message to a moderator

Re: import/export query [message #287088 is a reply to message #287082] Tue, 11 December 2007 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Better, precreate the table and change the semantics of length from BYTE to CHAR.

SQL> create table t (val varchar2(10 byte));

Table created.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       VARCHAR2(10)

SQL> alter table t modify (val varchar2(10 char));

Table altered.

SQL> desc t
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 VAL                                       VARCHAR2(10 CHAR)

Regards
Michel

Report message to a moderator

Re: import/export query [message #287113 is a reply to message #287019] Tue, 11 December 2007 01:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Or change nls_length_semantics parameter to Char and then create table.

Report message to a moderator

Re: import/export query [message #287119 is a reply to message #287019] Tue, 11 December 2007 02:10 Go to previous messageGo to next message
glenvishal
Messages: 8
Registered: May 2005
Junior Member
thanks for the replies .. i ll try doing it

Report message to a moderator

Re: import/export query [message #287127 is a reply to message #287119] Tue, 11 December 2007 02:43 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just to be precise (on an AL32UTF8 database):
SQL> create table t (col varchar2(1));

Table created.

SQL> insert into t values('é');
insert into t values('é')
                     *
ERROR at line 1:
ORA-01401: inserted value too large for column


SQL> alter table t modify (col varchar2(1 char));

Table altered.

SQL> insert into t values('é');

1 row created.

SQL> drop table t;

Table dropped.

SQL> alter session set nls_length_semantics=CHAR;

Session altered.

SQL> create table t (col varchar2(1));

Table created.

SQL> insert into t values('é');

1 row created.

SQL> drop table t;

Table dropped.

Regards
Michel

Report message to a moderator

Previous Topic: export/import
Next Topic: HOW CAN I RECOVER DATA IN ORACLE DATABASE 8.1.5 WHEN EXP COMMAND NOT WORKING
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 16:30:27 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.