Oracle Character Set 변환(7): 5.4. KO16MSWIN949 환경 CSSCAN 실행 결과

KO16MSWIN949 환경 CSSCAN 실행 결과 를 살펴본다. schema 생성, directory 생성, csscan 실행 결과 파일 scan.txt, scan.out, scan.err 내용, csscan 용도에 대한 내용을 확인할 수 있다.

5.4. KO16MSWIN949 환경 CSSCAN 실행 결과

5.4.1. schema 생성

KO16MSWIN949 instance에 아래 스크립트를 실행하여 csscan 실행을 위한 schema를 생성한다.

-- MSWIN949
sqlplus sys/________@oramswin949 as sysdba
@%ORACLE_HOME%\rdbms\admin\csminst.sql
sqlplus sys/________@oramswin949 as sysdba
ALTER USER CSMIG DEFAULT TABLESPACE USERS;
EXIT

• 3행: csminst.sql을 실행하여 csmig user(schema)와 csscan 실행에 필요한 테이블등의 객체를 생성한다. 이때 csmig user의 기본 tablespace는 system으로 지정된다.
• 5행: csmig user의 기본 tablespace를 users로 변경한다. users가 아닌 저장공간이 충분한 다른 tablespace로 변경해도 된다.

5.4.2. directory 생성

csscan 실행결과를 저장할 directory를 생성하고, 해당 directory로 이동한다.

mkdir D:\Temp\csscan\MSWIN949
cd D:\Temp\csscan\MSWIN949

5.4.3. csscan 실행

Windows 명령 프롬프트에서 다음 명령어로 csscan을 실행한다.

csscan \"sys/________@oramswin949 as sysdba\" full=y tochar=AL32UTF8

parameter는 두 개를 지정하였다.

• full: 데이터베이스 전체를 대상으로 scan하도록 Y 지정
  • 참고로, user parameter로 scan 대상 user를 지정하거나 table, column parameter로 scan 대상을 상세하게 지정할 수 있음
• tochar: 변환할 목표 character set인 AL32UTF8 지정 (CHAR, VARCHAR2, CLOB, LONG type scan)
  • 참고로, NCHAR, NVARCHAR2, NCLOB type을 scan하려면 tonchar parameter 지정 필요함

5.4.4. csscan 실행결과 확인-scan.txt(결과 요약 보고서)

KO16MSWIN949 테스트 환경에서 scan.txt의 전체 내용은 다음과 같다.

Database Scan Summary Report

Time Started  : 2014-05-07 20:21:51
Time Completed: 2014-05-07 20:22:46

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
​​​​​​​​​1  2014-05-07 20:22:04  2014-05-07 20:22:45
---------- -------------------- --------------------

[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            563.25M         136.75M         700.00M            .00K
SYSAUX                            360.19M         239.81M         600.00M           2.00K
UNDOTBS1                           19.25M         465.75M         485.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                               1.81M           3.19M           5.00M          39.00K
------------------------- --------------- --------------- --------------- ---------------
Total                             944.50M         845.50M       1,790.00M          42.00K

[Database Scan Parameters]

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

[Scan Summary]

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

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     2,566,115               36                0                0
CHAR                               354                0                0                0
LONG                           182,178                0                0                0
VARRAY                          39,205                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        2,787,852               36                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                           493                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              493                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     2,579,942           14,635              309                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        2,579,942           14,635              309                0
Total in percentage             99.424%           0.564%           0.012%           0.000%

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

Data Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.COM$                                                         11                0                0
SYS.ERROR$                                                       10                0                0
SYS.JOB$                                                          2                0                0
SYS.SCHEDULER$_JOB                                                8                0                0
SYS.SCHEDULER$_PROGRAM                                            5                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

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

Application Data:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_UPGRADE_PROGRESS                            89                0                0
LEG.SUB_MON_STAT                                             14,544              309                0
LEG.Z_TEST                                                        2                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

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

Data Dictionary Tables:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.COM$|COMMENT$                                                11                0                0
SYS.ERROR$|TEXT                                                  10                0                0
SYS.JOB$|NLSENV                                                   2                0                0
SYS.SCHEDULER$_JOB|NLS_ENV                                        8                0                0
SYS.SCHEDULER$_PROGRAM|NLS_ENV                                    5                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

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

Application Data:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_UPGRADE_PROGRESS|UPGRADE_ERR                89                0                0
LEG.SUB_MON_STAT|COMMT                                            0                1                0
LEG.SUB_MON_STAT|LINE_NUM                                     7,286              140                0
LEG.SUB_MON_STAT|SUB_STA_NM                                   7,258              168                0
LEG.Z_TEST|VAL                                                    2                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)                                                         
-----------------------------------------------------------------------------------------
SYS.I_SCHEDULER_JOB4 on SYS.SCHEDULER$_JOB(SYS_NC00056$)
SYS.I_SCHEDULER_JOB1 on SYS.SCHEDULER$_JOB(SYS_NC00055$)
-----------------------------------------------------------------------------------------

이 중에서 “Application Data Conversion Summary” 내용이 Oracle System Data가 아닌 사용자가 생성한 Data에 type별로 scan한 내용이다.

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     2,579,942           14,635              309                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        2,579,942           14,635              309                0
Total in percentage             99.424%           0.564%           0.012%           0.000%

위 내용을 해석하면 다음과 같다.

• VARCHAR2 type에 대해서 AL32UTF8로 변경 시 2,579,942개의 value는 변경 없이(Changeless) 그대로 전환될 수 있고, 14,635개의 value는 변경 가능(Convertable)하며, 309개의 value가 잘려(Truncation)지고, 손실/깨짐(Lossy)이 발생하는 value는 없음
• 그외 CHAR, LONG, VARRAY type의 데이터는 변환 대상이 없음

위 내용에서 Changeless, Convertible, Truncation, Lossy에 대한 설명은 이전 글에 작성해 두었고, 아래에 한번 더 표기해 둔다.

Oracle Character Set 변환(5) – 5.2. CSSCAN 사용 방법

Status	Description
Changeless	Data remains the same in the new character set – character set 변환시 데이터 변경없음
Convertible	Data can be successfully converted to the new character set – character set 변환시 데이터 변경 가능함
Truncation	Data will be truncated if conversion takes place – character set 변환시 데이터 잘림
Lossy	Character data will be lost if conversion takes place – character set 변환시 데이터 손실됨(깨짐)

“Distribution of Convertible, Truncated and Lossy Data by Table”, “Distribution of Convertible, Truncated and Lossy Data by Column” 내용은 각 테이블과 컬럼별로 scan한 결과이다.

아래에 파일의 일부 내용을 발췌해 둔다.

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

...
Application Data:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_UPGRADE_PROGRESS                            89                0                0
LEG.SUB_MON_STAT                                             14,544              309                0
LEG.Z_TEST                                                        2                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

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

...
Application Data:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_UPGRADE_PROGRESS|UPGRADE_ERR                89                0                0
LEG.SUB_MON_STAT|COMMT                                            0                1                0
LEG.SUB_MON_STAT|LINE_NUM                                     7,286              140                0
LEG.SUB_MON_STAT|SUB_STA_NM                                   7,258              168                0
LEG.Z_TEST|VAL                                                    2                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

위 내용을 해석하면 다음과 같다.

• LEG.SUB_MON_STAT 테이블은 AL32UTF8로 변경 시 14,544개의 value는 정상적으로 변환(Convertible)되고, 309개의 value가 잘려(Truncation)지며, 손실/깨짐(Lossy)이 발생하는 value는 없음. 각 컬럼별로 다음과 같음
  • COMMT: AL32UTF8로 변경 시 정상적으로 변환되는 value는 없고, 1개의 value가 잘려(Truncation)지며, 손실/깨짐(Lossy)이 발생하는 value는 없음
  • LINE_NUM: AL32UTF8로 변경 시 7,286개의 value는 정상적으로 변환(Convertible)되고, 140개의 value가 잘려(Truncation)지며, 손실/깨짐(Lossy)이 발생하는 value는 없음
  • SUB_STA_NM: AL32UTF8로 변경 시 7,258개의 value는 정상적으로 변환(Convertible)되고, 168개의 value가 잘려(Truncation)지며, 손실/깨짐(Lossy)이 발생하는 value는 없음
• LEG.Z_TEST 테이블은 AL32UTF8로 변경 시, 2개의 value가 정상적으로 변환(Convertible)되고, 잘려(Truncation)지거나 손실/깨짐(Lossy)이 발생하는 value는 없음. VAL 컬럼이 이에 해당함.

5.4.5. csscan 실행결과 확인-scan.out(scan 대상 테이블 정보)

scan.out 파일에서는 scan 대상 테이블 목록을 확인할 수 있다.

Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Wed May 7 20:21:48 2014

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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 1 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA]
. process 1 scanning SYS.TYPE$[AAAAH3AABAAAAzgAAA]
. process 1 scanning SYS.ATTRIBUTE$[AAAAH3AABAAAAzgAAA]
...
. process 1 scanning MDSYS.SDO_COORD_REF_SYS[AAAPKtAACAAAEtYAAA]
. process 1 scanning XDB.XDB$H_LINK[AAANh/AACAAACUwAAA]
. process 1 scanning SYS.SYS$SERVICE_METRICS_TAB[AAADI9AACAAACEAAAA]
...
. process 1 scanning LEG.SUB_MON_STAT[AAAQSRAAEAAAACIAAA]
...
. process 1 scanning XDB.PRIN_PT[AAAN2VAACAAAEDoAAA]
ORA-30967: operation directly on the Path Table is disallowed

CSS-00144: failed to scan table XDB.PRIN_PT


Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

여기에서는 다음 내용을 확인할 수 있다.

• 23행: 테스트 환경에 생성해 놓은 LEG user의 SUB_MON_STAT 테이블을 scan함

5.4.6. csscan 실행결과 확인-scan.err(오류 상세 내역)

scan.err 파일에서는 각 테이블, 컬럼의 character set 변환 오류 내역을 상세하게 살펴볼 수 있다.

Database Scan Individual Exception Report


[Database Scan Parameters]

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

[Data Dictionary individual exceptions]

User  : SYS
Table : COM$
Column: COMMENT$
Type  : VARCHAR2(4000)
Number of Exceptions         : 0
Max Post Conversion Data Size: 381

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAACIAABAAARCPABN convertible              사용월
AAAACIAABAAARCPABO convertible              호선명
AAAACIAABAAARCPABP convertible              지하철역코드
AAAACIAABAAARCPABQ convertible              지하철역명
AAAACIAABAAARCPABR convertible              승차인원수
AAAACIAABAAARCPABS convertible              하차인원수
AAAACIAABAAARCPABT convertible              작업일자
AAAACIAABAAARCPABU convertible              비고
AAAACIAABAAARCPABV convertible              참조설명
AAAACIAABAAARCPABW convertible              참조이미지
AAAACIAABAAARCPABX convertible              지하철역별승하차인원
------------------ ------------------ ----- ------------------------------


[Application data individual exceptions]

User  : LEG
Table : SUB_MON_STAT
Column: LINE_NUM
Type  : VARCHAR2(14)
Number of Exceptions         : 140
Max Post Conversion Data Size: 20

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAQSRAAEAAAAC/ABp exceed column size    20 공항철도 1호선
AAAQSRAAEAAAAC/ABq exceed column size    20 공항철도 1호선
AAAQSRAAEAAAAC/ABr exceed column size    20 공항철도 1호선
...
------------------ ------------------ ----- ------------------------------

User  : LEG
Table : SUB_MON_STAT
Column: COMMT
Type  : VARCHAR2(4000)
Number of Exceptions         : 1
Max Post Conversion Data Size: 4500

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAQSRAAEAAAACNAAA exceed column size  4500 서울역서울역서울역서울역서울역
------------------ ------------------ ----- ------------------------------

User  : LEG
Table : SUB_MON_STAT
Column: SUB_STA_NM
Type  : VARCHAR2(20)
Number of Exceptions         : 168
Max Post Conversion Data Size: 27

ROWID              Exception Type      Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAQSRAAEAAAAC+AA8 exceed column size    24 디지털미디어시티
AAAQSRAAEAAAAC+AAC exceed column size    21 가산디지털단지
AAAQSRAAEAAAAC/AAR exceed column size    27 동대문역사문화공원
...
------------------ ------------------ ----- ------------------------------

KO16MSWIN949 환경에서의 CSSCAN 실행결과 파일은 아래 github repository에서 확인할 수 있다.

• scan.txt: Oracle-CharacterSet-Conversion/scan.txt at main · DAToolset/Oracle-CharacterSet-Conversion (github.com)
• scan.out: Oracle-CharacterSet-Conversion/scan.out at main · DAToolset/Oracle-CharacterSet-Conversion (github.com)
• scan.err: Oracle-CharacterSet-Conversion/scan.err at main · DAToolset/Oracle-CharacterSet-Conversion (github.com)

5.5. 참고: CSSCAN 용도

CSSCAN은 두 가지 용도로 활용할 수 있다.

1. 변환시 잘리거나 깨지는 데이터의 RowID, ColumnID 확인 –> 데이터 정제에 활용
   • 잘리거나 깨지는 데이터가 대부분인 경우에는(예: US7ASCII to AL32UTF8) 수작업으로 거의 모든 데이터를 정제해야 하므로 시간과 비용이 많이 발생하여 큰 의미가 없음
2. As-Is 컬럼의 길이를 증가시킬 컬럼목록 확인 –> To-Be 데이터 모델 설계에 활용
   • To-Be 설계시 표준도메인의 자리수 결정에 활용할 수 있으므로, 상당히 의미가 있음

또한, As-Is 데이터에 대해 Character Set을 변환한 복제 테이블을 미리 구성하는 경우에도 이 길이(MAXSIZ)를 참조하여 생성하는 것이 좋다. As-Is 테이블의 컬럼길이와 동일하게 구성하는 경우, Character Set 변환 후 증가한 길이가 As-Is 테이블의 컬럼길이를 초과하는 데이터는 다음의 오류와 함께 데이터 적재가 실패한다.

– ORA-12899: value too large for column “OWNER”.”TABLE”.”COLUMN” (actual: Inserted length, maximum: Declared length)

(예: ORA-12899: value too large for column “LEG”.”SUB_MON_STAT”.”LINE_NUM” (actual: 16, maximum: 14) )

다음은 CSSCAN 실행 후, 특정 USER의 테이블 컬럼중 변환시 오류가 발생하는 대상과 현재 길이, 변환 길이, CLOB으로 변환해야 할지를 확인하는 Query문장이다.

SELECT  U.USERNAME, TC.TABLE_NAME, TC.COLUMN_NAME, TC.DATA_TYPE, TC.DATA_LENGTH, CC.MAXSIZ
       ,CASE WHEN TC.DATA_TYPE <> 'CLOB' AND CC.MAXSIZ > 4000 THEN 'Y' END AS CLOB_CONV_YN
  FROM  CSMIG.CSM$COLUMNS CC INNER JOIN DBA_USERS U
          ON   (CC.USR# = U.USER_ID)
        INNER JOIN DBA_OBJECTS O
          ON   (CC.OBJ# = O.OBJECT_ID)
        INNER JOIN DBA_TAB_COLUMNS TC
          ON   (CC.COL# = TC.COLUMN_ID
            AND O.OBJECT_NAME = TC.TABLE_NAME)
 WHERE  U.USERNAME = 'LEG'
   AND  O.OBJECT_TYPE = 'TABLE'
   AND  CC.ERRCNT <> 0;

추출되는 데이터의 샘플은 다음과 같다.

Row#	USERNAME	TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH (현재 길이)	MAXSIZ (변환 길이)	CLOB_CONV_YN (CLOB 변환여부)
1	LEG	SUB_MON_STAT	LINE_NUM	VARCHAR2	14	20
2	LEG	SUB_MON_STAT	SUB_STA_NM	VARCHAR2	20	27
3	LEG	SUB_MON_STAT	COMMT	VARCHAR2	4000	4500	Y

각 컬럼의 의미는 다음과 같다.

컬럼명	의미
USERNAME	테이블의 소유자
TABLE_NAME	대상 테이블 명
COLUMN_NAME	대상 테이블의 컬럼명
DATA_TYPE	대상 컬럼의 현재 데이터 타입
DATA_LENGTH	대상 컬럼의 현재 데이터 길이
MAXSIZ	Character Set 변환후 예상되는 최대 길이
CLOB_CONV_YN	현재 VARCHAR2타입을 CLOB으로 변경할 대상여부(Y: CLOB으로 변경 검토대상임)

주의할 것은, CSSCAN이 실행된 시점의 데이터를 기준으로 확인했기 때문에, 이 기준으로 길이를 증가시키더라도 컬럼 길이의 증가 또는 데이터의 증가에 따라 “ORA-12899: value too large for column” 오류가 발생할 가능성은 여전히 있다는 점이다.

---

여기까지 KO16MSWIN949 테스트 환경에서 CSSCAN을 실행한 결과에 대해 살펴보았다.