'오라클 CSSCAN'에 해당되는 글 1건

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#);