Some old databases are created with US7ASCII character set, when the client NLS_LANG setting was the same with database character set, Oracle will not do any conversion, so the client was able to store Chinese into the database. But they are planing to migrate these data into a new database with UTF8 character set. How to do this? Database link does not work, so materialized view does not work. A good way is to do the migration via text mode.
Because ociuldr can generate SQL Loader control file automatically, so we can easily do this job by the following command. First set the NLS_LANG to "US7ASCII".
C:\>set NLS_LANG=.US7ASCII
Then unload the data by ociuldr utility with "table=" option, and write the unload messages to file ("unload.log") in append mode for tracking.
C:\>ociuldr user=scott/tiger@ascii query="select * from emp" table=emp field=0x07 record=0x06 log=+unload.log
Assume this table contains some Chinese data, so we set the NLS_LANG as following.
C:\>set NLS_LANG=.ZHS16GBK
Then we can use sqlldr to load the data into UTF8 database, so Oracle will do the GBK to UTF8 conversion (You need create the table first, and may need to adjust some columns precision).
C:\>sqlldr test1/test1@utf8 control=emp_sqlldr.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Sep 19 22:13:29 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 14
I think we can easily write shell script to do this job, both ociuldr and sqlldr are high performance utilities. For huge tables, you should migrate some history data first, then do incremental data migration.