[ORACLE] ALL_TAB_COLUMNS() 실무 활용 가이드

[ Oracle] ALL_TAB_COLUMNS

 

Oracle Database를 사용하는 실무자라면 반드시 알아야 할 시스템 뷰 중 하나가 ALL_TAB_COLUMNS입니다. 이 뷰는 사용자가 접근할 수 있는 모든 테이블(또는 뷰)의 컬럼 정보를 상세히 제공하며, 데이터 구조를 이해하고 SQL 문을 정확하게 작성하기 위한 기초가 됩니다.

1. ALL_TAB_COLUMNS 개요

ALL_TAB_COLUMNS는 Oracle에서 제공하는 메타데이터 뷰로, 사용자가 접근할 수 있는 테이블 및 뷰의 컬럼 정보를 포함하고 있습니다. 이름에서 알 수 있듯이 "ALL"이 붙은 뷰는 사용자 자신뿐만 아니라 권한이 부여된 타인의 오브젝트 정보도 조회할 수 있습니다.

2. 주요 컬럼 설명

다음은 ALL_TAB_COLUMNS 뷰에서 자주 사용되는 주요 컬럼들과 그 설명입니다:

컬럼명	설명
OWNER	테이블 또는 뷰의 소유자
TABLE_NAME	테이블 또는 뷰의 이름
COLUMN_NAME	컬럼 이름
DATA_TYPE	컬럼의 데이터 타입 (예: VARCHAR2, NUMBER)
DATA_LENGTH	문자열의 경우 최대 길이 (BYTE 단위)
DATA_PRECISION	숫자형 컬럼의 전체 자릿수
DATA_SCALE	숫자형 컬럼의 소수점 이하 자릿수
NULLABLE	NULL 허용 여부 (Y/N)

3. USER_TAB_COLUMNS, ALL_TAB_COLUMNS, DBA_TAB_COLUMNS 비교

Oracle에서는 유사한 기능을 수행하는 뷰들이 존재합니다. 다음 표를 통해 이들의 차이를 비교해보겠습니다:

항목	USER_TAB_COLUMNS	ALL_TAB_COLUMNS	DBA_TAB_COLUMNS
조회 대상	자신이 소유한 컬럼 정보	접근 가능한 모든 컬럼 정보	전체 DB의 모든 컬럼 정보
필요 권한	일반 사용자	일반 사용자	DBA 권한 필요
실무 활용	자기 테이블 구조 확인	협업, 패키지 분석 시 활용	DB 전체 스키마 분석

4. 실무 활용 예제

4.1 특정 테이블의 컬럼 목록 조회

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
  AND OWNER = 'HR';
  

4.2 NUMBER 타입 컬럼만 필터링

SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE DATA_TYPE = 'NUMBER';
  

4.3 NULL 불허 컬럼 조회

SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE NULLABLE = 'N';
  

4.4 컬럼의 정밀도와 스케일 확인

SELECT TABLE_NAME, COLUMN_NAME, DATA_PRECISION, DATA_SCALE
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'SALES';
  

5. 성능 팁 및 주의사항

• WHERE 절 없이 전체를 조회하면 결과가 수만 건 이상일 수 있으므로 반드시 조건을 걸어야 합니다.
• 컬럼 이름은 대소문자 구분 없이 작성되지만, 조건절에서는 정확한 테이블명과 OWNER 사용을 권장합니다.
• 데이터 타입 및 정밀도는 프로그램 연동 시 오류를 방지하기 위해 꼭 확인해야 합니다.
• VIEW에 대해서도 동일하게 정보를 제공하므로 테이블과 구분이 필요할 수 있습니다.

6. ALL_TAB_COLUMNS 관련 뷰

• ALL_TABLES: 테이블 수준 메타 정보
• ALL_TAB_PRIVS: 컬럼 단위 권한 정보
• ALL_COL_COMMENTS: 컬럼 설명(comment) 정보
• ALL_CONS_COLUMNS: 제약 조건이 적용된 컬럼 정보

7. 결론

ALL_TAB_COLUMNS는 Oracle DB를 다루는 데 있어 빠질 수 없는 핵심 뷰입니다. 컬럼의 데이터 타입, 길이, NULL 허용 여부 등은 SQL 작성, 애플리케이션 연동, 데이터 마이그레이션 등 다양한 작업의 정확성과 안정성을 결정짓는 요소입니다. 따라서 이 뷰를 잘 이해하고 적극적으로 활용하는 것이 Oracle 기반 시스템의 품질을 높이는 지름길입니다.

8. 참고자료

• Oracle Database Reference 21c: https://docs.oracle.com/en/database/
• Oracle SQL Developer Tool
• DB 실무 프로젝트 문서 및 모델링 작업 경험