Sunday, June 22, 2014
Rectify lossy data before changing the character set.
As everyone knows the biggest task of changing the character set is clean up the lossy data. Challenge is if we have records in millions means how we are going to deal. This post may help you to rectify the lossy rows.
This will tell us what rows need to rectified from database to change the database to any character set.
Go to $ORACLE_HOME/bin and run the below. Make sure LD_LIBRARY_PATH env is set.
./csscan full=y process=10 (Process parameters depends on the host resource)
It will run for few hours, Also it depends on DB size and OS resource.
Once scan completed, run the below SQL as sys user to display how many lossy records schema level.
set pages 1000
set lines 170
select owner_name,table_name,column_name,count(*) from csmig.csmv$errors group by owner_name,table_name,column_name
oracle has a function called CONVERT, by using this we will be able to correct the lossy data.
Run in SQL developer or run as sql file for better results.
Spool the output and run this update as sql file. This will rectify lossy record .
Here for an example ,
I’m changing the character set from US7ASCII to AL32UTF8.
select 'update '||'"'||owner_name||'".'||table_name||' set '||column_name||' = (select convert ('||column_name||','||''''||'US7ASCII'||''''||','||''''||'AL32UTF8'||''''||') from '||'"'||owner_name||'"'||'.'||table_name||' where rowid = '||''''||DATA_ROWID||''''||')'||' where rowid ='||''''||DATA_ROWID||''''||';'
from csmig.csmv$errors where owner_name not in ('SYS','SQLTXPLAIN')
This will not work if
- Data type of column is LONG
- If column has any referential constraints. (Constraints may disabled and can be enabled after the update)
Rerun the scan to see lossy records have been cleared or not. If there is no lossy records go ahead and start the conversion by running csalter.plb in restricted mode.
Wish you good luck.