Oracle Tablespace Autoextend Exhaustion Guard

id: oracle-tablespace-autoextend-exhaustion-guard
namespace: company.team

description: |
  Find permanent Oracle tablespaces that are near full and whose datafiles cannot grow,
  the classic ORA-01653 "unable to extend" outage. The guard reads dba_tablespaces,
  dba_data_files and dba_free_space, flags every non-autoextensible datafile in a permanent
  tablespace at or above a used-percent threshold, alerts Slack and can enable bounded
  autoextend (AUTOEXTEND ON NEXT 256M MAXSIZE <cap>) on each flagged datafile behind an
  approval, then re-reads the data dictionary and fails the run if any flagged datafile is
  still fixed.

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

  - id: db_user
    type: STRING
    displayName: Database user
    description: A user with SELECT_CATALOG_ROLE for the audit, plus ALTER DATABASE
      for remediation.
    defaults: system

  - id: db_password
    type: STRING
    displayName: Database password
    description: Defaults to the ORACLE_PASSWORD secret. Override per run for local
      testing only.
    defaults: "{{ secret('ORACLE_PASSWORD') }}"

  - id: used_pct_threshold
    type: INT
    displayName: Used percent threshold
    description: A permanent tablespace at or above this percent used, holding a
      non-autoextensible datafile, is flagged.
    defaults: 90

  - id: datafile_max_size_mb
    type: INT
    displayName: Autoextend cap per datafile (MB)
    description: MAXSIZE applied when autoextend is enabled. The cap is never set
      below a datafile current size.
    defaults: 2048

  - id: excluded_tablespaces
    type: ARRAY
    itemType: STRING
    displayName: Tablespaces not checked
    description: Permanent tablespaces allowed to stay fixed size, for example a
      sealed archive tablespace.
    defaults: []

  - id: auto_remediate
    type: BOOL
    displayName: Auto remediate
    defaults: false

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

variables:
  audit_sql: |
    WITH perm AS (
      SELECT tablespace_name FROM dba_tablespaces
       WHERE contents = 'PERMANENT' AND status = 'ONLINE'
         AND tablespace_name NOT IN ({{ inputs.excluded_tablespaces | jq('if length == 0 then "\u0027__NO_EXCLUSION__\u0027" else map("\u0027" + gsub("\u0027"; "\u0027\u0027") + "\u0027") | join(",") end') | first }})
    ), alloc AS (
      SELECT tablespace_name, SUM(bytes) alloc_bytes FROM dba_data_files GROUP BY tablespace_name
    ), freesp AS (
      SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name
    )
    SELECT d.tablespace_name,
           d.file_id,
           d.file_name,
           d.autoextensible,
           ROUND(d.bytes/1024/1024) file_mb,
           ROUND(a.alloc_bytes/1024/1024) ts_alloc_mb,
           ROUND(NVL(f.free_bytes,0)/1024/1024) ts_free_mb,
           ROUND((a.alloc_bytes - NVL(f.free_bytes,0)) / a.alloc_bytes * 100, 1) pct_used,
           'ALTER DATABASE DATAFILE ''' || REPLACE(d.file_name, '''', '''''') || ''' AUTOEXTEND ON NEXT 256M MAXSIZE ' || GREATEST({{ inputs.datafile_max_size_mb }}, CEIL(d.bytes/1024/1024)) || 'M' AS fix_sql
      FROM dba_data_files d
      JOIN perm p ON p.tablespace_name = d.tablespace_name
      JOIN alloc a ON a.tablespace_name = d.tablespace_name
      LEFT JOIN freesp f ON f.tablespace_name = d.tablespace_name
     WHERE d.autoextensible = 'NO'
       AND (a.alloc_bytes - NVL(f.free_bytes,0)) / a.alloc_bytes * 100 >= {{ inputs.used_pct_threshold }}
     ORDER BY pct_used DESC, d.tablespace_name, d.file_id
  reverify_sql: |
    SELECT d.tablespace_name, d.file_id, d.file_name, d.autoextensible,
           ROUND(d.bytes/1024/1024) file_mb, ROUND(d.maxbytes/1024/1024) max_mb
      FROM dba_data_files d
      JOIN dba_tablespaces t ON t.tablespace_name = d.tablespace_name
     WHERE t.contents = 'PERMANENT' AND t.status = 'ONLINE'
     ORDER BY d.autoextensible, d.tablespace_name, d.file_id

tasks:
  - id: audit
    type: io.kestra.plugin.jdbc.oracle.Query
    description: Read-only. Resolves used percent per permanent tablespace from
      dba_data_files and dba_free_space, then returns each non-autoextensible
      datafile at or above the threshold with the fix statement.
    url: "{{ inputs.jdbc_url }}"
    username: "{{ inputs.db_user }}"
    password: "{{ inputs.db_password }}"
    fetchType: FETCH
    sql: "{{ render(vars.audit_sql) }}"

  - id: targets
    type: io.kestra.plugin.core.output.OutputValues
    description: Collect the per-datafile ALTER DATABASE statements, de-duplicated.
    values:
      fix_sql: "{{ outputs.audit.rows | jq('map(.FIX_SQL) | unique') | first | toJson
        }}"

  - id: summary
    type: io.kestra.plugin.core.log.Log
    message: >-
      Oracle autoextend audit on {{ inputs.jdbc_url }}: {{ outputs.audit.size }}
      non-autoextensible datafile(s) in a permanent tablespace at or above {{
      inputs.used_pct_threshold }}% used. {{ outputs.audit.rows |
      jq('map("\(.TABLESPACE_NAME) \(.FILE_NAME) \(.PCT_USED)% used, autoextend
      \(.AUTOEXTENSIBLE)") | 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 ~ ' flow ' ~ flow.id ~ ' execution ' ~ execution.id ~ ': ' ~ outputs.audit.size ~ ' non-autoextensible datafile(s) in a full permanent tablespace (ORA-01653 risk): ' ~ (outputs.audit.rows | jq('map("\(.TABLESPACE_NAME) (\(.PCT_USED)% used)") | unique | join(", ")') | first) ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Enabling bounded autoextend now.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}

      - id: remediate_gate
        type: io.kestra.plugin.core.flow.If
        condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
        then:
          - id: hold
            type: io.kestra.plugin.core.flow.Pause
            description: Pause for a DBA to approve enabling autoextend on the flagged
              datafiles before any ALTER DATABASE runs.
            onResume:
              - id: approved
                type: BOOL
                defaults: false
                description: Set true to apply bounded autoextend to the flagged datafiles.

          - id: approval_gate
            type: io.kestra.plugin.core.flow.If
            condition: "{{ outputs.hold.onResume.approved ?? false }}"
            then:
              - id: remediate
                type: io.kestra.plugin.core.flow.Loop
                description: Sequential remediation. One ALTER DATABASE DATAFILE per flagged
                  file, concurrencyLimit 1 so the controlfile updates never
                  overlap.
                concurrencyLimit: 1
                values: "{{ outputs.targets.values.fix_sql }}"
                tasks:
                  - id: enable_autoextend
                    type: io.kestra.plugin.jdbc.oracle.Query
                    allowFailure: true
                    url: "{{ inputs.jdbc_url }}"
                    username: "{{ inputs.db_user }}"
                    password: "{{ inputs.db_password }}"
                    fetchType: NONE
                    sql: "{{ item.value }}"

              - id: reaudit
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Re-read the data dictionary with the same detection query.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.db_user }}"
                password: "{{ inputs.db_password }}"
                fetchType: FETCH
                sql: "{{ render(vars.audit_sql) }}"

              - id: reverify
                type: io.kestra.plugin.jdbc.oracle.Query
                description: Read autoextensible and maxbytes for every permanent datafile, for
                  the after state.
                url: "{{ inputs.jdbc_url }}"
                username: "{{ inputs.db_user }}"
                password: "{{ inputs.db_password }}"
                fetchType: FETCH
                sql: "{{ render(vars.reverify_sql) }}"

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                condition: "{{ outputs.reaudit.size > 0 }}"
                then:
                  - id: remediation_failed
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: >-
                      {{ outputs.reaudit.size }} datafile(s) are still fixed
                      size and at or above {{ inputs.used_pct_threshold }}% used
                      after remediation: {{ outputs.reaudit.rows |
                      jq('map("\(.TABLESPACE_NAME) \(.FILE_NAME)") | join(",
                      ")') | first }}. The connecting user needs ALTER DATABASE.
                      See the enable_autoextend step logs.
                else:
                  - id: remediated
                    type: io.kestra.plugin.core.log.Log
                    message: >-
                      Enabled bounded autoextend and re-checked. No
                      non-autoextensible datafile remains at or above {{
                      inputs.used_pct_threshold }}% used. Applied: {{
                      fromJson(outputs.targets.values.fix_sql) | join('; ') }}.
                      Permanent datafiles now: {{ outputs.reverify.rows |
                      jq('map("\(.TABLESPACE_NAME) \(.FILE_NAME)
                      autoextend=\(.AUTOEXTENSIBLE) maxMB=\(.MAX_MB)") | join(";
                      ")') | first }}.
            else:
              - id: not_approved
                type: io.kestra.plugin.core.log.Log
                message: "Autoextend changes were not approved at the pause. No datafile was
                  altered."
    else:
      - id: clean
        type: io.kestra.plugin.core.log.Log
        message: "Every permanent tablespace is either below the threshold or has
          autoextensible datafiles. Nothing to remediate."

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {{ {'text': 'Oracle tablespace autoextend guard failed on ' ~ inputs.jdbc_url ~ ' (flow ' ~ flow.id ~ ', execution ' ~ execution.id ~ '). Check the logs.'} | toJson }}

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