database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41506 게시물 읽기
No. 41506
해당 테이블 혹은 모든 테이블에서 자기자신 데이터의 1.5의 바이트가 현재 자신의 컬럼의 바이트 사이즈를 초과하는 컬럼 찾기
작성자
허광민(hgm0302)
작성일
2018-01-03 17:26:27ⓒ
2018-01-03 17:27:58ⓜ
조회수
1,133

해당 테이블 혹은 모든 테이블에서 자기자신 데이터의 1.5의 바이트가 현재 자신의 컬럼의 바이트 사이즈를 초과하는 컬럼 찾는방법이 있는지 문의드립니다.

 

한글 1자당 2byte기준

ex) A 테이블 A_01컬럼의 데이터타입 varchar2(5byte)

컬럼의 데이터 = '한글' (4byte)

 

4 * 1.5 = 6byte 임으로 해당됨

 

위와같은 경우에 해당되는 컬럼들을 찾을 수 있는 방법이 있는지 문의드립니다.

이 글에 대한 댓글이 총 5건 있습니다.

SELECT table_name
     , column_id
     , column_name
     , data_type
     , data_length
     , vsize_new
  FROM (SELECT b.table_name
             , b.column_id
             , b.column_name
             , b.data_type
             , b.data_length
             , TO_NUMBER(
               dbms_xmlgen.getxmltype(
               'SELECT MAX(LENGTHB('
                || b.column_name || ') * 2 - LENGTH(' || b.column_name
                || ')) FROM ' || b.table_name
                || ' HAVING COUNT(*) > 0'
               ).Extract('//text()')
               ) vsize_new
             , ROWNUM rn
          FROM user_objects a
             , user_tab_columns b
         WHERE a.object_type = 'TABLE'
           AND a.object_name = b.table_name
           AND b.data_type LIKE '%CHAR%'
        )
 WHERE vsize_new > data_length * 1.5
;

마농(manon94)님이 2018-01-04 08:40:55에 작성한 댓글입니다.

댓글 정말 감사드립니다.

적어주신 내역으로 쿼리를 실행해 본 결과 수치가 부적합하다는 오류가 발생하네요.

 

혹시 버전때문에 처리가 안되는지 몰라 내역 기재합니다.

사용 DBMS는 오라클 TOAD이며 버전은 11g입니다.

 

 

 

허광민님이 2018-01-04 09:29:18에 작성한 댓글입니다. Edit

SELECT *
  FROM (SELECT table_name
             , column_id
             , column_name
             , data_type
             , data_length
             , TO_NUMBER(
               dbms_xmlgen.getxmltype(
               'SELECT MAX(LENGTHB('
                || column_name || ') * 2 - LENGTH(' || column_name
                || ')) FROM ' || table_name
                || ' HAVING COUNT(*) > 0'
               ).Extract('//text()')
               ) vsize_new
             , ROWNUM rn    -- view merge 방지용
          FROM (SELECT b.table_name
                     , b.column_id
                     , b.column_name
                     , b.data_type
                     , b.data_length
                     , ROWNUM rn    -- view merge 방지용
                  FROM user_objects a
                     , user_tab_columns b
                 WHERE a.object_type = 'TABLE'
                   AND a.object_name = b.table_name
                   AND b.data_type LIKE '%CHAR%'
                )
        )
 WHERE vsize_new > data_length * 1.5
;

마농(manon94)님이 2018-01-04 09:41:12에 작성한 댓글입니다.
이 댓글은 2018-01-04 10:09:05에 마지막으로 수정되었습니다.

동일하게 오류가 발생합니다.

적어주신 구문 중에

 

TO_NUMBER(

dbms_xmlgen.getxmltype(

'SELECT MAX(LENGTHB('

|| column_name || ') * 2 - LENGTH(' || column_name

|| ')) FROM ' || table_name

|| ' HAVING COUNT(*) > 0'

).Extract('//text()')

) vsize_new

 

해당 내역이 핵심인 것 같은데 테이블컬럼에 데이터가 없는경우

 

SELECT MAX(LENGTHB(column_name) * 2 - LENGTH(column_name)) FROM table_name HAVING COUNT(*) > 0

해당 구문은 NULL값이 나오는데 문제가 없는지?

 

MAX(LENGTHB(column_name) * 2 - LENGTH(column_name))

해당 컬럼 맥스데이터의 바이트 *2 에서 문자열의 길이를 빼주는 구문은 어떤의미인지 궁금합니다.

허광민님이 2018-01-04 10:47:11에 작성한 댓글입니다. Edit

1. 데이터가 없는 테이블에서 이상한 값이 나와서 오류가 나길래
  HAVING 조건을 주어 NULL 이 나오도록 유도한 것입니다.
2. LENGTHB(column_name) * 2 - LENGTH(column_name) 는
  LENGTHB(column_name) 는 해당항목의 바이트
  LENGTH(column_name) 는 해당항목의 글자수
  바이트수에서 글자수를 빼면 2바이트짜리 글자의 수가 나옵니다.
  즉 한글의 갯수가 나온다고 생각하면 되구요.
  바이트수에서 한글의 수를 더하면 이관 후 바이트수가 됩니다.
3. 오류는
  우선 필요 테이블 목록 뽑으신 후
  테이블명 하나씩 조건으로 주어가면서 확인해 보세요.
  오류 원인은 가지가지이니 직접 찾아내셔야 할 듯 하네요.

마농(manon94)님이 2018-01-04 10:54:15에 작성한 댓글입니다.
이 댓글은 2018-01-04 10:55:20에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
41509조건에 따라 데이터 일정 간격으로 update 문의 [1]
rapid
2018-01-13
1070
41508임의로 row수 늘이기 [4]
초보입니다.
2018-01-11
1174
41507컬럼을 일정 길이만큼 잘라서 구분자를 붙이기 [1]
선이
2018-01-10
1010
41506해당 테이블 혹은 모든 테이블에서 자기자신 데이터의 1.5의 바이트가 현재 자신의 컬럼의 바이트 사이즈를 초과하는 컬럼 찾기 [5]
허광민
2018-01-03
1133
41505APPEND PARALLEL 사용법
엑스
2017-12-23
1127
41504대량의 인서트 이럴경우 어떤게 효율적일까요.?
asdf
2017-12-22
1182
41503select ' ∎ ⦁ ◆ ⓐ ⓒ •' as bb from dual 하면 ? 오류가 나요 [1]
김기훈
2017-12-21
1074
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.093초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다