Oracle Character Set Scanner (CSSCAN)변경 대상 추출 스크립트

 

CSSCAN이 설치된 상태에서 아래 쿼리문을 통하여 테이블별 타입 수정 스크립트를 일괄 추출할 수 있다. SQL> @?/rdbms/admin/csminst.sql     SELECT    'alter table '        || owner        || '.'        || table_name        || ' modify  ('        || column_name        || ' '        || data_type        || '('        || CEIL (LENGTH / 2 * 3)        || '))'   FROM (SELECT d.name AS owner,                b.name AS table_name,                c.name AS column_name,                DECODE (                   c.type#,                   1, DECODE (c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),                   2, DECODE (                         c.scale,                         NULL, DECODE (c.precision#, NULL, 'NUMBER', 'FLOAT'),                         'NUMBER'),                   8, 'LONG',                   9, DECODE (c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),                   12, 'DATE',                   23, 'RAW',                   24, 'LONG RAW',                   69, 'ROWID',                   96, DECODE (c.charsetform, 2, 'NCHAR', 'CHAR'),                   105, 'MLSLABEL',                   106, 'MLSLABEL',                   112, DECODE (c.charsetform, 2, 'NCLOB', 'CLOB'),                   113, 'BLOB',                   114, 'BFILE',                   115, 'CFILE',                   178, 'TIME(' || c.scale || ')',                   179, 'TIME(' || c.scale || ')' || ' WITH TIME ZONE',                   180, 'TIMESTAMP(' || c.scale || ')',                   181, 'TIMESTAMP(' || c.scale || ')' || ' WITH TIME ZONE',                   231,    'TIMESTAMP('                        || c.scale                        || ')'                        || ' WITH LOCAL TIME ZONE',                   182, 'INTERVAL YEAR(' || c.precision# || ') TO MONTH',                   183,    'INTERVAL DAY('                        || c.precision#                        || ') TO SECOND('                        || c.scale                        || ')',                   208, 'UROWID',                   'UNDEFINED')                   AS data_type,                c.LENGTH AS LENGTH           FROM (select distinct usr#,obj#,col# from csmig.CSM$ERRORS) a,                sys.col$ c,                sys.obj$ b,                sys.user$ d          WHERE     A.OBJ# = b.obj#                AND a.col# = c.col#                AND c.obj# = B.OBJ#                and b.owner#=d.user#                AND a.usr# = d.user#);