# Oracle Dba Storage > USE THIS SKILL for ANY Oracle space & storage request — tablespaces (create, add datafile, resize, bigfile), temp/undo sizing & retention, segment/table reorganization, tablespace defragmentation, ASM disk add/drop/rebalance, archive log / FRA / diag-trace / listener-log space reclamation, and segment/datafile shrink or storage-tiering (ILM/ADO) design. Invoke whenever the user mentions "out of space", ORA-01653/ORA-01654/ORA-01688/ORA-30036/ORA-39171, "tablespace full", "FRA full", "archive destination full", "shrink", "reclaim", "defrag", "rebalance", or capacity planning for storage. Follows oracle-dba-common contracts: SQLcl saved connection dba_ai_conn (wallet secrets — never plaintext), --dry-run default for all T2+ actions (--execute to act), full structured logging, and tier gating (T3 is runbook-only + human approval token). When in doubt, runbook. - Skill: `shreyas70773/oracle-dba-storage` (Agent Skill, multi-file: 44 files) - Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-storage` - Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-storage/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: Shreyas70773 (https://skillmd.com/u/shreyas70773) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/shreyas70773/oracle-dba-storage --- # Oracle DBA - Space & Storage Management Domain skill for capacity and storage operations. Loads `oracle-dba-common` first (`_common/scripts/lib.sh` / `lib.ps1`, `_common/references/conventions.md`). Max self-execute tier = **T2**. T3 is **runbook-only**. ## Scope & risk map Every task is listed with its level, tier, mechanism path, and idempotency. 15 tasks: 8 L1, 5 L2, 2 L3. | Task | Level | Tier | Mechanism (sql / script / runbook) | Idempotent? | |------|-------|------|------------------------------------|-------------| | Space & storage health report | L1 | T1 | `sql/space_overview.sql` | yes (read-only) | | Archive log status (cleanup precheck) | L1 | T1 | `sql/archivelog_status.sql` | yes (read-only) | | FRA usage report (reclaim precheck) | L1 | T1 | `sql/fra_usage.sql` | yes (read-only) | | ASM status report | L1 | T1 | `sql/asm_status.sql` via `scripts/asm_disk_manage.{sh,ps1} status` | yes (read-only) | | Reorg candidates report | L1 | T1 | `sql/reorg_candidates.sql` via `scripts/segment_reorg.{sh,ps1} candidates` | yes (read-only) | | Add datafile to tablespace | L1 | T2 | `scripts/add_datafile.{sh,ps1}` -> `sql/add_datafile.sql` | yes (tag-detected NOOP) | | Datafile / tempfile resize + autoextend | L1 | T2 | `scripts/datafile_resize.{sh,ps1}` -> `sql/datafile_resize.sql` | yes (grow-only; <=cur = NOOP) | | Temp tablespace management (addfile/autoextend/shrink/setdefault) | L1 | T2 | `scripts/temp_manage.{sh,ps1}` -> `sql/temp_manage.sql` | yes (tag/default NOOP) | | Undo tablespace sizing / retention | L1 | T2 | `scripts/undo_retention.{sh,ps1}` -> `sql/undo_retention.sql` | yes (unchanged = NOOP); **Partial** (TS *switch* is T3) | | Archive log cleanup | L1 | T2 | `scripts/archivelog_cleanup.{sh,ps1}` (RMAN crosscheck+delete) | yes (crosscheck/delete re-runnable) | | FRA space reclamation | L1 | T2 | `scripts/fra_reclaim.{sh,ps1}` (RMAN delete obsolete/within retention) | yes | | Trace/audit/dump cleanup (adump, diag) | L1 | T2 | `scripts/diag_cleanup.{sh,ps1}` (ADRCI purge + adump age-prune) | yes (age-based) | | Listener / alert log rotation | L1 | T2 | `scripts/log_rotate.{sh,ps1}` (ADRCI purge + listener log rotate) | yes (size/age threshold) | | Create tablespace (perm/temp/undo, smallfile/bigfile) | L2 | T2 | `scripts/tablespace_create.{sh,ps1}` -> `sql/tablespace_create.sql` | yes (exists = NOOP) | | Bigfile tablespace management (resize/autoextend/info) | L2 | T1/T2 | `scripts/bigfile_manage.{sh,ps1}` -> `sql/bigfile_manage.sql` | yes (info=T1; grow-only) | | Segment / table reorganization (online MOVE) | L2 | T1/T2 | `scripts/segment_reorg.{sh,ps1}` -> `sql/segment_reorg.sql` | yes-aware (candidates=T1; reorg=T2) | | Tablespace defragmentation (coalesce / rebuild idx) | L2 | T2 | `scripts/tablespace_defrag.{sh,ps1}` -> `sql/tablespace_defrag.sql` | yes (LMT coalesce / UNUSABLE-only) | | ASM disk add / rebalance | L2 | T2 | `scripts/asm_disk_manage.{sh,ps1} add\|rebalance` | yes (member path skipped) | | Datafile / segment shrink & reclamation | L3 | **T3** | `references/runbooks.md#datafile-segment-shrink` | no (runbook-only) | | ASM disk drop | L3 | **T3** | `scripts/asm_disk_manage.{sh,ps1} drop` -> `references/runbooks.md#asm-disk-drop` | no (runbook-only) | | Storage tiering / ILM (ADO) policy design | L3 | **T3** | `references/runbooks.md#storage-tiering-ilm` | no (runbook-only) | | Drop datafile (reverse an add) | L3 | **T3** | `references/runbooks.md#drop-datafile` | no (runbook-only) | | Drop tempfile | L3 | **T3** | `references/runbooks.md#drop-tempfile` | no (runbook-only) | | Undo tablespace switch/replace | L3 | **T3** | `references/runbooks.md#undo-tablespace-switch` | no (runbook-only) | > The 15 in-scope automatable tasks are the L1/L2 rows. The L3 rows are the > destructive/structural escalation paths several T2 wrappers defer to (drop, shrink, > ILM, undo-switch) — delivered as PRINT-only runbooks. ## Preconditions - SQLcl MCP saved connection **`dba_ai_conn`** reachable (wallet-backed; never SYS/SYSTEM unless a runbook explicitly needs SYSDBA). All in-DB SQL runs via `connect_db` / `Connect-Db`. - Secrets resolve from the **Oracle Wallet** / external password store. No plaintext on argv/env/files anywhere. Storage paths and ASM disk-group names are NOT secrets. - Helpers sourced from `_common/scripts/lib.sh` (Linux) / `lib.ps1` (Windows). Every OS-level task ships a behaviour-equivalent `.sh` + `.ps1` pair that passes a parse check. - **ASM exception:** `asm_disk_manage.{sh,ps1}` are HOST operations against the ASM instance and **cannot** use `connect_db` (that helper targets the DB instance, and the SQLcl MCP server is host-restricted). They shell to `sqlplus "/ as sysasm"` with **OS authentication only** (no creds on argv/env; output redacted via `_odb_redact`). This is an intentional, documented exception — see `references/version-notes.md` §7. ## Procedures For each task: - **Trigger**: a space/capacity symptom or an explicit request (see `description`). - **Precheck**: `precheck` / `Invoke-Precheck` read-only guard (connection + object exists + capacity/safety, e.g. archivelog applied-to-standby & backed-up before any delete). - **Action**: the `scripts/.{sh,ps1}` wrapper. T2+ default to `--dry-run` (preview, change nothing); pass `--execute` to act. No long inline code lives here. - **Postcheck**: `postcheck` / `Invoke-Postcheck` verifies the goal (e.g. file >= target, tablespace exists via anchored KEYVAL match, table in target TS with 0 UNUSABLE indexes, FRA reclaimable dropped). - **Rollback note**: grow-only ops reverse by RESIZE-up / removing the freshly added file (that removal is the T3 `drop-datafile`/`drop-tempfile` runbook); undo_retention prints the previous value/RETENTION setting; reorg moves back to the original tablespace; ASM add reverses via the `asm-disk-drop` runbook. Destructive reversals go through runbooks. ### Space-reclamation safety gate (archivelog / FRA / diag / log_rotate) These T2 wrappers DELETE files, so they are gated harder than a normal T2: - **Within retention only.** Deletion is permitted only for archived logs/backups that are already applied to all required standbys AND backed up, or RMAN-`OBSOLETE` within the configured retention policy. The read-only `archivelog_status.sql` / `fra_usage.sql` KEYVALs (`arch_not_applied_to_standby`, `arch_unbacked_on_disk`, `fra_reclaimable_gb`) are the precheck; if anything recovery-required would be touched, the wrapper refuses. - **Beyond retention / force-delete is T3** — never an unguarded `rm`/`DELETE`; escalate to a runbook + `require_approval_token`. - `diag_cleanup` / `log_rotate` only age/size-prune ADR (trace/incident/cdump), the OS `audit_file_dest` `.aud` files, and the listener/alert logs — never datafiles/redo/backups. ## Tier-3 runbooks See `references/runbooks.md`. Anchored sections: `#datafile-segment-shrink`, `#storage-tiering-ilm`, `#asm-disk-drop`, `#drop-datafile`, `#drop-tempfile`, `#undo-tablespace-switch`. They PRINT exact, copy-pasteable 19c+23ai commands with a pre-flight checklist, diagnostics, verification, and rollback for a human operator, and NEVER self-execute — gated by `require_approval_token` / `Require-ApprovalToken`. ## Scheduling (cron / Task Scheduler) See `cron/crontab.d` (Linux) and `cron/task-scheduler.xml` (Windows). They schedule ONLY the safe, repeatable jobs: the T1 space health report (read-only) and the T2 reclamation wrappers (`archivelog_cleanup`, `fra_reclaim`, `diag_cleanup`, `log_rotate`) with `--execute`. The T2 add/resize/autoextend/reorg/defrag wrappers are operator-initiated (a real storage change is a decision) and are intentionally NOT timer-driven. NO T3 action (shrink, drop, ILM apply, undo switch) is ever scheduled — those stay runbook-only behind `require_approval_token`. ## Version notes See `references/version-notes.md` for 19c-vs-23ai deltas (online MOVE LOB/partition/IOT/ domain-index caveats, segment/datafile shrink, Heat Map/ADO availability + Advanced Compression licensing, temp/undo syntax, RMAN/ADRCI reclamation, and the ASM SYSASM OS-auth exception).