[ORACLE] DBA_ALL_TABLES 완벽 가이드 : 오라클 테이블 구조의 핵심 이해

[ORACLE] DBA_ALL_TABLES

 

Oracle 데이터베이스에서 모든 테이블의 구조적 특성과 저장 속성을 한눈에 파악하려면 DBA_ALL_TABLES 뷰를 이해해야 합니다. 이 뷰는 데이터베이스 관리자(DBA)가 시스템 전반의 테이블 특성을 분석하고, 성능 최적화 및 스토리지 관리 정책을 수립하는 데 핵심적인 역할을 합니다. 오늘은 DBA_ALL_TABLES의 구조, 주요 컬럼, 활용 사례, 그리고 실제 운영 환경에서의 모니터링 팁까지 종합적으로 다뤄보겠습니다.

---

1. DBA_ALL_TABLES란?

DBA_ALL_TABLES는 Oracle 데이터베이스의 모든 접근 가능한 테이블에 대한 정보를 제공하는 데이터 딕셔너리 뷰입니다. 이 뷰는 DBA뿐 아니라 개발자, 성능 튜너에게도 중요한 역할을 하며, 각 테이블의 물리적 속성(스토리지 구조, 캐시 여부, 파티션 정책 등)을 상세히 파악할 수 있습니다.

특히 DBA_TABLES, ALL_TABLES, USER_TABLES와 달리, DBA_ALL_TABLES는 데이터베이스 전체 범위의 접근 가능한 모든 테이블 정보를 통합적으로 제공합니다. 즉, 관리적 관점에서 ‘전체 스키마 단위 분석’이 가능하다는 점이 큰 장점입니다.

---

2. 주요 컬럼 및 속성 설명

컬럼명	데이터 타입	설명
OWNER	VARCHAR2(30)	테이블의 소유자(스키마명)
TABLE_NAME	VARCHAR2(30)	테이블 이름
TABLESPACE_NAME	VARCHAR2(30)	테이블이 속한 테이블스페이스
CLUSTER_NAME	VARCHAR2(30)	테이블이 클러스터 내에 존재하는 경우 클러스터 이름
IOT_TYPE	VARCHAR2(12)	Index-Organized Table 여부 (‘IOT’, ‘IOT_OVERFLOW’, ‘IOT_MAPPING’)
PARTITIONED	VARCHAR2(3)	파티션 테이블 여부 (‘YES’ 또는 ‘NO’)
CACHE	VARCHAR2(3)	테이블 데이터가 캐시에 유지되는지 여부 (‘Y’ 또는 ‘N’)
NUM_ROWS	NUMBER	통계 수집 시점 기준의 행(row) 수
BLOCKS	NUMBER	테이블이 사용하는 블록 수
AVG_ROW_LEN	NUMBER	평균 행 길이(바이트)
LAST_ANALYZED	DATE	테이블 통계가 마지막으로 갱신된 시점

---

3. DBA_ALL_TABLES vs DBA_TABLES 비교

항목	DBA_ALL_TABLES	DBA_TABLES	비교 요약
포함 범위	현재 사용자가 접근 가능한 모든 테이블	데이터베이스 내의 모든 테이블	DBA_ALL_TABLES는 접근 권한 기준, DBA_TABLES는 전체 데이터 기준
보안 수준	사용자 권한 제한	SYSDBA 전용	보안 측면에서 DBA_ALL_TABLES가 더 안전함
활용 주체	DBA, 개발자, 분석가	DBA 전용	운영자와 개발자가 공통으로 활용 가능

---

4. DBA_ALL_TABLES 조회 예시

Oracle SQL*Plus나 SQL Developer에서 아래와 같이 조회할 수 있습니다:

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS, PARTITIONED
FROM DBA_ALL_TABLES
WHERE OWNER = 'HR'
ORDER BY TABLE_NAME;

이 쿼리는 HR 스키마의 테이블 구조와 데이터 수, 파티션 여부를 빠르게 파악할 때 유용합니다.

---

5. 성능 최적화 활용 사례

DBA_ALL_TABLES를 활용하면 테이블의 물리적 구조와 스토리지 정책을 기반으로 성능을 개선할 수 있습니다.

• 통계 정보 관리: LAST_ANALYZED와 NUM_ROWS를 통해 오래된 통계 정보를 식별하고, DBMS_STATS 패키지를 사용해 최신화.
• IOT 테이블 최적화: IOT_TYPE이 ‘IOT’인 테이블은 인덱스 기반 저장이므로, 랜덤 I/O를 줄이고 검색 성능을 향상.
• 파티션 관리: PARTITIONED가 ‘YES’인 테이블은 파티션 단위로 백업 및 관리 가능.
• 스토리지 재구성: BLOCKS 수가 비정상적으로 높은 테이블은 SHRINK SPACE 또는 MOVE TABLESPACE 명령으로 최적화 가능.

---

6. DBA_ALL_TABLES를 이용한 모니터링 자동화

DBA들은 주기적으로 다음과 같은 스크립트를 통해 스토리지 사용량 및 통계 수집 상태를 자동 점검합니다.

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED
FROM DBA_ALL_TABLES
WHERE LAST_ANALYZED < SYSDATE - 30
ORDER BY LAST_ANALYZED;

이 스크립트를 크론잡(CRON)이나 DBMS_SCHEDULER와 연계하면 30일 이상 통계가 갱신되지 않은 테이블을 자동 보고할 수 있습니다. 이 방식은 성능 저하를 예방하고 옵티마이저의 판단 정확도를 유지하는 데 유용합니다.

---

7. 파티션 테이블 관리와 DBA_ALL_TABLES

파티션 테이블의 메타데이터 역시 DBA_ALL_TABLES에서 기본 정보를 제공합니다. 단, 세부 파티션 구조는 DBA_TAB_PARTITIONS 또는 DBA_TAB_SUBPARTITIONS 뷰와 결합하여 조회해야 완전한 정보를 얻을 수 있습니다.

SELECT a.OWNER, a.TABLE_NAME, b.PARTITION_NAME, b.NUM_ROWS
FROM DBA_ALL_TABLES a
JOIN DBA_TAB_PARTITIONS b
ON a.TABLE_NAME = b.TABLE_NAME
WHERE a.PARTITIONED = 'YES';

이 조합은 파티션 단위의 데이터 분포를 파악하고, 특정 파티션의 통계 갱신 시점을 점검할 때 매우 효과적입니다.

---

8. DBA_ALL_TABLES와 관련된 주요 뷰

뷰 이름	설명	활용 목적
DBA_TABLES	데이터베이스의 모든 테이블 정보	전체 테이블 구조 파악
ALL_TABLES	현재 사용자가 접근 가능한 테이블	권한 내 관리용
USER_TABLES	자신이 소유한 테이블 정보	개인 스키마 관리
DBA_TAB_COLUMNS	각 테이블의 컬럼 구조	테이블 구조 상세 분석
DBA_TAB_PARTITIONS	파티션 테이블의 세부 파티션 정보	파티션별 통계 분석

---

9. 결론

DBA_ALL_TABLES는 Oracle 데이터베이스의 ‘테이블 구조의 지도’라 할 수 있습니다. 이 뷰를 통해 DBA는 모든 테이블의 저장 구조, 통계 정보, 캐시 전략을 한눈에 파악하고 시스템 전반의 최적화를 설계할 수 있습니다. 운영 환경에서는 특히 통계 관리와 파티션 분석, IOT 성능 튜닝에 활용도가 높습니다. 정기적인 점검과 SQL 스크립트 자동화로 데이터 품질과 성능을 지속적으로 유지하는 것이 바람직합니다.

---

출처

• Oracle® Database Reference 19c - DBA_ALL_TABLES View
• Oracle Performance Tuning Guide