oracle資料庫字符集characterset遷移及變更之csscan及csalter.plb系列三

背景

本文主要熟悉下如何用csscan以及csalter指令碼變更資料庫字符集，進一步理解如何調整資料庫字符集的知識。
之前的相關文章連結：
oracle10g_csscan_更變資料庫字符集及國家字符集的工具測試
http://blog.itpub.net/9240380/viewspace-614070/

csscan: error while loading shared libraries: libclntsh.so.10.1
http://blog.itpub.net/9240380/viewspace-766685/

分析思路

結論

1,csscan結合csalter.plb可以調整資料庫的資料庫字符集
2,執行完csscan後，會在當前目錄產生3個檔案：
[oracle@seconary admin]$ ll -lrht scan*
-rw-r--r-- 1 oracle oinstall 9.2K Nov 30 01:45 scan.txt ---需要關注的檔案
-rw-r--r-- 1 oracle oinstall 87K Nov 30 01:45 scan.out ---這個就是類似LOG之類的檔案
-rw-r--r-- 1 oracle oinstall 5.0K Nov 30 01:45 scan.err --需要關注的檔案

具體關於這幾個檔案，請見下述測試

只要把上述2個關注檔案裡面的錯誤解決完畢，方可繼續執行csalter.plb,否則會報如下錯誤
Checking data validity...
Unrecognized convertible data found in scanner result ---好像轉換不能成功

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

3，csscan的使用者和exp同理，也分為資料庫級，使用者級，表級，為了方便執行，可以採用命令列模式執行：
csscan userid=\'sys/system as sysdba\' full=y tochar=AL32UTF8 array=1024000 process=10

4,具體分析思路為：
[oracle@seconary admin]$ more scan.txt
Data Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

確認上述檔案中列Convertible的內容為0或空，否則在執行csalter.plb會上述第2提到的錯誤
因為這步我沒有理解到位，消耗了1個小時左右的時間

4，後來是查閱MOS文章：oracle_更改資料庫字符集 (NLS_CHARACTERSET) ，受到啟發，用上述第4提示的涉及Convertible的表或相關資料，把這些資料EXP出來
然後再刪除這些資料，接著重新執行csscan,直到cscsan.txt中的Convertible為空，最後再執行csalter.plb，就成功了，然後匯入EXP的資料
不過記得一定要重啟資料庫

這步可以說是我認為最有價值的地方

5，再引申一下，其實從csalter.plb執行輸出日誌也會有對應的提示，如下：
Checking data validity...
Unrecognized convertible data found in scanner result ---看到沒 convertible，Unrecognized，這裡就很重要

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

所以說一定要深入理解輸出日誌的報錯，太重要了，太重要了

6,關於csscan執行產生的3個日誌，理解還不夠，還要進一步理解，結合實戰案例，解決問題

分析思路

測試

SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ --------------------------------------------------
NLS_CHARACTERSET ZHS16GBK

SQL> show user
USER is "SCOTT"

SQL> select a,dump(a) from t_charset where rownum=1;

A DUMP(A)
---------- --------------------------------------------------
我們 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32

---檢視csscan用法
[oracle@seconary admin]$ csscan -help

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:32:25 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:

Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"

Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:

Example:
CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3

Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database --整庫
USER yes owner of tables to be scanned --特定使用者
TABLE yes list of tables to scan --特定表
COLUMN yes list of columns to scan --特定列
EXCLUDE list of tables to exclude from scan --排除的表
TOCHAR yes new database character set name ----目標資料庫字符集
FROMCHAR current database character set name
TONCHAR new national character set name --同上
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer --獲取資料的ARRAY大小
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files --日誌
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection --開啟語言及字符集檢測
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.
[oracle@seconary admin]$

---提示CSS-00107: Character set migration utility schema not installed好像一個什麼使用者示例資料沒安裝
[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:37:02 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

根據我之前的文章：
http://blog.itpub.net/9240380/viewspace-614070/

可解決上述的問題
[oracle@seconary admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:39:05 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql

中間內容略
View created.

View created.

View created.

Grant succeeded.

Grant succeeded.

--重新執行CSSCAN即可
[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:40:19 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Current database character set is ZHS16GBK.
Enter new database character set name: > AL32UTF8 --輸入目標字符集
Enter array fetch buffer size: 1024000 > 可見與csscan命令列的引數可以對應起來，這裡是互動式方式，道理同exp

Enter number of scan processes to utilize(1..64): 1 > 10

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAADfAABAAAAXYAAA] ---可見會掃描所有使用者的表
. process 7 scanning SYS.SOURCE$[AAAADfAABAAAR+AAAA]
. process 3 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA]

. process 3 scanning SYS.OBJ$[AAAAASAABAAAADwAAA]

. process 7 scanning APEX_030200.WWV_FLOW_CALS[AAARPaAACAAAKHgAAA]
. process 2 scanning SCOTT.EMP[AAAR3xAAEAAAACQAAA]
. process 10 scanning SCOTT.T_VARCHAR[AAAR7TAAEAAAACoAAA] --這個就是我們的業務表
. process 8 scanning SYS.FILE$[AAAAARAABAAAADoAAA]
. process 1 scanning SYS.PROPS$[AAAABiAABAAAAMgAAA]
. process 4 scanning SYS.TRIGGERJAVAS$[AAAACmAABAAAAUoAAA]
. process 6 scanning SYS.PENDING_TRANS$[AAAAEHAABAAAAdQAAA]
. process 5 scanning SYS.MAP_FILE_EXTENT$[AAAAERAABAAAAeIA

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

--CSSCAN會在當前目錄生成相關日誌
[oracle@seconary admin]$ ll -lrht scan*
-rw-r--r-- 1 oracle oinstall 9.2K Nov 30 01:45 scan.txt
-rw-r--r-- 1 oracle oinstall 87K Nov 30 01:45 scan.out
-rw-r--r-- 1 oracle oinstall 5.0K Nov 30 01:45 scan.err

---可見scan.txt是一個掃描後形成的報告，包括掃描程式消耗的程式以及開始及結束時間，以及每個表空間遷移後空間變化的情況，以及掃描定製的引數，即針對於CSSCAN
以及各種字典表及應用表，基於不同資料型別分組的，資料遷移的情況，這個非常細分
[oracle@seconary admin]$ more scan.txt
Database Scan Summary Report

Time Started : 2015-11-30 01:41:47
Time Completed: 2015-11-30 01:45:41

Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2015-11-30 01:42:32 2015-11-30 01:45:36
2 2015-11-30 01:42:36 2015-11-30 01:45:36
3 2015-11-30 01:42:34 2015-11-30 01:45:36
4 2015-11-30 01:42:36 2015-11-30 01:45:36
5 2015-11-30 01:42:35 2015-11-30 01:45:36
6 2015-11-30 01:42:35 2015-11-30 01:45:36
7 2015-11-30 01:42:34 2015-11-30 01:45:36
8 2015-11-30 01:42:34 2015-11-30 01:45:36
9 2015-11-30 01:42:34 2015-11-30 01:45:36
10 2015-11-30 01:42:36 2015-11-30 01:45:36
---------- -------------------- --------------------

[Database Size]

Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 670.81M 9.19M 680.00M .00K
SYSAUX 461.63M 28.38M 490.00M .00K
UNDOTBS1 5.81M 69.19M 75.00M .00K
TEMP .00K .00K .00K .00K
USERS 21.63M 2.13M 23.75M 3.00M
------------------------- --------------- --------------- --------------- ---------------
Total 1,159.88M 108.88M 1,268.75M 3.00M

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 3,281,790 34 0 0
CHAR 2,594 0 0 0
LONG 239,383 0 0 0
VARRAY 42,147 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,565,914 34 0 0
Total in percentage 99.999% 0.001% 0.000% 0.000%

The data dictionary can not be safely migrated using the CSALTER script

XML CSX Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 495 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 495 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 3,010,086 1 0 0
CHAR 47 1,048,577 0 0
LONG 0 0 0 0
VARRAY 6,912 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,017,045 1,048,578 0 0
Total in percentage 74.209% 25.791% 0.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

Data Dictionary Tables:

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.AUD$ 5 0 0
SYS.USER$ 1 0 0
SYS.WRH$_SQLSTAT 28 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SCOTT.T_CHARSET 524,289 0 0
SCOTT.T_CHARSET_BAK 524,288 0 0
SCOTT.T_VARCHAR 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

Data Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.AUD$|USERID 5 0 0
SYS.USER$|NAME 1 0 0
SYS.WRH$_SQLSTAT|PARSING_SCHEMA_NAME 28 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SCOTT.T_CHARSET|A 524,289 0 0
SCOTT.T_CHARSET_BAK|A 524,288 0 0
SCOTT.T_VARCHAR|A 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
SYS.I_USER1 on SYS.USER$(NAME)
-----------------------------------------------------------------------------------------
[oracle@seconary admin]

--scan.err記錄遷移失敗一些記錄資訊，全與我之前測試建立的包含中文字元的資料庫使用者名稱相關
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

User : SYS
Table : WRH$_SQLSTAT
Column: PARSING_SCHEMA_NAME
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 11

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAR5hAACAAAOXpAAU convertible ZXY翟z
AAAR5hAACAAAOXpAAW convertible ZXY翟z
AAAR5hAACAAAOXpAAY convertible ZXY翟z
AAAR5hAACAAAOXqAAA convertible ZXY翟z
AAAR5hAACAAAOXqAAB convertible ZXY翟z
AAAR5hAACAAAOXqAAD convertible ZXY翟z
AAAR5hAACAAAOXqAAE convertible ZXY翟z
AAAR5hAACAAAOXqAAI convertible ZXY翟z
AAAR5hAACAAAOXqAAN convertible ZXY翟z
AAAR5hAACAAAOXqAAS convertible ZXY翟z
AAAR5hAACAAAOXqAAT convertible ZXY翟z
AAAR5hAACAAAOXqAAU convertible ZXY翟z
AAAR5hAACAAAOXqAAa convertible ZXY翟z
AAAR5hAACAAAOXqAAc convertible ZXY翟z
AAAR5hAACAAAOXrAAD convertible ZXY翟z
AAAR5hAACAAAOXrAAE convertible ZXY翟z
AAAR5hAACAAAOXrAAI convertible ZXY翟z
AAAR5hAACAAAOXrAAL convertible ZXY翟z
AAAR5hAACAAAOXrAAN convertible ZXY翟z
AAAR5hAACAAAOXrAAQ convertible ZXY翟z
AAAR5hAACAAAOXrAAR convertible ZXY翟z
AAAR5hAACAAAOXrAAS convertible ZXY翟z
AAAR5hAACAAAOXsAAF convertible ZXY翟z
AAAR5hAACAAAOXsAAL convertible ZXY翟z
AAAR5hAACAAAOXsAAN convertible ZXY翟z
AAAR5hAACAAAOXsAAP convertible ZXY翟z
AAAR5hAACAAAOXsAAR convertible ZXY翟z
AAAR5hAACAAAOXsAAW convertible ZXY翟z
------------------ ------------------ ----- ------------------------------

User : SYS
Table : USER$
Column: NAME
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 27

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAAKAABAAAADVAAE convertible ZXY翟z
------------------ ------------------ ----- ------------------------------

User : SYS
Table : AUD$
Column: USERID
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 9

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAF/AABAAAAj5AAk convertible ZXY翟z
AAAAF/AABAAAAj5AAl convertible ZXY翟z
AAAAF/AABAAAAj5AAm convertible ZXY翟z
AAAAF/AABAAAAj5AAn convertible ZXY翟z
AAAAF/AABAAAAj5AAo convertible ZXY翟z
------------------ ------------------ ----- ------------------------------

[Application data individual exceptions]

[oracle@seconary admin]$

---scan.out就是真正的CSSCAN的輸出日誌了
[oracle@seconary admin]$ more scan.out

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:40:19 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Current database character set is ZHS16GBK.

Enter new database character set name: >
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAADfAABAAAAXYAAA]
. process 7 scanning SYS.SOURCE$[AAAADfAABAAAR+AAAA]
. process 3 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA]
. process 8 scanning SYS.METHOD$[AAAAH3AABAAAAzgAAA]
. process 9 scanning SYS.ATTRIBUTE$[AAAAH3AABAAAAzgAAA]
. process 6 scanning SYS.TYPE$[AAAAH3AABAAAAzgAAA]
. process 5 scanning SYS.LIBRARY$[AAAAACAABAAAACQAAA]

ORA-30967: operation directly on the Path Table is disallowed

CSS-00144: failed to scan table XDB.PRIN_PT

ORA-30967: operation directly on the Path Table is disallowed

CSS-00144: failed to scan table XDB.SC_PT
---可見ora-30967與域索引相關，我們不研究這個，這是ORACLE一種技術
[oracle@seconary admin]$ oerr ora 30967
30967, 00000, "operation directly on the Path Table is disallowed"
// *Cause: illegal operation on secondary objects of a domain index
// *Action: Try appropriate operation on the domain index.

注意：到這裡其實資料庫的字符集沒有調整為al32utf8,好了，我們繼續操作
[oracle@seconary admin]$ pwd
/oracle/product/11.2.0/db_1/rdbms/admin
[oracle@seconary admin]$ ll csal*
-rw-r--r-- 1 oracle oinstall 6431 Aug 3 2009 csalter.plb
[oracle@seconary admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:59:20 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @@csalter.plb

0 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary. 這裡很重要，ORACLE提示會更新資料字典的內容，所在操作之前一定要備份資料庫，不然萬一失敗就玩大了
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
Unrecognized convertible data found in scanner result ---好像轉換不能成功

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

---可見重啟庫仍然無效，源字符集zhs16gbk並未轉化指定的al32utf8
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 956303352 bytes
Database Buffers 687865856 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK

基於上述CSSCAN中錯誤提示，解決包含中文字元的使用者
SQL> select username from dba_users;

USERNAME
------------------------------
SYS
SYSTEM
ZXY翟z
SCOTT
OUTLN
CSMIG
MGMT_VIEW
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS

USERNAME
------------------------------
DBSNMP
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS

USERNAME
------------------------------
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

32 rows selected.

SQL> drop user ZXY翟z cascade;

User dropped.

[oracle@seconary admin]$ pwd
/oracle/admin

[oracle@seconary admin]$ ll scan*

-rw-r--r-- 1 oracle oinstall 5043 Nov 30 01:45 scan.err
-rw-r--r-- 1 oracle oinstall 89077 Nov 30 01:45 scan.out
-rw-r--r-- 1 oracle oinstall 9340 Nov 30 01:45 scan.txt

[oracle@seconary admin]$ rm -rf scan*

---發現還是有殘留的壞資料，想辦法移除這些壞資料
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

User : SYS
Table : WRH$_SQLSTAT
Column: PARSING_SCHEMA_NAME ---提示你是哪個表的哪個列有問題
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 11

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAR5hAACAAAOXpAAU convertible ZXY翟z
AAAR5hAACAAAOXpAAW convertible ZXY翟z
AAAR5hAACAAAOXpAAY convertible ZXY翟z
AAAR5hAACAAAOXqAAA convertible ZXY翟z
AAAR5hAACAAAOXqAAB convertible ZXY翟z
AAAR5hAACAAAOXqAAD convertible ZXY翟z
AAAR5hAACAAAOXqAAE convertible ZXY翟z
AAAR5hAACAAAOXqAAI convertible ZXY翟z
AAAR5hAACAAAOXqAAN convertible ZXY翟z
AAAR5hAACAAAOXqAAS convertible ZXY翟z
AAAR5hAACAAAOXqAAT convertible ZXY翟z
AAAR5hAACAAAOXqAAU convertible ZXY翟z
AAAR5hAACAAAOXqAAa convertible ZXY翟z
AAAR5hAACAAAOXqAAc convertible ZXY翟z
AAAR5hAACAAAOXrAAD convertible ZXY翟z
AAAR5hAACAAAOXrAAE convertible ZXY翟z
AAAR5hAACAAAOXrAAI convertible ZXY翟z
AAAR5hAACAAAOXrAAL convertible ZXY翟z
AAAR5hAACAAAOXrAAN convertible ZXY翟z
AAAR5hAACAAAOXrAAQ convertible ZXY翟z
AAAR5hAACAAAOXrAAR convertible ZXY翟z
AAAR5hAACAAAOXrAAS convertible ZXY翟z
AAAR5hAACAAAOXsAAF convertible ZXY翟z
AAAR5hAACAAAOXsAAL convertible ZXY翟z
AAAR5hAACAAAOXsAAN convertible ZXY翟z
AAAR5hAACAAAOXsAAP convertible ZXY翟z
AAAR5hAACAAAOXsAAR convertible ZXY翟z
AAAR5hAACAAAOXsAAW convertible ZXY翟z
------------------ ------------------ ----- ------------------------------

User : SYS
Table : AUD$
Column: USERID ---這個列有問題
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 9

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAF/AABAAAAj5AAk convertible ZXY翟z
AAAAF/AABAAAAj5AAl convertible ZXY翟z
AAAAF/AABAAAAj5AAm convertible ZXY翟z
AAAAF/AABAAAAj5AAn convertible ZXY翟z
AAAAF/AABAAAAj5AAo convertible ZXY翟z
------------------ ------------------ ----- ------------------------------

[Application data individual exceptions]

[oracle@seconary admin]$

其實也可以解決更新此列，不過這是底層表，和其它表的關聯非常複雜，不能簡單的UPDATE，當然DELETE也不是一種非常好的方法
SQL> select userid from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');

USERID
------------------------------
ZXY翟z
ZXY翟z
ZXY翟z
ZXY翟z
ZXY翟z

SQL> delete from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');

5 rows deleted.

SQL> commit;

Commit complete.

SQL> select userid from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');

no rows selected

SQL> delete from wrh$_sqlstat where rowid in
2 ('AAAR5hAACAAAOXpAAU',
3 'AAAR5hAACAAAOXpAAW',
4 'AAAR5hAACAAAOXpAAY',
5 'AAAR5hAACAAAOXqAAA',
6 'AAAR5hAACAAAOXqAAB',
7 'AAAR5hAACAAAOXqAAD',
8 'AAAR5hAACAAAOXqAAE',
9 'AAAR5hAACAAAOXqAAI',
10 'AAAR5hAACAAAOXqAAN',
11 'AAAR5hAACAAAOXqAAS',
12 'AAAR5hAACAAAOXqAAT',
13 'AAAR5hAACAAAOXqAAU',
14 'AAAR5hAACAAAOXqAAa',
15 'AAAR5hAACAAAOXqAAc',
16 'AAAR5hAACAAAOXrAAD',
17 'AAAR5hAACAAAOXrAAE',
'AAAR5hAACAAAOXrAAI',
19 'AAAR5hAACAAAOXrAAL',
20 'AAAR5hAACAAAOXrAAN',
'AAAR5hAACAAAOXrAAQ',
22 'AAAR5hAACAAAOXrAAR',
23 'AAAR5hAACAAAOXrAAS',
24 'AAAR5hAACAAAOXsAAF',
25 'AAAR5hAACAAAOXsAAL',
26 'AAAR5hAACAAAOXsAAN',
27 'AAAR5hAACAAAOXsAAP',
28 'AAAR5hAACAAAOXsAAR',
29 'AAAR5hAACAAAOXsAAW');

28 rows deleted.

SQL> commit;

Commit complete.

[oracle@seconary admin]$ rm -rf scan*
[oracle@seconary admin]$

---這下沒有錯誤了
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

[Application data individual exceptions]

還是沒有轉換到指定的資料庫字符集
SQL> @@csalter.plb

0 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
Unrecognized convertible data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 922748920 bytes
Database Buffers 721420288 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK

SQL>

SQL> alter system enable restricted session;

System altered.

還是不成功
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
Unrecognized convertible data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

--多執行幾次上述的CSSCAN命令（調整互動式為命令列式，方便多次執行）,看來不是多次執行CSSCAN可以解決的
[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y tochar=AL32UTF8 array=1024000 process=10

Checking data validity...
Unrecognized convertible data found in scanner result

PL/SQL procedure successfully completed.

Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.

換個思路，分析下 @@csalter.plb指令碼內容，也沒有看到有價值的內容，因為全是加密的程式碼

繼續換個思路，基於MOS一篇文章：oracle_更改資料庫字符集 (NLS_CHARACTERSET)

找到哪些資料可能會在遷移時產生轉換，即字符集轉換，把這些資料匯出來，然後刪除這些表或資料，接著執行CSALTER指令碼
[oracle@seconary admin]$ more scan.txt

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ---------------- ---可知是哪些表可能會發生資料轉換（基於字符集轉換）
SCOTT.T_CHARSET 524,289 0 0
SCOTT.T_CHARSET_BAK 524,288 0 0
SCOTT.T_VARCHAR 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

[oracle@seconary admin]$ exp userid=scott/system owner=scott file=exp_scott_1.dmp

Export: Release 11.2.0.1.0 - Production on Mon Nov 30 03:38:10 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table T_CHARSET 524289 rows exported
. . exporting table T_CHARSET_BAK 524288 rows exported
. . exporting table T_CLOB 1 rows exported
. . exporting table T_VARCHAR 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@seconary admin]$

SQL> drop user scott cascade;

User dropped.

[oracle@seconary admin]$ cd $ORACLE_BASE/admin
[oracle@seconary admin]$ ll
total 28944
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:58 charzhs
-rw-r--r-- 1 oracle oinstall 16384 Nov 30 00:32 exp_charzhs_clob.dmp
-rw-r--r-- 1 oracle oinstall 7356416 Nov 29 22:01 exp_charzhs.dmp
-rw-r--r-- 1 oracle oinstall 16384 Nov 29 23:12 exp_charzhs_varchar.dmp
-rw-r--r-- 1 oracle oinstall 14696448 Nov 30 03:39 exp_scott_1.dmp
-rw-r--r-- 1 oracle oinstall 16384 Nov 30 00:56 exp_user1.dmp
-rw-r--r-- 1 oracle oinstall 7356416 Nov 29 21:53 exp_utf8char.dmp
drwxr-x--- 5 oracle oinstall 4096 Apr 17 2014 guowang
drwxr-x--- 5 oracle oinstall 4096 Sep 11 00:44 newb
drwxr-x--- 5 oracle oinstall 4096 Nov 28 16:45 onetime
-rw-r--r-- 1 oracle oinstall 1357 Nov 30 03:16 scan.err
-rw-r--r-- 1 oracle oinstall 88905 Nov 30 03:16 scan.out
-rw-r--r-- 1 oracle oinstall 8693 Nov 30 03:16 scan.txt
drwxr-xr-x 3 oracle oinstall 4096 Aug 28 2013 second
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:35 utf8char
[oracle@seconary admin]$ rm -rf scan*
[oracle@seconary admin]$

重新執行csscan
[oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y tochar=AL32UTF8 array=1024000 process=10

直到scan.txt中Convertible對應的值為非0
[oracle@seconary admin]$ more scan.txt
Data Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

這下執行csalter.plb成功了
[oracle@seconary admin]$ cd /oracle/product/11.2.0/db_1/rdbms/admin
[oracle@seconary admin]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 03:47:31 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @@csalter.plb

0 rows created.

Function created.

Function created.

Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
begin converting system objects

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database ---CSALTER完後，一定要重啟庫，引申：命令執行過程中的輸出資訊日誌一定要仔細理解，這個非常重要，如果理解不對路，可能會消耗大多無謂到時間到其它分析思路上去

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.

Procedure dropped.

SQL>

SQL> col parameter for a30
SQL> col value for a30
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 939526136 bytes
Database Buffers 704643072 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8

SQL> create user scott identified by system account unlock;

User created.

SQL> grant resource,connect to scott;

Grant succeeded.

報如下錯誤
[oracle@seconary admin]$ imp userid=system/system fromuser=scott touser=scott file=exp_scott_1.dmp rows=n

Column 1 我們
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered

SQL> desc t_charset;
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(10)

基於前文：oracle資料庫字符集characterset遷移及變更系列二
http://blog.itpub.net/9240380/viewspace-1850079/

調整引數blank_trimming=true，不過要重啟庫方可生效，即移除列尾部的空格字元

SQL> show parameter blank_trimming

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
blank_trimming boolean
FALSE
SQL> alter system set blank_trimming=true scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 939526136 bytes
Database Buffers 704643072 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

[oracle@seconary admin]$ imp userid=system/system fromuser=scott touser=scott file=exp_scott_1.dmp ignore=y

Import: Release 11.2.0.1.0 - Production on Mon Nov 30 04:09:02 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
中間略
Column 3 CLERK
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated --報這個錯是正常的，因為在這前已經匯入了EMP表的記錄，所以可以忽略
Column 1 7902
Column 2 FORD
Column 3 ANALYST
Column 4 7566
Column 5 03-DEC-1981:00:00:00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7934
Column 2 MILLER
Column 3 CLERK
Column 4 7782
Column 5 23-JAN-1982:00:00:00
Column 6 1300
Column 7
Column 8 10 0 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "T_CHARSET" 524289 rows imported
. . importing table "T_CHARSET_BAK" 524288 rows imported
. . importing table "T_CLOB" 1 rows imported
. . importing table "T_VARCHAR" 1 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[oracle@seconary admin]$

SQL> select a,dump(a) from t_charset where rownum<=10;

A DUMP(A)
-------------------- ------------------------------------------------------------------------------------------
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我們 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32

10 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1850149/，如需轉載，請註明出處，否則將追究法律責任。