데이터베이스 질문과 답변
by 임시아이디 [2016.12.29 10:47:01]
SELECT table_name
, num_rows -- 통계정보 건수
, TO_NUMBER(
dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')
) num_rows2 -- 실제측정 건수
FROM user_tables
;
모든 테이블의 건수를 확인하는 쿼리는 가지고 있는데요 이걸 유저컬럼 테이블를 엮어서 어떻게 특정컬럼의 건수를 확인해보고싶은데요 어떻게 해도 잘 안되네요 ㅠ 혹시 방법이 있을까요?
아래는 제가 생각해본 방법인데 XML에러가 나네요.
SELECT table_name
, TO_NUMBER(
dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')
) num_rows2 -- 실제측정 건수
FROM USER_TAB_COLUMNS;
WHERE (COLUMN_NAME LIKE '%JUM_NO%' OR COLUMN_NAME LIKE '%RCV_JUM_NO%' OR COLUMN_NAME LIKE '%REM_AGNJUM%' OR COLUMN_NAME LIKE '%CCL_JUM_NO%' OR COLUMN_NAME LIKE '%LEASE_JUM_NO%')
;
일단 USER_TAB_COLUMNS 뒤에 ;는 빼시구요.
위의 쿼리는 모든 테이블에서 각 테이블의 실제 데이터 건수를 구하는 쿼리인데
아래에 만드신 쿼리는 특정컬럼명을 가지고 있는 테이블에서 각 테이블의 실제 데이터 건수를 구하는 쿼리인데 맞나요?
구하시려고 하는 게 컬럼을 가지고 있는 테이블의 개수인가요? 데이터 건수인가요?
아하 중간에 ; 이게 잘못들어갔네요 이거 테스트해보다가 넣은거라 실제로 빼도 에러가 납니다. 위에 쿼리를 토대로 아래는 제가 뽑을려는 데이터 건수 쿼리입니다.
실제로 수행했던 쿼리 그대로 적어보세요. 해당 쿼리만 봐서는 문제 없거든요.
SELECT COUNT(*) c FROM ' || table_name 뒤에 where 조건 더 주신 거 아닌가요?
WHERE추가 전에 테이블 명도 변경이 됐습니다. 그게 문제가 있는가 해서요.
테이블 명이 변경 되면 바로 USER_TAB_COLUMNS에 적용됩니다 해당 문제는 아닐 것 같고요.
WHERE 절 추가된 최종 쿼리 올려보세요.
현재 쿼리에도 약간 비효율이 있네요. 특정 테이블에 컬럼 두개가 동시에 있으면 테이블 건수를 중복으로 읽겠네요.
SELECT TABLE_NAME
, TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLNAMES
, TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) c FROM ' || TABLE_NAME).EXTRACT('//text()')) NUM_ROWS2
FROM USER_TAB_COLUMNS
WHERE (COLUMN_NAME LIKE '%JUM_NO%'
OR COLUMN_NAME LIKE '%RCV_JUM_NO%'
OR COLUMN_NAME LIKE '%REM_AGNJUM%'
OR COLUMN_NAME LIKE '%CCL_JUM_NO%'
OR COLUMN_NAME LIKE '%LEASE_JUM_NO%')
GROUP BY TABLE_NAME
SELECT TABLE_NAME , TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLNAMES , TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) c FROM ' || TABLE_NAME).EXTRACT('//text()')) NUM_ROWS2 FROM USER_TAB_COLUMNS WHERE (COLUMN_NAME LIKE '%JUM_NO%' OR COLUMN_NAME LIKE '%RCV_JUM_NO%' OR COLUMN_NAME LIKE '%REM_AGNJUM%' OR COLUMN_NAME LIKE '%CCL_JUM_NO%' OR COLUMN_NAME LIKE '%LEASE_JUM_NO%') GROUP BY TABLE_NAME
써주신대로 GROUP BY 빼고 제가 만든 최종쿼리예요 제가 생각하더라도 GROUP BY는 있어야겠네요 하지만 이 쿼리를 돌려보면은 ORA-19202:XML 처리, ORA-00933:SQL 명령어가 올바르게 종료되지 않았습니다.중 오류가 발생했습니다., ORA-06512:"SYS.DMBS_XMLGEN",줄 288에서 ORA-06512:줄 1에서 이런 에러메세지가 뜨네요 ㅠㅠ
SELECT B.TABLE_NAME
, TO_CHAR(WM_CONCAT(A.COLUMN_NAME)) COLNAMES
, TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) c FROM ' || B.TABLE_NAME).EXTRACT('//text()')) NUM_ROWS2
FROM USER_TAB_COLUMNS A, USER_TABLES B
WHERE (A.COLUMN_NAME LIKE '%JUM_NO%'
OR A.COLUMN_NAME LIKE '%RCV_JUM_NO%'
OR A.COLUMN_NAME LIKE '%REM_AGNJUM%'
OR A.COLUMN_NAME LIKE '%CCL_JUM_NO%'
OR A.COLUMN_NAME LIKE '%LEASE_JUM_NO%')
AND A.TABLE_NAME = B.TABLE_NAME
AND B.STATUS = 'VALID'
GROUP BY B.TABLE_NAME
table 상태가 valid한 것만 가지고 해야할 것 같네요.
감사합니다. 해결됐네요. 점심 맛있게 드세요.^^
) 버튼을 클릭하여 작성 하시면 됩니다.