Use csscan to convert character sets of ORACLE databases

 

1. Install Character Set Character

Refer:

 

Http://www.oracle.com/technetwork/database/globalization/index-085942.html

 

Generally. This tool is available when you install the oracle system.

 

Bash-3.00 $ which csscan

/Orahome/oracle/product/11.2.0/dbhome_1/bin/csscan

 

 

 

2.

 

Run the following command:

 

Csscan \ "sys/<syspassword >@< TNSalias> as sysdba \" FULL = y fromchar = WE8MSWIN1252 TOCHAR = AL32UTF8LOG = dbcheck CAPTURE = n array = 1000000 PROCESS = 2

 

 

 

3. Check related output files

 

$ Ls dbch *

 

Dbcheck. err dbcheck. out dbcheck.txt

 

Charcheck. out-list of all scanned items

Charcheck.txt-Summary of scan results

Charcheck. err-an exception in the scan item

 

For some data that may be lost during the conversion process or that cannot be converted into the target character set, you can query the data using the following SQL statement: f

 

SELECT DISTINCTz. owner_name

 

| '.'

 

| Z. table_name

 

| '('

 

| Z. column_name

 

| ')-'

 

| Z. column_type

 

| ''Lossycolumns

 

FROMcsmig. csmv $ errors z

 

WHERE z. error_type = 'data _ loss'

 

ORDER BYLossyColumns;

 

If there is an error with the ORA-01401 or ORA-12899, you can use the import for further processing, first by checking which columns are identified as "Truncation" and then seeing which tables in which columns may be affected. Mportgo p

 

You can use the following query to check which columns are identified as "Truncation" and the minimum length required by the new columns.

Conn/AS sysdba

SET serveroutput ON

DECLARE

Newmaxsz NUMBER;

BEGIN

FOR rec IN

(Select distinct u. owner_name,

U. table_name,

U. column_name,

U. column_type,

U. owner_id,

U. table_id,

U. column_id,

U. column_intid

FROM csmv $ errors u

WHERE u. error_type = 'exceed _ size'

Order by u. owner_name,

U. table_name,

U. column_name

)

LOOP

Select max (cnvsize)

INTO newmaxsz

FROM csm $ errors

WHERE usr # = rec. owner_id

AND obj # = rec. table_id

AND col # = rec. column_id

AND intcol # = rec. column_intid;

 

DBMS_OUTPUT.PUT_LINE (rec. owner_name | '. '| rec. table_name | '(' | rec. column_name | ')-' | rec. column_type | '-' | newmaxsz | 'bytes ');

End loop;

END;

/

 

 

 

4. You can query dbcheck. err to find out possible errors in the conversion process.

 

 

 

Bash-3.00 $ more dbcheck. err

 

DatabaseScan Individual Exception Report

 

 

 

 

 

[DatabaseScan Parameters]

 

 

 

Parameter Value

 

------------------------------------------------------------------------------

 

CSSCANVersion v2.1

 

InstanceName XXX

 

DatabaseVersion 11.2.0.2.0

 

Scantype Full database

 

Scan CHARdata? YES

 

Databasecharacter set WE8MSWIN1252

 

FROMCHAR WE8MSWIN1252

 

TOCHAR AL32UTF8

 

Scan NCHARdata? NO

 

Array fetchbuffer size 1000000

 

Number ofprocesses 2

 

Captureconvertible data? NO

 

------------------------------------------------------------------------------

 

 

 

[DataDictionary individual exceptions]

 

 

 

 

 

[Applicationdata individual exceptions]

 

 

 

 

 

Then, you can query dbcheck. out to handle exceptions.

 

 

 

 

 

If you re-collect the histogram data:

 

$ ORACLE_HOME/nls/csscan/SQL/analyze_histgrm. SQL

 

 

 

EXECDBMS_STATS.DELETE_TABLE_STATS ('serv _ infra', 'SC _t_service_up ');

 

EXECDBMS_STATS.DELETE_TABLE_STATS ('cam ', 'users ');

 

 

 

Clear applications with no correct conversion

 

/Orahome/oracle/product/11.2.0/dbhome_1/apex

 

Connect/as sysdba

 

@ Apxremov. SQL

 

 

 

5. Execute the conversion again:

 

Conn/as sysdba

 

Exec DBMS_SCHEDULER.DISABLE ('gather _ STATS_JOB ')

 

-- Execdbms_workload_repository.modify_snapshot_settings (interval = & gt; 0, retention = & gt; 1440 );

 

 

 

EXECDBMS_STATS.DELETE_TABLE_STATS ('serv _ infra', 'SC _ T_SERVICE_UP ');

 

Selectpolicimestamp-dbms_stats.get_stats_history_availability from dual;

 

Execdbms_stats.alter_stats_history_retention (0 );

 

Clear statistics:

 

ExecDBMS_STATS.PURGE_STATS (systimestamp );

 

Selectcount (*) from SYS. WRI $ _ OPTSTAT_HISTGRM_HISTORY;

 

Css. sh-the actual character set conversion checker (creates dbcheck. * files)

 

Run_conv. SQL-steps to run the conversion

 

 

 

Csscan \ "sys/<syspassword >@< TNSalias> as sysdba \" FULL = y fromchar = WE8MSWIN1252 TOCHAR = AL32UTF8LOG = dbcheck CAPTURE = n array = 1000000 PROCESS = 2

 

 

 

Import data that has not been converted:

 

Impdpfull = n tables = metadata. log_request dumpfile = convert. dmp logfile = meta. logtable_exists_action = append

From henrybai's column