Oracle Sequence MAXVALUE Exhaustion Guard

id: oracle-sequence-maxvalue-exhaustion-guard
namespace: company.team

description: |
  Find Oracle NOCYCLE sequences running out of headroom before their MAXVALUE, raise the
  ceiling with ALTER SEQUENCE behind a dry run plus an approval pause, then read
  dba_sequences again and fail if any target is still short.

inputs:
  - id: jdbc_url
    type: STRING
    displayName: Oracle JDBC URL
    defaults: "jdbc:oracle:thin:@//localhost:1521/FREEPDB1"

  - id: username
    type: STRING
    displayName: Database user
    description: A user that can read dba_sequences (SELECT_CATALOG_ROLE or SELECT
      ANY DICTIONARY). Raising the MAXVALUE of another schema's sequence needs
      ALTER ANY SEQUENCE. A DBA role covers both.
    defaults: SYSTEM

  - id: password
    type: STRING
    displayName: Database password
    defaults: "{{ secret('ORACLE_PASSWORD') }}"

  - id: sequence_owners
    type: ARRAY
    itemType: STRING
    displayName: Schema owners to check
    description: Application schema owners whose sequences are checked. System
      schemas are left out so an internal sequence is never touched.
    defaults:
      - APP

  - id: min_headroom_values
    type: INT
    displayName: Minimum remaining values before a sequence is flagged
    description: A NOCYCLE sequence that can still serve fewer than this many more
      nextval calls before its MAXVALUE is a finding. The remaining count is
      measured in SQL as the number of future values between last_number and
      max_value, so caching is accounted for conservatively. The default flags
      any sequence with under one million draws left.
    defaults: 1000000

  - id: restore_headroom_values
    type: INT
    displayName: Remaining values to restore when raising a ceiling
    description: When a sequence is raised, the new MAXVALUE is set so that this
      many future nextval calls fit above last_number, capped at the Oracle
      NUMBER ceiling of 28 nines. Keep it well above min_headroom_values so the
      re-read passes with margin. The default gives one hundred million draws.
    defaults: 100000000

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    description: Allow ALTER SEQUENCE to raise a MAXVALUE. Off by default.
    defaults: false

  - id: dry_run
    type: BOOL
    displayName: Dry run
    description: Must be false, together with auto_remediate, for any ceiling to be raised.
    defaults: true

variables:
  audit_sql: |
    SELECT s.sequence_owner AS owner,
           s.sequence_name AS sequence_name,
           TO_CHAR(s.max_value) AS max_value,
           TO_CHAR(s.last_number) AS last_number,
           TO_CHAR(s.increment_by) AS increment_by,
           s.cycle_flag AS cycle_flag,
           TO_CHAR(CASE WHEN s.last_number > s.max_value THEN 0
                        ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) AS remaining_values,
           TO_CHAR(LEAST(9999999999999999999999999999,
                         GREATEST(s.max_value + s.increment_by,
                                  s.last_number + {{ inputs.restore_headroom_values }} * s.increment_by))) AS new_max,
           CASE WHEN s.max_value >= 9999999999999999999999999999 THEN 'REPORT_ONLY'
                ELSE 'RAISE' END AS action,
           'ALTER SEQUENCE "' || s.sequence_owner || '"."' || s.sequence_name || '" MAXVALUE '
             || TO_CHAR(LEAST(9999999999999999999999999999,
                              GREATEST(s.max_value + s.increment_by,
                                       s.last_number + {{ inputs.restore_headroom_values }} * s.increment_by))) AS fix_sql
      FROM dba_sequences s
     WHERE s.sequence_owner IN ('{{ inputs.sequence_owners | join("','") | upper }}')
       AND s.increment_by > 0
       AND s.cycle_flag = 'N'
       AND (CASE WHEN s.last_number > s.max_value THEN 0
                 ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) < {{ inputs.min_headroom_values }}
     ORDER BY s.sequence_owner, s.sequence_name

  excluded_sql: |
    SELECT s.sequence_owner AS owner,
           s.sequence_name AS sequence_name,
           TO_CHAR(s.max_value) AS max_value,
           TO_CHAR(s.last_number) AS last_number,
           TO_CHAR(CASE WHEN s.last_number > s.max_value THEN 0
                        ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) AS remaining_values
      FROM dba_sequences s
     WHERE s.sequence_owner IN ('{{ inputs.sequence_owners | join("','") | upper }}')
       AND s.increment_by > 0
       AND s.cycle_flag = 'Y'
       AND (CASE WHEN s.last_number > s.max_value THEN 0
                 ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) < {{ inputs.min_headroom_values }}
     ORDER BY s.sequence_owner, s.sequence_name

  totals_sql: |
    SELECT COUNT(*) AS sequence_count,
           NVL(SUM(CASE WHEN s.increment_by > 0 THEN 1 ELSE 0 END), 0) AS ascending_count,
           NVL(SUM(CASE WHEN s.cycle_flag = 'N' THEN 1 ELSE 0 END), 0) AS nocycle_count,
           NVL(SUM(CASE WHEN s.cycle_flag = 'Y' THEN 1 ELSE 0 END), 0) AS cycle_count,
           NVL(SUM(CASE WHEN s.increment_by > 0 AND s.cycle_flag = 'N'
                         AND (CASE WHEN s.last_number > s.max_value THEN 0
                                   ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) < {{ inputs.min_headroom_values }}
                        THEN 1 ELSE 0 END), 0) AS nocycle_near_max,
           NVL(SUM(CASE WHEN s.increment_by > 0 AND s.cycle_flag = 'Y'
                         AND (CASE WHEN s.last_number > s.max_value THEN 0
                                   ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) < {{ inputs.min_headroom_values }}
                        THEN 1 ELSE 0 END), 0) AS cycle_near_max
      FROM dba_sequences s
     WHERE s.sequence_owner IN ('{{ inputs.sequence_owners | join("','") | upper }}')

  reverify_sql: |
    SELECT s.sequence_owner AS owner,
           s.sequence_name AS sequence_name,
           TO_CHAR(s.max_value) AS max_value,
           TO_CHAR(s.last_number) AS last_number,
           s.cycle_flag AS cycle_flag,
           TO_CHAR(CASE WHEN s.last_number > s.max_value THEN 0
                        ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) AS remaining_values
      FROM dba_sequences s
     WHERE INSTR(',{{ outputs.targets.values.name_list }},', ',' || s.sequence_owner || '.' || s.sequence_name || ',') > 0
       AND s.increment_by > 0
       AND s.cycle_flag = 'N'
       AND (CASE WHEN s.last_number > s.max_value THEN 0
                 ELSE FLOOR((s.max_value - s.last_number) / s.increment_by) + 1 END) < {{ inputs.min_headroom_values }}
     ORDER BY s.sequence_owner, s.sequence_name

tasks:
  - id: totals_before
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. How many sequences the checked schemas hold, split into
      ascending, NOCYCLE, CYCLE plus how many of each are already low on
      headroom.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH_ONE
    sql: "{{ render(vars.totals_sql) }}"

  - id: audit
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Lists the ascending NOCYCLE sequences with fewer than
      min_headroom_values draws left before their MAXVALUE, each with its
      current ceiling, last_number, the remaining count plus the exact ALTER
      SEQUENCE that raises it.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: excluded
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Lists ascending CYCLE sequences that are also low on
      headroom. These are reported, never raised, because a CYCLE sequence wraps
      to its MINVALUE instead of raising ORA-08004.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.username }}"
    password: "{{ inputs.password }}"
    fetchType: FETCH
    sql: "{{ render(vars.excluded_sql) }}"

  - id: targets
    type: io.kestra.plugin.core.output.OutputValues
    description: Split the findings into raise targets, report-only sequences
      already at the NUMBER ceiling plus the comma-joined names of the raise
      targets for the re-read.
    values:
      raise_sql: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "RAISE") |
        .FIX_SQL]') | first | toJson }}
      raise_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "RAISE")] |
        length') | first }}
      raise_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "RAISE") | .OWNER +
        "." + .SEQUENCE_NAME + " [max " + .MAX_VALUE + ", last " + .LAST_NUMBER
        + ", " + .REMAINING_VALUES + " left, raise to " + .NEW_MAX + "]"] |
        join("; ")') | first }}
      report_only_count: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REPORT_ONLY")] |
        length') | first }}
      report_only_detail: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "REPORT_ONLY") |
        .OWNER + "." + .SEQUENCE_NAME + " [max " + .MAX_VALUE + " at the NUMBER
        ceiling, " + .REMAINING_VALUES + " left]"] | join("; ")') | first }}
      name_list: >-
        {{ outputs.audit.rows | jq('[.[] | select(.ACTION == "RAISE") | .OWNER +
        "." + .SEQUENCE_NAME] | (if length == 0 then ["__NO_TARGET__"] else .
        end) | join(",")') | first }}

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Oracle sequences on {{ inputs.jdbc_url }} for schemas {{
      inputs.sequence_owners | jq('map(ascii_upcase) | join(", ")') | first }}:
      {{ outputs.totals_before.row.SEQUENCE_COUNT }} total, {{
      outputs.totals_before.row.NOCYCLE_COUNT }} NOCYCLE, {{
      outputs.totals_before.row.CYCLE_COUNT }} CYCLE. {{
      outputs.totals_before.row.NOCYCLE_NEAR_MAX }} NOCYCLE sequence(s) have
      fewer than {{ inputs.min_headroom_values }} value(s) left before their
      MAXVALUE: {{ outputs.targets.values.raise_count }} can be raised ({{
      outputs.targets.values.raise_detail == '' ? 'none' :
      outputs.targets.values.raise_detail }}), {{
      outputs.targets.values.report_only_count }} are already at the NUMBER
      ceiling so a raise cannot help ({{
      outputs.targets.values.report_only_detail == '' ? 'none' :
      outputs.targets.values.report_only_detail }}). {{ outputs.excluded.size }}
      CYCLE sequence(s) are also low on headroom but wrap by design, so they are
      reported not raised ({{ outputs.excluded.rows | jq('map(.OWNER + "." +
      .SEQUENCE_NAME + " [max " + .MAX_VALUE + ", " + .REMAINING_VALUES + "
      left]") | join("; ")') | first }}).

  - id: route
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.audit.size > 0 }}"
    then:
      - id: alert
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {{ {'text': 'Oracle ' ~ inputs.jdbc_url ~ ': ' ~ outputs.audit.size ~ ' NOCYCLE sequence(s) are within ' ~ inputs.min_headroom_values ~ ' draws of their MAXVALUE and will stop every insert that uses them with ORA-08004 when they run out. Raisable: ' ~ (outputs.targets.values.raise_detail == '' ? 'none' : outputs.targets.values.raise_detail) ~ '. At the NUMBER ceiling, reported only: ' ~ (outputs.targets.values.report_only_detail == '' ? 'none' : outputs.targets.values.report_only_detail) ~ '. CYCLE sequences low on headroom but wrapping by design: ' ~ (outputs.excluded.size > 0 ? (outputs.excluded.rows | jq('map(.OWNER + "." + .SEQUENCE_NAME) | join(", ")') | first) : 'none') ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' A MAXVALUE raise is armed and waiting for approval.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: ceiling_route
        type: io.kestra.plugin.core.flow.If
        description: Explain the report-only sequences when there are any. A sequence
          whose MAXVALUE is already the 28 digit NUMBER limit cannot be given
          more room, so a raise is not the fix.
        condition: "{{ (outputs.targets.values.report_only_count | number) > 0 }}"
        then:
          - id: ceiling_explained
            type: io.kestra.plugin.core.log.Log
            level: WARN
            message: >-
              {{ outputs.targets.values.report_only_count }} sequence(s) are low
              on headroom while their MAXVALUE is already
              9999999999999999999999999999, the largest value a 28 digit Oracle
              NUMBER can hold ({{ outputs.targets.values.report_only_detail }}).
              Raising the ceiling cannot help here because there is no higher
              ceiling. The fix is a redesign. Recreate the sequence with a
              larger scale, move the column to a wider type, switch to CYCLE
              where repeated values are safe for that key. This flow reports
              these and leaves them untouched.
        else:
          - id: ceiling_clean
            type: io.kestra.plugin.core.log.Log
            message: "No flagged sequence is at the NUMBER ceiling, so every raise target
              has room to grow."

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run and
          (outputs.targets.values.raise_count | number) > 0 }}"
        then:
          - id: approval_gate
            type: io.kestra.plugin.core.flow.Pause
            description: Hold the run until a human approves raising the ceiling. Changing a
              sequence MAXVALUE is a schema change, so it never starts on its
              own even though raising a ceiling is additive and non-destructive.
            pauseDuration: PT2H
            behavior: FAIL
            onResume:
              - id: approved
                type: BOOL
                defaults: false
                description: Set true to run the ALTER SEQUENCE statements that raise the
                  flagged ceilings. Anything else leaves every sequence as it
                  is.

          - id: apply
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
            then:
              - id: remediate
                type: io.kestra.plugin.core.flow.Loop
                description: One ALTER SEQUENCE MAXVALUE per raise target. Sequential on
                  purpose, so two schema changes do not contend on the data
                  dictionary. The new ceiling is always above the current one,
                  so no MAXVALUE is ever lowered.
                concurrencyLimit: 1
                values: "{{ outputs.targets.values.raise_sql }}"
                tasks:
                  - id: raise_one
                    type: io.kestra.plugin.jdbc.oracle.Query
                    description: ALTER SEQUENCE for one sequence, raising its MAXVALUE.
                      allowFailure, so one sequence a privilege problem blocks
                      does not stop the pass. The re-read is what decides the
                      run.
                    allowFailure: true
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.username }}"
                    password: "{{ inputs.password }}"
                    fetchType: NONE
                    sql: "{{ item.value }}"

              - id: reverify
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Read dba_sequences again for the exact sequences that were raised.
                  Any that still have fewer than min_headroom_values draws left
                  did not get their new ceiling.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: FETCH
                sql: "{{ render(vars.reverify_sql) }}"

              - id: totals_after
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Re-read the per state counts so the run reports what the pass
                  changed.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.username }}"
                password: "{{ inputs.password }}"
                fetchType: FETCH_ONE
                sql: "{{ render(vars.totals_sql) }}"

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.reverify.size > 0 }}"
                then:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      {{ outputs.reverify.size }} of {{
                      outputs.targets.values.raise_count }} raised sequence(s)
                      are still within {{ inputs.min_headroom_values }} draws of
                      their MAXVALUE: {{ outputs.reverify.rows | jq('map(.OWNER
                      + "." + .SEQUENCE_NAME + " [max " + .MAX_VALUE + ", last "
                      + .LAST_NUMBER + ", " + .REMAINING_VALUES + " left]") |
                      join(", ")') | first }}. The schemas now hold {{
                      outputs.totals_after.row.NOCYCLE_NEAR_MAX }} NOCYCLE
                      sequence(s) low on headroom, from {{
                      outputs.totals_before.row.NOCYCLE_NEAR_MAX }} before.
                      ALTER SEQUENCE is refused when the caller lacks ALTER ANY
                      SEQUENCE for that schema, so check the privileges of the
                      user in the username input.
                else:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: >-
                      Raised the MAXVALUE of {{
                      fromJson(outputs.targets.values.raise_sql) | length }}
                      sequence(s): {{ outputs.targets.values.raise_detail }}.
                      Every one now has at least {{ inputs.min_headroom_values
                      }} value(s) of headroom. NOCYCLE sequences low on headroom
                      went from {{ outputs.totals_before.row.NOCYCLE_NEAR_MAX }}
                      to {{ outputs.totals_after.row.NOCYCLE_NEAR_MAX }}.
            else:
              - id: declined
                type: io.kestra.plugin.core.log.Log
                message: "Raise not approved. {{ outputs.targets.values.raise_count }} NOCYCLE
                  sequence(s) stay at their current MAXVALUE and will raise
                  ORA-08004 when they run out."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: >-
          No NOCYCLE sequence in the checked schemas is within {{
          inputs.min_headroom_values }} value(s) of its MAXVALUE. {{
          outputs.totals_before.row.NOCYCLE_COUNT }} NOCYCLE sequence(s)
          checked. {{ outputs.excluded.size }} CYCLE sequence(s) are low on
          headroom but wrap by design, so they are left alone.

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'Oracle sequence MAXVALUE exhaustion guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). A sequence may still be about to stop its inserts with ORA-08004. Check the logs.'} | toJson }}

triggers:
  - id: daily
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled and always in safe mode (report only).
    cron: "0 4 * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true