NLS considerations in Import/Export - Frequently Asked Questions_227332.1
NLS considerations in Import/Export - Frequently Asked Questions (Doc ID 227332.1)
Applies to:Oracle Database - Enterprise Edition - Version 8.0.3.0 and laterOracle Database - Standard Edition - Version 8.0.3.0 and later Information in this document applies to any platform. Purposedocument characterset conversion when using Export/Import (both the old exp/imp and expdp/impdp). Details
For the main Globalization (NLS) FAQ please see: Note:60134.1 Globalization (NLS) - Frequently Asked Questions 1. How does NLS affect exp/imp (old import/export )?
Imp and exp are client products, in the same way as SQL*Plus or Oracle Forms and will therefore translate characters from the database character set to that defined by NLS_LANG. The character set used for the export will be stored in the export file and, when the file is imported, the import will check the character set that was used. If it is different to that defined by NLS_LANG at the import site, the characters will be translated to the import character set and then, if necessary, to the database character set. 2. How should NLS_LANG be set when using exp (old export ) ?Oracle recommends to set the character set part of NLS_LANG environment parameter to ALWAYS the same character set as the character set of the database you are exporting. NLS_LANG=AMERICAN_AMERICA.
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
That way no conversion will take place and the exportfile will be created in the same character set as the original database and contain ALL data from original database (even incorrectly stored data if that would be the case).
3. How should NLS_LANG be set when using imp (old import ) ?If the source and target database have the same character set, the character set part of the NLS_LANG should be set to that same character set on both the exp and the imp side. NLS_LANG=AMERICAN_AMERICA. NLS_LANG=AMERICAN_AMERICA.
3.a) double check the NLS_CHARACTERSET on the SOURCE database
An example: you want to go from an WE8MSWIN1252 to an AL32UTF8 db using the old exp/imp tool:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
export with the NLS_LANG set to AMERICAN_AMERICA.
on unix this is:
$ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 $ export NLS_LANG $ exp .... on windows this is: c:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 c:\>exp .... 3.b) import with the NLS_LANG set to AMERICAN_AMERICA.In this example this means set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 before import into the AL32UTF8 database
on unix this is:
$ set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 $ export NLS_LANG $ imp .... on windows this is: c:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 c:\>imp .... The conversion to UTF8 is done while inserting the data in the UTF8 database by the imp connection.
We recommend to set the NLS_LANG explicit in the current shell for unix (->How to Set Unix Environment Variable ) or in the dos box used for the exp or imp tool on windows (-> "c:\>set NLS_LANG=AMERICAN_AMERICA. 4. How are exp and imp affected by the NLS_LANGUAGE and NLS_TERRITORY ?
Not. Normally you use the AMERICAN_AMERICA default, but if you imported with NLS_LANG set to FRENCH_FRANCE for example then you will not have problems, even if the originating environment used GERMAN_GERMANY or so. 5. I have the message "( possible ncharset conversion )" during imp.You see something similar to Export file created by EXPORT:V08.01.07 via direct path import done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set export server uses WE8ISO8859P15 NCHAR character set (possible ncharset conversion)
in the import log , this is normal and is not a error condition. This select will give all N-type tables:
select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB');
- But even in the case that you use N-types like NCHAR or NCLOB then this is not a problem:
6. How to know in what characterset a dmp (old exp) file is created?
simply issue: imp system/oracle@database show=yes file=test.dmp
import done in US7ASCII character set and AL16UTF16 NCHAR character set
-> this is the current NLS_LANG value set in the environment and the NCHAR characterset of the target database import server uses WE8MSWIN1252 character set (possible charset conversion) -> this is only shown if the NLS_LANG during this import session is different from the target database characterset, so if you see 3 lines you might have problems :-) export client uses UTF8 character set (possible charset conversion) -> this is the characterset used during the export session and the characterset used in the dmp file.
|
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1345688/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- NLS considerations in Import Export - Frequently Asked Questions (文件 ID 227332.1)IDEImportExport
- Export/Import and NLS Considerations (此文章不錯,推薦)ExportImportIDE
- Oracle Advanced Security Frequently Asked QuestionsOracle
- FAQ: SQL Query Performance - Frequently Asked Questions_398838.1SQLORM
- export/importExportImport
- mysql export & importMySqlExportImport
- mysql import and exportMySqlImportExport
- import、require 、export、export default、exports、module exportsImportUIExport
- ES6 import exportImportExport
- oracle Export/Import工具使用OracleExportImport
- statistics的export與import!ExportImport
- DB2 export and importDB2ExportImport
- DLL的Export和ImportExportImport
- module.exports 、 exports 和 export 、 export default 、 importExportImport
- Import Error: cannot import name ‘export_saved_modelImportErrorExport
- Export/import Datas To/from a Csv FileExportImport
- import,export的支援[nodejs]ImportExportNodeJS
- full database export and import(實戰)DatabaseExportImport
- DataPump Export/Import Of LOBs Are Not Executed in ParallelExportImportParallel
- oracle Export and Import 簡介(轉)OracleExportImport
- Data Utilities : Export and Import Utilities (57)ExportImport
- Export And Import Between Different Oracle VersionsExportImportOracle
- 新手關於import/export的理解ImportExport
- ES6 module模組 import exportImportExport
- 1nd round export and import errorExportImportError
- Oracle 12c full transportable export & importOracleExportImport
- JS/TS 的 import 和 export 用法小結JSImportExport
- node識別es6的 import/exportImportExport
- DB2 export 與 import 相關操作DB2ExportImport
- 詳解es6的export和import命令ExportImport
- Run Out Of Space On UNDO Tablespace Using DataPump Import/ExportImportExport
- import、require、export、module.exports 混合使用詳解ImportUIExport
- 【metalink】Export/Import DataPump Parameter TRACE (文件 ID 286496.1)ExportImport
- Export and import right application or execute import imp-00010 error solveExportImportAPPError
- ES6模組化之export和import的用法ExportImport
- ES6規範import和export用法總結ImportExport
- 如何讓瀏覽器支援 import 和export語法瀏覽器ImportExport
- 關於 ES6 的 import 與 export 的思考ImportExport