DBA_SEQUENCES - Query Oracle Sequence Configuration

DBA_SEQUENCES

Overview

DBA_SEQUENCES describes every sequence in the database, including its current high-water-mark value, increment step, minimum and maximum bounds, cache size, cycle behaviour, and ordering option. Sequences are the standard Oracle mechanism for generating unique numeric identifiers and are used heavily by application schemas as surrogate primary keys, batch job identifiers, and message sequence numbers.

DBAs query DBA_SEQUENCES to check how close a sequence is to exhaustion, audit cache settings for performance, identify sequences that are not caching values (which causes heavy redo generation), and compare configuration between environments. Note that LAST_NUMBER reflects the next value that will be cached — not the last value issued to a session — making it an approximation of the current position rather than an exact count.

View Type: Static Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_SEQUENCES, SELECT_CATALOG_ROLE, or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SEQUENCE_OWNER	VARCHAR2(128)	Schema that owns the sequence
SEQUENCE_NAME	VARCHAR2(128)	Name of the sequence
MIN_VALUE	NUMBER	Minimum value the sequence can generate
MAX_VALUE	NUMBER	Maximum value the sequence can generate; very large number for unlimited ascending sequences
INCREMENT_BY	NUMBER	Step value between sequence numbers; negative for descending sequences
CYCLE_FLAG	VARCHAR2(1)	Y if the sequence restarts at MIN_VALUE after reaching MAX_VALUE; N otherwise
ORDER_FLAG	VARCHAR2(1)	Y if NEXTVAL is guaranteed to be ordered across RAC instances; N for performance-oriented unordered generation
CACHE_SIZE	NUMBER	Number of sequence values pre-allocated and held in memory; 0 means NOCACHE
LAST_NUMBER	NUMBER	Next sequence value that will be written to disk; values below this are either cached or already issued
SCALE_FLAG	VARCHAR2(1)	Y if scalable sequences (RAC-aware sharded sequences) are enabled (Oracle 18c+)
EXTEND_FLAG	VARCHAR2(1)	Y if the sequence uses extended MAX_VALUE when scale is enabled
SHARDED_FLAG	VARCHAR2(1)	Y if this is a sharded sequence (Oracle 21c+)
SESSION_FLAG	VARCHAR2(1)	Y if this is a session sequence that restarts at MIN_VALUE for each new session (Oracle 18c+)
KEEP_VALUE	VARCHAR2(1)	Y if the sequence value is preserved across FLASHBACK DATABASE (Oracle 12c+)

Essential Queries

Basic Usage

List all sequences in a schema with their current position and key configuration:

SELECT
    sequence_name,
    min_value,
    max_value,
    increment_by,
    cycle_flag,
    order_flag,
    cache_size,
    last_number
FROM
    dba_sequences
WHERE
    sequence_owner = 'HR'
ORDER BY
    sequence_name;

Monitoring Query

Identify sequences approaching exhaustion — where LAST_NUMBER has consumed more than 90% of the available range:

SELECT
    sequence_owner,
    sequence_name,
    min_value,
    max_value,
    last_number,
    increment_by,
    cycle_flag,
    ROUND(
        (last_number - min_value) / NULLIF(max_value - min_value, 0) * 100,
        2
    )                   AS pct_used,
    ROUND(
        (max_value - last_number) / NULLIF(increment_by, 0)
    )                   AS values_remaining
FROM
    dba_sequences
WHERE
    cycle_flag    = 'N'
    AND increment_by > 0
    AND (last_number - min_value) / NULLIF(max_value - min_value, 0) > 0.90
    AND sequence_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY
    pct_used DESC;

Combined with Other Views

Join DBA_SEQUENCES with DBA_DEPENDENCIES to find all tables and PL/SQL objects that reference each sequence, helping assess the impact of a sequence change:

SELECT
    s.sequence_owner,
    s.sequence_name,
    s.last_number,
    s.cache_size,
    d.owner             AS dependent_owner,
    d.name              AS dependent_object,
    d.type              AS dependent_type
FROM
    dba_sequences   s
    LEFT JOIN dba_dependencies d ON d.referenced_owner = s.sequence_owner
                                 AND d.referenced_name  = s.sequence_name
                                 AND d.referenced_type  = 'SEQUENCE'
WHERE
    s.sequence_owner = 'HR'
ORDER BY
    s.sequence_name,
    d.name;

NOCACHE Sequence Audit

Find all sequences with CACHE_SIZE = 0 (NOCACHE), which generate a redo log write for every NEXTVAL call and can become a performance bottleneck under high concurrency:

SELECT
    sequence_owner,
    sequence_name,
    increment_by,
    min_value,
    max_value,
    last_number,
    order_flag
FROM
    dba_sequences
WHERE
    cache_size = 0
    AND sequence_owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
ORDER BY
    sequence_owner,
    sequence_name;

Sequence Configuration Comparison

Compare sequence configuration between two schemas (for example, production vs. a cloned test environment) to detect drift:

SELECT
    COALESCE(p.sequence_name, t.sequence_name)  AS sequence_name,
    p.cache_size                                AS prod_cache,
    t.cache_size                                AS test_cache,
    p.increment_by                              AS prod_incr,
    t.increment_by                              AS test_incr,
    p.cycle_flag                                AS prod_cycle,
    t.cycle_flag                                AS test_cycle,
    p.order_flag                                AS prod_order,
    t.order_flag                                AS test_order
FROM
    (SELECT sequence_name, cache_size, increment_by, cycle_flag, order_flag
     FROM dba_sequences WHERE sequence_owner = 'PROD_OWNER') p
    FULL OUTER JOIN
    (SELECT sequence_name, cache_size, increment_by, cycle_flag, order_flag
     FROM dba_sequences WHERE sequence_owner = 'TEST_OWNER') t
    ON t.sequence_name = p.sequence_name
WHERE
    p.cache_size  != t.cache_size
    OR p.increment_by != t.increment_by
    OR p.cycle_flag   != t.cycle_flag
    OR p.order_flag   != t.order_flag
    OR p.sequence_name IS NULL
    OR t.sequence_name IS NULL
ORDER BY
    sequence_name;

Common Use Cases

• Sequence exhaustion prevention — Regularly query sequences with high LAST_NUMBER relative to MAX_VALUE, especially for NUMBER(10) primary keys in high-volume OLTP systems where exhaustion can cause ORA-08004
• NOCACHE performance remediation — Identify NOCACHE sequences and alter them to CACHE (typically 20–1000 depending on insert rate) to eliminate per-NEXTVAL redo writes
• RAC ORDER_FLAG audit — In RAC environments, sequences with ORDER_FLAG = ‘Y’ serialize across instances; confirm this is intentional as it degrades throughput
• Gap analysis explanation — Educate developers that sequence gaps are normal due to caching, rollbacks, and instance restarts; LAST_NUMBER confirms the cache boundary
• Post-import sequence resynchronisation — After a Data Pump import, query LAST_NUMBER to confirm all sequences were set to values beyond the imported data’s maximum key values
• Session sequence usage — Filter SESSION_FLAG = ‘Y’ to find session-scoped sequences, which are useful for temporary staging keys but invisible after session end

Related Views

• DBA_OBJECTS — Confirms that sequences exist as VALID objects; LAST_DDL_TIME shows when the sequence was last altered
• DBA_DEPENDENCIES — Maps which tables, triggers, and PL/SQL units call NEXTVAL or CURRVAL on a given sequence
• DBA_TAB_COLUMNS — Cross-reference with DEFAULT_VALUE for identity column metadata, which uses internal sequences managed by Oracle 12c+
• USER_SEQUENCES — Equivalent view restricted to the current user’s sequences; safe for application-level queries without DBA privileges

Version Notes

• Oracle 9i / 10g: Core structure stable; CACHE values of 20 introduced as the default when CACHE is specified without a number
• Oracle 12c: Identity columns introduced (GENERATED AS IDENTITY); these use internal sequences visible in DBA_SEQUENCES with system-generated names; KEEP_VALUE column added for Flashback compatibility
• Oracle 18c: SCALE_FLAG and SESSION_FLAG columns added; scalable sequences automatically shard the range across RAC instances to reduce contention; session sequences restart each session
• Oracle 21c: SHARDED_FLAG added for Oracle Sharding environments where each shard maintains an independent sequence range
• Oracle 23ai: No structural changes; sequences interoperate with SQL Domain DEFAULT expressions; JSON Relational Duality Views can use sequences for key generation