[ Oracle] 오라클 ALL_SEQUENCES 뷰로 시퀀스 관리 완전 정복

[Oracle] ALL_SEQUENCES

 

Oracle 데이터베이스에서 시퀀스(sequence)는 고유한 숫자 값을 자동으로 생성하는 객체입니다. 주로 기본 키나 일련 번호를 자동 증가시키기 위해 사용됩니다. 이 시퀀스 객체에 대한 정보를 확인하고 관리하는 데 필수적인 도구가 바로 ALL_SEQUENCES 뷰입니다. 이 글에서는 Oracle의 ALL_SEQUENCES 뷰가 무엇인지, 어떤 정보를 담고 있으며, 어떻게 실무에 적용할 수 있는지를 구체적인 예제와 함께 설명합니다.

1. ALL_SEQUENCES란?

ALL_SEQUENCES는 사용자가 접근 권한을 가진 모든 시퀀스 객체의 메타데이터를 제공하는 Oracle 시스템 뷰입니다. 일반적으로 자신의 스키마 또는 권한이 부여된 타 스키마의 시퀀스를 조회하는 데 사용되며, 시퀀스의 현재 값, 증가 간격, 최소/최대값, 캐시 여부 등의 정보를 확인할 수 있습니다.

2. 주요 컬럼 설명

다음은 ALL_SEQUENCES 뷰의 주요 컬럼과 그 의미입니다:

컬럼명	설명
SEQUENCE_OWNER	시퀀스를 소유한 사용자
SEQUENCE_NAME	시퀀스의 이름
MIN_VALUE	생성 가능한 최소 값
MAX_VALUE	생성 가능한 최대 값
INCREMENT_BY	시퀀스의 증가 간격
CYCLE_FLAG	최대값 도달 시 처음으로 되돌릴지 여부 (Y/N)
ORDER_FLAG	순서 보장 여부 (Y/N)
CACHE_SIZE	캐시된 시퀀스 수
LAST_NUMBER	다음에 생성될 숫자 값

3. USER_SEQUENCES, ALL_SEQUENCES, DBA_SEQUENCES 비교

Oracle에서는 시퀀스 정보를 확인할 수 있는 뷰가 여러 종류 있습니다. 다음 표를 통해 차이를 확인하세요:

뷰 이름	설명	사용 권한
USER_SEQUENCES	자신이 소유한 시퀀스만 조회	일반 사용자
ALL_SEQUENCES	자신이 접근 가능한 모든 시퀀스	일반 사용자
DBA_SEQUENCES	모든 사용자 소유 시퀀스 정보	DBA 권한 필요

4. 실무 활용 예제

4.1 특정 사용자 소유 시퀀스 목록 확인

SELECT SEQUENCE_NAME, LAST_NUMBER
FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER = 'HR';
  

4.2 증가 간격이 1이 아닌 시퀀스 찾기

SELECT SEQUENCE_NAME, INCREMENT_BY
FROM ALL_SEQUENCES
WHERE INCREMENT_BY != 1;
  

4.3 CYCLE 옵션이 활성화된 시퀀스 조회

SELECT SEQUENCE_NAME
FROM ALL_SEQUENCES
WHERE CYCLE_FLAG = 'Y';
  

4.4 캐시 크기가 0인 시퀀스 확인

SELECT SEQUENCE_NAME
FROM ALL_SEQUENCES
WHERE CACHE_SIZE = 0;
  

5. 성능과 관련된 고려사항

• 캐시(Cache): 성능 향상을 위해 기본적으로 시퀀스는 값을 미리 캐시합니다. CACHE_SIZE가 0이면 매번 디스크 접근이 필요해 성능이 저하될 수 있습니다.
• CYCLE 옵션: 이 옵션은 번호가 최대값에 도달하면 MIN_VALUE로 되돌리지만, 중복 키 문제가 발생할 수 있으므로 주의해야 합니다.
• ORDER 옵션: 병렬 환경에서 순서를 보장하지만, 성능에 영향을 줄 수 있어 필요한 경우에만 활성화하세요.

6. 관련 뷰 및 함수

• USER_OBJECTS: 시퀀스를 포함한 객체 정보 확인
• DBA_OBJECTS: 전체 DB의 객체 유형 및 상태 확인
• DBMS_METADATA.GET_DDL: 시퀀스 생성 스크립트 추출

7. DBMS_METADATA로 시퀀스 정의 추출

시퀀스의 정확한 생성 스크립트를 얻고 싶다면 아래처럼 조회할 수 있습니다:

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', 'EMP_SEQ', 'HR') FROM DUAL;
  

8. ALL_SEQUENCES를 활용한 정기 점검 체크리스트

• 비정상적으로 증가 간격이 큰 시퀀스는 없는가?
• 중복 가능성이 있는 CYCLE 시퀀스는 있는가?
• 캐시 사이즈가 너무 작아 병목이 발생하고 있지는 않은가?
• ORDER 플래그가 꼭 필요한 경우에만 사용되고 있는가?

9. 결론

Oracle의 ALL_SEQUENCES 뷰는 데이터베이스 전반의 시퀀스 객체를 효과적으로 관리하는 데 꼭 필요한 도구입니다. 테이블의 자동 증가 값은 물론, 분산 시스템에서 유일한 번호를 생성할 때도 그 정확성과 안정성이 중요하므로, 이 뷰를 활용하여 시퀀스 설계와 성능을 주기적으로 점검하는 습관을 들이는 것이 좋습니다.

10. 참고자료

• Oracle Database 21c Reference Guide: https://docs.oracle.com/en/database/
• DBMS_METADATA 패키지 공식 문서
• SQL Developer 실무 경험 기반