I saw somebody were asking how to change the character set id in the Oracle export file. I did once in 1998, we had a export file from a US7ASCII database, and wanted to import it into a ZHS16GBK database, to avoid any data conversion. But I am not sure whether this method is safe enough. Oracle store the character set id information in the second and third bytes of the export file. We just need to change it, I wrote a dmp2utf8 utility for this job.
Usange: dmp2utf8 dmpfile [charset id]
The first parameter is the eport file name, the second is the target character id, if you ommit the second argument, the default is 871, the character id of UTF8. This utility just can be used to avoid possible conversion when importing, so if you specify the wrong NLS_LANG setting when exporting, it will not help at all.
The next problem is how to get the id of the target character set, we can query it from existing database by following query. For example to get the character set id of UTF8, run the following query.
ASQL> select nls_charset_id(value) ID,
2 VALUE CHARSET_NAME
3 from v$nls_valid_values
4 where parameter = 'CHARACTERSET'
5 AND nls_charset_id(value) IS NOT NULL
6 AND VALUE LIKE '%UTF8%'
7 /
ID CHARSET_NAME
--- ------------
871 UTF8
873 AL32UTF8
2 rows returned.
Important notes: I provide the tool and source code here, but not responsible for any possible issues.