You may have a database that has been in service for a long time, perhaps for several years. It may be that when the database was created, the US7ASCII character set was in use. If this is an E-Business Suite database, if may be necessary to print barcodes or special symbols, or even invoices in different languages. These things can’t be done with the US7ASCII character set. But fear not! Oracle has an easy solution to upgrade the character set to Unicode. The current standard is now the AL32UTF8 character set. UTF8 has been widely used and is still supported but it is also now deprecated. So, for having a Unicode enable database, AL32UTF8 is the best choice.
If your database is a currently supported patchset version of 11gR2 (18.104.22.168+), Oracle has a new free tool to assist with character set conversions – the Oracle Database Migration Assistant for Unicode (DMU). Some older versions are supported with additional patches. The old tools – CSSCAN, CSCONV, and CSALTER – are no longer supported on releases that support DMU. The new version 2.1 is supported on many platforms, both 32 and 64-bit, and uses JDK 6 or JDK 7.
Oracle has plenty of documentation to assist with the use of this tool. You can find the primary URL at: http://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/overview/index.html
Click on the Downloads tab, and you will find the download link:
You must have the JDK 6 or JDK 7 path. It’s helpful to first read the documentation: http://docs.oracle.com/cd/E64126_01/index.htm
The DMU is a GUI tool and is fairly easy to use.
Click on the database you want to scan, and the tool will guide you through all the steps.
You can accept the defaults.
The tool will display the scanning progress on all the tables.
It generates a spreadsheet report for review of all issues in the current database. This tool can be used to perform a character set conversion in place, or you can still use expdp/impdp to perform the character set conversion. This is useful when migrating across platforms. US7ASCII is a subset of the Unicode AL32UTF8 character set, so usually there are no issues with the conversion. The exception being sometimes there are issues from data expansion caused by the conversion, but the ProblemDataReport spreadsheet will detail down to every column and row id any data issues.
Data cleanup may be done in advance in order to prevent partial data loss. The tool can fix the data in place to prepare it for a conversion. Once the data has been cleaned up, there should be no issues with the data after performing an export/import.