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.
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.
CSSCAN:
This will tell us
what rows need to rectified from database to change the database to any
character set.
what rows need to rectified from database to change the database to any
character set.
RUNNING CSSCAN:
Go to $ORACLE_HOME/bin and run the below. Make sure
LD_LIBRARY_PATH env is set.
LD_LIBRARY_PATH env is set.
./csscan full=y process=10 (Process parameters depends on
the host resource)
the host resource)
It will run for few hours, Also it depends on DB size and OS
resource.
resource.
Once scan completed, run the below SQL as sys user to
display how many lossy records schema level.
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
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.
lossy data.
Run in SQL developer or run as sql file for better
results.
results.
Spool the output
and run this update as sql file. This will rectify lossy record .
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.
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||””||’;’
‘||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
Data type of column is LONG
–
If column has any referential constraints.
(Constraints may disabled and can be enabled after the update)
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.
not. If there is no lossy records go
ahead and start the conversion by running csalter.plb in restricted mode.
Wish you good luck.
Recent Posts