해당 테이블 혹은 모든 테이블에서 자기자신 데이터의 1.5의 바이트가 현재 자신의 컬럼의 바이트 사이즈를 초과하는 컬럼 찾는방법이 있는지 문의드립니다.
한글 1자당 2byte기준
ex) A 테이블 A_01컬럼의 데이터타입 varchar2(5byte)
컬럼의 데이터 = '한글' (4byte)
4 * 1.5 = 6byte 임으로 해당됨
위와같은 경우에 해당되는 컬럼들을 찾을 수 있는 방법이 있는지 문의드립니다.
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 ;
댓글 정말 감사드립니다.
적어주신 내역으로 쿼리를 실행해 본 결과 수치가 부적합하다는 오류가 발생하네요.
혹시 버전때문에 처리가 안되는지 몰라 내역 기재합니다.
사용 DBMS는 오라클 TOAD이며 버전은 11g입니다.
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 ;
동일하게 오류가 발생합니다.
적어주신 구문 중에
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 에서 문자열의 길이를 빼주는 구문은 어떤의미인지 궁금합니다.
1. 데이터가 없는 테이블에서 이상한 값이 나와서 오류가 나길래 HAVING 조건을 주어 NULL 이 나오도록 유도한 것입니다. 2. LENGTHB(column_name) * 2 - LENGTH(column_name) 는 LENGTHB(column_name) 는 해당항목의 바이트 LENGTH(column_name) 는 해당항목의 글자수 바이트수에서 글자수를 빼면 2바이트짜리 글자의 수가 나옵니다. 즉 한글의 갯수가 나온다고 생각하면 되구요. 바이트수에서 한글의 수를 더하면 이관 후 바이트수가 됩니다. 3. 오류는 우선 필요 테이블 목록 뽑으신 후 테이블명 하나씩 조건으로 주어가면서 확인해 보세요. 오류 원인은 가지가지이니 직접 찾아내셔야 할 듯 하네요.
