利用csscan转换ORACLE数据库的字符集

1.安 装Character Set Scanner

参考：

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

一般地。安装oracle 系统就会有这个工具。

bash-3.00$ which csscan
/orahome/oracle/product/11.2.0/dbhome_1/bin/csscan

2.

执行下面的命令：

Csscan\"sys/<syspassword>@<TNSalias> as sysdba\" FULL=YFROMCHAR=WE8MSWIN1252TOCHAR=AL32UTF8LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2

3. 检查相关的输出文件

$ ls dbch*

dbcheck.err dbcheck.outdbcheck.txt

charcheck.out – 所有经过扫描的扫描项列表

charcheck.txt – 扫描结果的总结

charcheck.err – 扫描项中的异常

对于某些可能在转换过程中丢失的数据或不能被转换成目标字符集的数据可以通过下面的SQL语句查询得到：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;

如果有 ORA-01401或者 ORA-12899的错误，你可以利用利用import再做处理，首先通过查看哪些列被标识成了”Truncation”，然后看哪些表中的列可能会被影响了。mportgo p

你可通过下面的查询查看哪些列被标识了 "Truncation" 和新列所需要的最小长度。
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. 可以通过查询dbcheck.err来了解转换过程可能发生的错误。

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]

然后通过查询dbcheck.out对发生的异常进行处理。

如果重新收集直方图的数据：

$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');

清除没有正确转换的应用

/orahome/oracle/product/11.2.0/dbhome_1/apex

connect /as sysdba

@apxremov.sql

5.再次执行转换：

Conn/ as sysdba

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB')

--execdbms_workload_repository.modify_snapshot_settings(interval => 0,retention=>1440);

EXECDBMS_STATS.DELETE_TABLE_STATS ('SERV_INFRA', 'SC_T_SERVICE_UP');

selectsystimestamp - dbms_stats.get_stats_history_availability from dual;

execdbms_stats.alter_stats_history_retention(0);

清除统计信息：

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=YFROMCHAR=WE8MSWIN1252TOCHAR=AL32UTF8LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2

导入没有转换的数据：

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