sql> select nls_charset_id('zhs16gbk') from dual;nls_charset_id('zhs16gbk')-------------------------- 8521 row selected.sql> select nls_charset_name(852) from dual;nls_char--------zhs16gbk1 row selected.十进制转换十六进制:sql> select to_char('852','xxxx') from dual;to_ch----- 3541 row selected.
对应上面的图中第2、3字节，我们知道该导出文件字符集为zhs16gbk.
查询数据库中有效的字符集可以使用以下脚本:
col nls_charset_id for 9999col nls_charset_name for a30col hex_id for a20select nls_charset_id(value) nls_charset_id, value nls_charset_name,to_char(nls_charset_id(value),'xxxx') hex_idfrom v$nls_valid_valueswhere parameter = 'characterset'order by nls_charset_id(value)/
e:/nls2>set nls_lang=american_america.us7asciie:/nls2>imp eygle/eygle file=sus7ascii-cus7ascii.dmp fromuser=eygle touser=eygle tables=testimport: release 9.2.0.4.0 - production on mon nov 3 17:14:39 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v09.02.00 via conventional pathimport done in us7ascii character set and al16utf16 nchar character setimport server uses zhs16gbk character set (possible charset conversion). . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on mon nov 3 17:14:50 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select name,dump(name) from test;namedump(name)-----------------------------????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,1162 rows selected.sql> exitdisconnected from oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productione:/nls2>set nls_lang=american_america.zhs16gbke:/nls2>imp eygle/eygle file=sus7ascii-cus7ascii.dmp fromuser=eygle touser=eygle tables=test ignore=yimport: release 9.2.0.4.0 - production on mon nov 3 17:15:28 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v09.02.00 via conventional pathimport done in zhs16gbk character set and al16utf16 nchar character setexport client uses us7ascii character set (possible charset conversion). . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on mon nov 3 17:15:34 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select name,dump(name) from test;namedump(name)--------------------------------------------------------------------------------????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,116????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,1164 rows selected.sql> drop table test;table dropped.sql> exitdisconnected from oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productione:/nls2>set nls_lang=american_america.zhs16gbke:/nls2>imp eygle/eygle file=sus7ascii-czhs16gbk.dmp fromuser=eygle touser=eygle tables=test ignore=yimport: release 9.2.0.4.0 - production on mon nov 3 17:17:21 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v09.02.00 via conventional pathimport done in zhs16gbk character set and al16utf16 nchar character set. . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on mon nov 3 17:17:30 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select name,dump(name) from test;namedump(name)----------------------------------------------????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,1162 rows selected.sql> exitdisconnected from oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productione:/nls2>set nls_lang=american_america.us7asciie:/nls2>imp eygle/eygle file=sus7ascii-czhs16gbk.dmp fromuser=eygle touser=eygle tables=test ignore=yimport: release 9.2.0.4.0 - production on mon nov 3 17:18:00 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v09.02.00 via conventional pathimport done in us7ascii character set and al16utf16 nchar character setimport server uses zhs16gbk character set (possible charset conversion)export client uses zhs16gbk character set (possible charset conversion). . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on mon nov 3 17:18:08 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select name,dump(name) from test;namedump(name)----------------------------------------????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,116????typ=1 len=4: 63,63,63,63testtyp=1 len=4: 116,101,115,1164 rows selected.sql>
e:/nls2>set nls_lang=american_america.zhs16gbke:/nls2>imp eygle/eygle file=sus7ascii-cus7ascii-exp817.dmp fromuser=eygle touser=eygle tables=testimport: release 9.2.0.4.0 - production on mon nov 3 17:37:17 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v08.01.07 via conventional pathimport done in zhs16gbk character set and al16utf16 nchar character setexport server uses utf8 nchar character set (possible ncharset conversion). . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on mon nov 3 17:37:23 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select name,dump(name) from test;name dump(name)--------------------------------------------------------------------------------测试 typ=1 len=4: 178,226,202,212test typ=1 len=4: 116,101,115,1162 rows selected.sql>
sql> col parameter for a30sql> col value for a30sql> select * from v$nls_parameters;parameter value------------------------------ ------------------------------nls_language americannls_territory americanls_currency $nls_iso_currency americanls_numeric_characters .,nls_calendar gregoriannls_date_format dd-mon-rrnls_date_language americannls_characterset zhs16gbknls_sort binary……………….19 rows selected.sql> create database character set us7ascii;create database character set us7ascii*error at line 1:ora-01031: insufficient privilegessql> select * from v$nls_parameters;parameter value------------------------------ ------------------------------nls_language americannls_territory americanls_currency $nls_iso_currency americanls_numeric_characters .,nls_calendar gregoriannls_date_format dd-mon-rrnls_date_language americannls_characterset us7asciinls_sort binary…………..19 rows selected.sql> exitdisconnected from oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productione:/nls2>set nls_lang=american_america.us7asciie:/nls2>imp eygle/eygle file=sus7ascii-cus7ascii.dmp fromuser=eygle touser=eygleimport: release 9.2.0.4.0 - production on sun nov 2 14:53:26 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to: oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionexport file created by export:v09.02.00 via conventional pathimport done in us7ascii character set and al16utf16 nchar character setimport server uses zhs16gbk character set (possible charset conversion). . importing table "test" 2 rows importedimport terminated successfully without warnings.e:/nls2>sqlplus eygle/eyglesql*plus: release 9.2.0.4.0 - production on sun nov 2 14:53:35 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select * from test;name----------测试test2 rows selected.
我们看到，当发出create database character set us7ascii；命令时，数据库v$nls_parameters中的字符集设置随之更改，该参数影响导入进程， 更改后可以正确导入数据，重起数据库后，该设置恢复。
e:/nls2>sqlplus "/ as sysdba"sql*plus: release 9.2.0.4.0 - production on sun nov 2 19:42:07 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.connected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionsql> select instance_name from v$intance;select instance_name from v$intance *error at line 1:ora-00942: table or view does not existsql> select instance_name from v$instance;instance_name----------------penny1 row selected.sql> @?/rdbms/admin/csminst.sqluser created.grant succeeded.………..
这个脚本创建相应用户(csmig)及数据字典对象，扫描信息会记录在相应的数据字典表里。
我们可以在命令行调用这个工具对数据库进行扫描:
e:/nls2>csscan full=y fromchar=zhs16gbk tochar=us7ascii log=us7check.log capture=y array=1000000 process=2character set scanner v1.1 : release 9.2.0.1.0 - production on sun nov 2 20:24:45 2003copyright (c) 1982, 2002, oracle corporation. all rights reserved.username: eygle/eygleconnected to:oracle9i enterprise edition release 9.2.0.4.0 - productionwith the partitioning, oracle label security, olap and oracle data mining optionsjserver release 9.2.0.4.0 - productionenumerating tables to scan.... process 1 scanning sys.source$[aaaabhaabaaaairaaa]. process 2 scanning sys.attribute$[aaaaeoaabaaaahzaaa]. process 2 scanning sys.parameter$[aaaaeoaabaaaahzaaa]. process 2 scanning sys.method$[aaaaeoaabaaaahzaaa]……... process 2 scanning system.def$_aqerror[aaaa8faabaaacwjaaa]. process 1 scanning wmsys.wm$env_vars[aaabewaabaaafmzaaa]………………….. process 2 scanning sys.ugroup$[aaaaa5aabaaaagpaaa]. process 2 scanning sys.con$[aaaaacaabaaaacpaaa]. process 1 scanning sys.file$[aaaaaraabaaaabxaaa]creating database scan summary report...creating individual exception report...scanner terminated successfully.
然后我们可以检查输出的日志来查看数据库扫描情况:
database scan individual exception report[database scan parameters]parameter value ------------------------------ ------------------------------------------------scan type full database scan char data? yes current database character set zhs16gbk new database character set us7ascii scan nchar data? no array fetch buffer size 1000000 number of processes 2 capture convertible data? yes ------------------------------ ------------------------------------------------[data dictionary individual exceptions][application data individual exceptions]user : eygletable : testcolumn: nametype : varchar2(10)number of exceptions : 1 max post conversion data size: 4 rowid exception type size cell data(first 30 bytes) ------------------ ------------------ ----- ------------------------------aaabpiaadaaaaamaaa lossy conversion 测试 ------------------ ------------------ ----- ------------------------------
