Oracle Database Discussions

Hi All,

My DB is in 11.1.0.7.0 and NLS_Characterset is AL32UTF8.

One of my table column is in varchar2 and it contains different characterset (� ). My BO reports are failing because of this character set (BO character set is in AL32UTF8)

Using covert function one set/pattern of data i converted to the original characterset. [ Select column1, convert(column2, 'AL32UTF8', 'WE8ISO8859P1') FROM xyz ]
I also create a new table with same filed as nvarchar2 and inserted the data from the coverted records. From the new table BO reports are fine.

Now i wanted to move complete table data (around 20 GB, not sure how many rows and and what all types of charater set having non-UTF8 character) to the new table with nvarchar2. Problem is that Since it is worldwide application there are many types of characterset involved in the database. Converting each one like previous query won't be possible and accurate.

Is there any way i can perform this task
1. Identify all the rows that not UTF8 format.
2. Convert to a correct character set and move to a new table with nvarchar2 that by default will take care of this (I hope so). Pelase note that i can't remove the charaters that have non-utf8 format
So looks like diffrent character set data is in UTF8 format and we can't really see the extact charater set since it is in AL32UTF8. we need to convert back to the original character set and save it in nvarchar2 format.

Please share your suggestions. Thanks in advance.

Regards,
Anto.