使用命令:imp file=/home/2/2009_09_15useradmin.dmp ignore=y fromuser=useradmin touser=useradminImport: Release 9.2.0.4.0 - Production on Thu Sep 17 11:00:54 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Username:
Password: Connected to: Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses ZHS32GB18030 character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 957:
"CREATE TABLE "ALLFIELDS" ("???" VARCHAR2(50), "????" VARCHAR2(50), "????" V"
"ARCHAR2(50), "????" VARCHAR2(50), "????" VARCHAR2(50), "??" VARCHAR2(50)) "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
"TS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 957 encountered
ORA-00957: duplicate column name
IMP-00017: following statement failed with ORACLE error 957:
已创建useradmin用户并附于权限了.
我的判断的是之前那个数据库的环境用的是中文字符,其环境变量/etc/profile的内容:
export LD_ASSUME_KERNEL=2.4.1
#export ORACLE_HOME=/opt/oracle10g/u01
#export ORACLE_BASE=/opt/oracle10g
export ORACLE_HOME=/opt/oracle9i/u01
export ORACLE_BASE=/opt/oracle9i
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib:/usr/local/lib
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS32GB18030"
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export EDITOR=vim
export ORACLE_OWNER=oracle
export ORACLE_SID=ORCL
export ORACLE_TERM=xterm
export SQLPATH=$HOME
export PATH=$PATH:$ORACLE_HOME/bin而新的系统的环境变量/home/oracle/.bash_profile内容如下
unset USERNAME
#oracle 9i
export ORACLE_BASE=/home/ora9/
export ORACLE_HOME=/home/ora9/product/9.2
export ORACLE_OWNER=oracle
export ORACLE_SID=ORCL
export ORACLE_TERM=xterm
export LD_ASSUME_KERNEL=2.4.1
export THREADS_FLAG=native
export LD_LIBRARY_PATH=/home/ora9/product/9.2/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=/home/ora9/product/9.2/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
请前辈不吝赐教,谢谢!
Password: Connected to: Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses ZHS32GB18030 character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 957:
"CREATE TABLE "ALLFIELDS" ("???" VARCHAR2(50), "????" VARCHAR2(50), "????" V"
"ARCHAR2(50), "????" VARCHAR2(50), "????" VARCHAR2(50), "??" VARCHAR2(50)) "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
"TS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 957 encountered
ORA-00957: duplicate column name
IMP-00017: following statement failed with ORACLE error 957:
已创建useradmin用户并附于权限了.
我的判断的是之前那个数据库的环境用的是中文字符,其环境变量/etc/profile的内容:
export LD_ASSUME_KERNEL=2.4.1
#export ORACLE_HOME=/opt/oracle10g/u01
#export ORACLE_BASE=/opt/oracle10g
export ORACLE_HOME=/opt/oracle9i/u01
export ORACLE_BASE=/opt/oracle9i
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib:/usr/local/lib
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS32GB18030"
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
export EDITOR=vim
export ORACLE_OWNER=oracle
export ORACLE_SID=ORCL
export ORACLE_TERM=xterm
export SQLPATH=$HOME
export PATH=$PATH:$ORACLE_HOME/bin而新的系统的环境变量/home/oracle/.bash_profile内容如下
unset USERNAME
#oracle 9i
export ORACLE_BASE=/home/ora9/
export ORACLE_HOME=/home/ora9/product/9.2
export ORACLE_OWNER=oracle
export ORACLE_SID=ORCL
export ORACLE_TERM=xterm
export LD_ASSUME_KERNEL=2.4.1
export THREADS_FLAG=native
export LD_LIBRARY_PATH=/home/ora9/product/9.2/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=/home/ora9/product/9.2/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
请前辈不吝赐教,谢谢!
SQL> select * from nls_database_parameters;PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICANNLS_TERRITORY
AMERICANLS_CURRENCY
$
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICANLS_NUMERIC_CHARACTERS
.,NLS_CHARACTERSET
WE8ISO8859P1
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CALENDAR
GREGORIANNLS_DATE_FORMAT
DD-MON-RRNLS_DATE_LANGUAGE
AMERICAN
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_SORT
BINARYNLS_TIME_FORMAT
HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY
$
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_COMP
BINARYNLS_LENGTH_SEMANTICS
BYTENLS_NCHAR_CONV_EXCP
FALSE
PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16NLS_RDBMS_VERSION
9.2.0.4.0
20 rows selected.
SQL> select * from nls_database_parameters; 看 NLS_CHARACTERSET
export client uses ZHS32GB18030 character set (possible charset conversion)
字符集不一致....
DBCA时候选字符集的时候用ZHS16CGB231280
NLS_CHARACTERSET
ZHS16CGB231280那应该是选 'ZHS32GB18030'还是'ZHS16CGB231280'?
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
export client uses ZHS32GB18030 character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 957:
"CREATE TABLE "ALLFIELDS" ("???" VARCHAR2(50), "????" VARCHAR2(50), "????" V"
"ARCHAR2(50), "????" VARCHAR2(50), "????" VARCHAR2(50), "??" VARCHAR2(50)) "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIS"
"TS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 957 encountered
ORA-00957: duplicate column name
IMP-00017: following statement failed with ORACLE error 957:列名重复,导入之前我只创建了这个用户并授权,没有进行创表空间之类的操作,该数据库是刚刚建成的..请各位前辈帮个忙...
export NLS_LANG=SIMPLIFIED chinese_china.ZHS16CGB231280然后imp之前
export NLS_LANG=SIMPLIFIED chinese_china.ZHS16CGB231280
export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS32GB18030
然后 imp 试试
测试结果如下:
[oracle@test7777 oracle]$ imp useradmin/useradmin@ORCL file=/file/2009_09_15useradmin.dmp ignore=y fromuser=useradmin touser=useradmin;Import: Release 9.2.0.4.0 - Production on 星期三 8月 26 07:08:23 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - ProductionExport file created by EXPORT:V09.02.00 via conventional path
import done in ZHS32GB18030 character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
. . importing table "ALLFIELDS" 288 rows imported
. . importing table "ASSOCIATEFIELD" 7 rows imported
. . importing table "DATADICTION"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "数据类型": invalid identifier
. . importing table "TEMPLETS" 14 rows imported
. . importing table "TEST" 357 rows imported
. . importing table "T_INPUT" 2 rows imported
. . importing table "T_PROTECTBAK" 1 rows imported
. . importing table "USERADMIN" 14 rows imported
. . importing table "USERADMIN2" 26 rows imported
. . importing table "电源企业资料" 5167 rows imported
. . importing table "东莞客户资料" 1013 rows imported
. . importing table "公司客户" 37820 rows imported
. . importing table "联系记录" 25792 rows imported
. . importing table "联系记录备份" 4 rows imported
. . importing table "名片客户资料" 3975 rows imported
. . importing table "网站客户资料" 4559 rows imported
. . importing table "仪器仪表" 4813 rows imported
. . importing table "元器件" 12418 rows imported
. . importing table "元器件快递" 5555 rows imported
. . importing table "粘贴错误" 246 rows imported
. . importing table "注册客户" 200919 rows imported
. . importing table "注册客户资料A" 4648 rows imported
Import terminated successfully with warnings.
重建用户,Imp的时候不带ignore,试试
OK,基本搞定,多谢c4313848老兄这么长时间的回答.
十分感谢呀.