【Oracle】シーケンスの確認方法完全ガイド｜USER/ALL/DBA_SEQUENCES・NEXTVAL/CURRVAL・依存オブジェクト調査まで解説

SELECT * FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME;

SELECT
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CYCLE_FLAG,
    ORDER_FLAG,
    CACHE_SIZE,
    LAST_NUMBER
FROM USER_SEQUENCES
ORDER BY SEQUENCE_NAME;

SELECT
    SEQUENCE_OWNER,
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    LAST_NUMBER
FROM ALL_SEQUENCES
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME;

SELECT
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CACHE_SIZE,
    LAST_NUMBER
FROM ALL_SEQUENCES
WHERE SEQUENCE_OWNER = 'SCOTT'
ORDER BY SEQUENCE_NAME;

SELECT
    SEQUENCE_OWNER,
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CYCLE_FLAG,
    CACHE_SIZE,
    LAST_NUMBER
FROM DBA_SEQUENCES
ORDER BY SEQUENCE_OWNER, SEQUENCE_NAME;

SELECT
    SEQUENCE_NAME,
    MIN_VALUE,
    MAX_VALUE,
    INCREMENT_BY,
    CYCLE_FLAG,
    ORDER_FLAG,
    CACHE_SIZE,
    LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'SEQ_EMP';

SELECT SEQUENCE_NAME, LAST_NUMBER, INCREMENT_BY
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME LIKE 'SEQ%'
ORDER BY SEQUENCE_NAME;

SELECT SEQ_EMP.NEXTVAL FROM DUAL;

SELECT SEQ_EMP.CURRVAL FROM DUAL;

-- 別スキーマのシーケンスを参照する場合
SELECT SCOTT.SEQ_EMP.NEXTVAL FROM DUAL;
SELECT SCOTT.SEQ_EMP.CURRVAL FROM DUAL;

SELECT
    NAME         AS object_name,
    TYPE         AS object_type,
    REFERENCED_NAME  AS seq_name,
    REFERENCED_TYPE  AS ref_type
FROM USER_DEPENDENCIES
WHERE REFERENCED_TYPE = 'SEQUENCE'
ORDER BY TYPE, NAME;

SELECT
    NAME         AS object_name,
    TYPE         AS object_type
FROM USER_DEPENDENCIES
WHERE REFERENCED_TYPE = 'SEQUENCE'
  AND REFERENCED_NAME = 'SEQ_EMP'
ORDER BY TYPE, NAME;

SELECT
    SEQUENCE_NAME,
    LAST_NUMBER,
    MAX_VALUE,
    (MAX_VALUE - LAST_NUMBER) AS remaining,
    ROUND((LAST_NUMBER / MAX_VALUE) * 100, 2) AS used_pct
FROM USER_SEQUENCES
ORDER BY used_pct DESC;

SELECT
    SEQUENCE_NAME,
    LAST_NUMBER,
    MAX_VALUE,
    ROUND((LAST_NUMBER / MAX_VALUE) * 100, 2) AS used_pct
FROM USER_SEQUENCES
WHERE CYCLE_FLAG = 'N'
  AND ROUND((LAST_NUMBER / MAX_VALUE) * 100, 2) >= 80
ORDER BY used_pct DESC;