'오라클 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#);

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,