# Oracle Dba Advanced Features

> USE THIS SKILL WHENEVER the user mentions Oracle Advanced Compression / OLTP / Advanced Row compression / ROW STORE COMPRESS ADVANCED, Hybrid Columnar Compression (HCC) / COLUMN STORE COMPRESS FOR QUERY|ARCHIVE / Exadata-qualified compression, Database In-Memory / INMEMORY / IM column store / INMEMORY_SIZE / V$IM_SEGMENTS / MEMCOMPRESS, Partitioning implementation / RANGE|LIST|HASH|INTERVAL|composite / partition add-drop- split-merge / heap-to-partitioned conversion / partitioning strategy, Oracle Sharding / GDSCTL / GSM / shard director / shardcatalog / sharded & duplicated tables / chunks, or Result Cache / RESULT_CACHE_MODE / RESULT_CACHE_MAX_SIZE / DBMS_RESULT_CACHE — EVEN IF they do not name the specific task. Covers (T1) compression/HCC/In-Memory/partition/ Result-Cache/sharding INVENTORY & advisor reports; (T2, --dry-run default) OLTP-compress enable/disable, HCC attribute set, In-Memory per-object enable/disable, partition add/ drop on an already-partitioned table, and Result Cache configure; and (T3,

- Skill: `shreyas70773/oracle-dba-advanced-features` (Agent Skill, multi-file: 30 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-advanced-features`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-advanced-features/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Productivity
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-advanced-features

---

# Oracle DBA - Advanced Features

Compression (Basic/OLTP/Advanced Row and HCC), Database In-Memory, Partitioning, Oracle
Sharding, and server Result Cache for Oracle **19c and 23ai** (Enterprise Edition;
single-instance and Data Guard; on-prem). One mechanism by design for in-DB work plus
host-level runbooks for Sharding:

- **In-DB** work (compression/In-Memory/partition/Result-Cache DDL and all reports) runs
  through the **SQLcl MCP** saved connection `dba_ai_conn` (wallet-backed) → `sql/*.sql`,
  driven by the `scripts/*.sh` / `*.ps1` wrappers that enforce the dry-run default.
- **Host** work for **Sharding** (GDSCTL / `gsm` / `srvctl`, shard-director provisioning,
  chunk MOVE/SPLIT) **cannot** go through SQLcl MCP (restrict level 4) and is **T3
  runbook-only**; only the read-only catalog INSPECTION runs in-DB via `dba_ai_conn`.

Everything self-executing is **T1 (observe)** or **T2 (reversible, dry-run-default)**. Every
**bulk segment rewrite**, **In-Memory pool sizing / instance restart**, **heap→partitioned
conversion**, **partitioning strategy design**, and **all of Sharding setup/management** is
**T3** and lives only in `references/runbooks.md` — it prints commands for a human and never
self-executes.

## Scope & risk map

Maps every task in section 21 ("Advanced Features") of `oracle_dba_task_level_map.md`. The
T2 wrappers deliberately STOP at the safe/reversible boundary and point to the T3 runbook
for the structural variant.

| Task (map) | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------------|-------|------|------------------------------------------|-------------|
| Basic / OLTP compression implementation — inventory/advisor | L2 | T1 | `scripts/checks/check_compression.sh` / `.ps1` + `sql/compression_report.sql` | yes — read-only |
| Basic / OLTP compression implementation — enable/disable | L2 | T2 | `scripts/oltp_compression.sh` / `.ps1` (`enable`/`disable`) + `sql/oltp_compress_enable.sql` / `oltp_compress_disable.sql` | yes — already-(N)COMPRESS → noop |
| Hybrid Columnar Compression (HCC) — eligibility/storage probe | L3 | T1 | `scripts/hcc_compression.sh eligibility` / `.ps1` + `sql/hcc_eligibility_report.sql` | yes — read-only |
| Hybrid Columnar Compression (HCC) — set attribute (future loads) | L3 | T2 | `scripts/hcc_compression.sh set-attribute` / `.ps1` + `sql/hcc_set_attribute.sql` | yes — attribute already at level → noop |
| Hybrid Columnar Compression (HCC) — bulk segment rewrite (MOVE / redef of EXISTING rows) | L3 | **T3** | `references/runbooks.md#hcc-bulk-move` | no — full-segment rewrite, ~2x space |
| Database In-Memory configuration — pool + population report | L3 | T1 | `scripts/inmemory_object.sh report` / `.ps1` + `sql/inmemory_report.sql` | yes — read-only |
| Database In-Memory configuration — per-object enable/disable | L3 | T2 | `scripts/inmemory_object.sh enable`/`disable` / `.ps1` + `sql/inmemory_object.sql` | yes — already-(NO )INMEMORY → noop |
| Database In-Memory configuration — pool sizing (INMEMORY_SIZE/SGA, restart) | L3 | **T3** | `references/runbooks.md#inmemory-pool` | partial — SPFILE set repeatable; restart is the event |
| Database In-Memory configuration — enablement design (what to populate, sizing) | L3 | **T3** | `references/runbooks.md#inmemory-design` | n/a — design gate, no DDL |
| Partitioning implementation — inventory report | L3 | T1 | `scripts/partition_maint.sh report` / `.ps1` + `sql/partition_report.sql` | yes — read-only |
| Partitioning implementation — add/drop partition (already-partitioned table) | L3 | T2 | `scripts/partition_maint.sh add`/`drop` / `.ps1` + `sql/partition_add.sql` / `partition_drop.sql` | yes — partition present/absent → noop; INTERVAL add → noop |
| Partitioning implementation — heap→partitioned conversion (segment rewrite) | L3 | **T3** | `references/runbooks.md#partition-convert` | no — rewrites every dependent |
| Partitioning implementation — strategy design (key/method/interval/ILM) | L3 | **T3** | `references/runbooks.md#partition-strategy` | n/a — design gate, no DDL |
| Sharding setup & management — topology / chunk inspection | L3 | T1 | `scripts/checks/check_sharding.sh` / `.ps1` + `sql/sharding_topology_report.sql` | yes — read-only; non-catalog → noop note |
| Sharding setup & management — GDSCTL/GSM deploy, chunks, scale-out | L3 | **T3** | `references/runbooks.md#sharding` | partial — GDSCTL topology re-runnable; chunk/shard removal data-sensitive |
| Result cache configuration — config + hit/miss report | L3 | T1 | `scripts/result_cache.sh report` / `.ps1` + `sql/result_cache_report.sql` | yes — read-only |
| Result cache configuration — set MODE / MAX_SIZE / MAX_RESULT (dynamic) | L3 | T2 | `scripts/result_cache.sh configure` / `.ps1` + `sql/result_cache_configure.sql` | yes — only ALTERs params that differ → else noop |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB
  prechecks, postchecks, idempotency probes, and `sql/*.sql` run through it via
  `connect_db` / `Connect-Db`. **Never SYS/SYSTEM** — the connection's user owns the target
  object or holds the needed `ALTER`/`ALTER SYSTEM` privilege, granted out of band per site
  policy. SYSDBA is a **T3-only** exception, named inside a runbook (e.g. an instance
  restart for In-Memory pool sizing) and supplied interactively by the operator — never the
  default, never auto.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere** (argv, env, files). The shared helpers reject credential-looking arguments with
  `ERR_SECRET` (8); identifier and high-value inputs are validated before use.
- **Licensing is the operator's responsibility before any T2/T3 enable**: Advanced Row /
  OLTP and HCC need the **Advanced Compression Option**; **Database In-Memory**,
  **Partitioning**, and **Sharding** each need their option/feature licensed. Verify via
  `DBA_FEATURE_USAGE_STATISTICS` / `V$OPTION` (see the runbook pre-flight) BEFORE enabling
  anything that increments usage. Partitioning is included in EE.
- **Storage qualification for HCC**: HCC physically compresses only on Oracle-qualified
  storage (Exadata / ExaCC / ZFS Storage Appliance / qualified arrays). The eligibility
  report probes for this; on unqualified storage the attribute sets but loads silently fall
  back to no HCC — the wrapper warns.
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/advanced-features/advanced-features.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/advanced-features/advanced-features.log` (Windows); skill
  segment `advanced-features`. Non-zero exit on failure; never swallow errors.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute`. `partition drop` additionally requires `--confirm-drop`, and a non-empty
  partition requires `--allow-data-loss` on top of `--execute`.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2 scripts
default to `--dry-run`; pass `--execute` to act. See each script's `--help`.

### Compression inventory & advisor — T1
- **Trigger**: deciding what to compress; before an OLTP/HCC enable; periodic audit.
- **Precheck**: `dba_ai_conn` reachable.
- **Action**: `scripts/checks/check_compression.sh [--owner SCHEMA]` / `.ps1 [-Owner SCHEMA]` + `sql/compression_report.sql`.
- **Postcheck**: n/a — emits `compressed_tables` / `uncompressed_tables` metrics + candidate list.
- **Rollback note**: none — read-only.

### OLTP / Advanced Row compression enable / disable — T2
- **Trigger**: turn Advanced Row compression on (or off) for ONE table.
- **Precheck**: table exists.
- **Action**: `scripts/oltp_compression.sh enable|disable --owner O --table T [--online yes|no] --execute` / `.ps1 enable|disable -Owner O -Table T [-Online yes|no] --execute`. Dry-run prints the intended `ALTER TABLE`.
- **Postcheck**: `DBA_TABLES.COMPRESSION` is `ENABLED` (enable) / `DISABLED` (disable).
- **Rollback note**: reversible — run `disable` (sets `NOCOMPRESS`; `--online yes` re-expands existing rows). `enable`/`disable` are mutual inverses.

### HCC eligibility / storage qualification — T1
- **Trigger**: before considering HCC; confirm storage actually compresses.
- **Precheck**: `dba_ai_conn` reachable.
- **Action**: `scripts/hcc_compression.sh eligibility` / `.ps1 eligibility` + `sql/hcc_eligibility_report.sql`.
- **Postcheck**: n/a — emits `exadata_cells`; logs `finding=no_hcc_qualified_storage` when 0.
- **Rollback note**: none — read-only.

### HCC set attribute (future loads only) — T2
- **Trigger**: flag a cold/archive table so FUTURE direct-path loads compress columnar.
- **Precheck**: table exists. (Storage qualification is advisory — run `eligibility` first.)
- **Action**: `scripts/hcc_compression.sh set-attribute --owner O --table T --level QUERY_LOW|QUERY_HIGH|ARCHIVE_LOW|ARCHIVE_HIGH --execute` / `.ps1 ... -Level ...`. Dry-run prints the `COLUMN STORE COMPRESS FOR` clause.
- **Postcheck**: `DBA_TABLES.COMPRESS_FOR` reflects the chosen level.
- **Rollback note**: reversible — `ALTER TABLE ... NOCOMPRESS` (per runbook). Rewriting EXISTING rows is **T3 `#hcc-bulk-move`** (this wrapper never moves segments).

### In-Memory pool + population report — T1
- **Trigger**: see the IM column-store size, populated segments, flagged objects.
- **Precheck**: `dba_ai_conn` reachable.
- **Action**: `scripts/inmemory_object.sh report` / `.ps1 report` + `sql/inmemory_report.sql`.
- **Postcheck**: n/a — emits `im_segments_populated` / `tables_flagged_inmemory`.
- **Rollback note**: none — read-only.

### In-Memory per-object enable / disable — T2
- **Trigger**: mark a scan-heavy table `INMEMORY` (or clear it).
- **Precheck**: table exists AND (for `enable`) `INMEMORY_SIZE > 0`. If the pool is 0, the precheck STOPS and points to **T3 `#inmemory-pool`** (sizing needs a restart).
- **Action**: `scripts/inmemory_object.sh enable --owner O --table T [--level MEMCOMPRESS_QUERY] [--priority HIGH] --execute` / `disable --owner O --table T --execute` / `.ps1 -Owner -Table -Level -Priority`.
- **Postcheck**: `DBA_TABLES.INMEMORY` is `ENABLED` (enable) / `DISABLED` (disable).
- **Rollback note**: reversible — run `disable` (`NO INMEMORY`). Pool sizing and what-to-populate design are **T3 `#inmemory-pool`** / **`#inmemory-design`**.

### Partition inventory — T1
- **Trigger**: review partitioned tables, intervals, per-partition size, index locality/status.
- **Precheck**: `dba_ai_conn` reachable.
- **Action**: `scripts/partition_maint.sh report` / `.ps1 report` + `sql/partition_report.sql`.
- **Postcheck**: n/a — emits `partitioned_tables` / `unusable_part_indexes`.
- **Rollback note**: none — read-only.

### Partition add / drop (already-partitioned table) — T2
- **Trigger**: pre-create the next RANGE/LIST partition, or roll off an old (empty) one.
- **Precheck**: table exists and IS partitioned (else points to **T3 `#partition-convert`**).
- **Action**: `scripts/partition_maint.sh add --owner O --table T --name P --type RANGE|LIST --high-value "..." --execute` / `drop --owner O --table T --name P [--update-indexes yes|no] --confirm-drop [--allow-data-loss] --execute` / `.ps1 ... -Name -Type -HighValue ... -ConfirmDrop [-AllowDataLoss]`. Dry-run prints the `ALTER TABLE`. INTERVAL tables auto-create RANGE partitions → `add` no-ops.
- **Postcheck**: target partition present (add) / absent (drop).
- **Rollback note**: an `add` is undone by `drop` of that (empty) partition. A `drop` of a NON-EMPTY partition is **data loss** — recoverable only via backup/Flashback; that is why `--allow-data-loss` is mandatory. Heap→partitioned conversion is **T3 `#partition-convert`**; strategy is **T3 `#partition-strategy`**.

### Sharding topology / chunk inspection — T1
- **Trigger**: inspect a shard catalog's topology, chunk balance, sharded tables.
- **Precheck**: `dba_ai_conn` reachable (run on the SHARD CATALOG database).
- **Action**: `scripts/checks/check_sharding.sh` / `.ps1` + `sql/sharding_topology_report.sql`. Gracefully degrades on a non-catalog DB (logs `finding=not_a_shard_catalog`).
- **Postcheck**: n/a — emits `gsmcatalog_views`.
- **Rollback note**: none — read-only. ALL sharding setup/management is **T3 `#sharding`** (host-level GDSCTL/GSM; cannot go through SQLcl MCP).

### Result Cache configure — T2
- **Trigger**: enable/size/disable the server Result Cache (dynamic, no restart).
- **Precheck**: `result_cache_mode` and `result_cache_max_size` parameters present.
- **Action**: `scripts/result_cache.sh configure --mode MANUAL|FORCE --max-mb N [--max-result-pct P] [--scope BOTH|MEMORY|SPFILE] --execute` / `.ps1 -Mode -MaxMb -MaxResultPct -Scope`. `--max-mb 0` disables. Dry-run prints the intended values; only differing params are ALTERed.
- **Postcheck**: live `result_cache_mode` equals the requested mode.
- **Rollback note**: fully reversible — re-run with the previous values (capture them from `report` first); `--max-mb 0` disables. In multitenant, `dba_ai_conn` binds the PDB/CDB scope.

## Tier-3 runbooks

All **structural / HA-impacting / restart-requiring** Advanced-Features work is Tier 3 and
lives in **`references/runbooks.md`**: `#hcc-bulk-move` (rewrite EXISTING rows into HCC),
`#inmemory-pool` (INMEMORY_SIZE/SGA + instance restart), `#inmemory-design` (what to
populate + sizing model), `#partition-convert` (heap→partitioned), `#partition-strategy`
(key/method/interval/ILM design), and `#sharding` (GDSCTL/GSM deploy + chunk management).

These runbooks **PRINT exact, copy-pasteable commands for a human operator and NEVER
self-execute.** They are gated by `require_approval_token` / `Require-ApprovalToken`: an
automated agent's only sanctioned action is to print the relevant section and stop with
`ERR_APPROVAL` (6) until a human supplies a real change ticket/token (`--token <TICKET>` or
`ODB_APPROVAL_TOKEN`; placeholders like empty/`TODO`/`CHANGE_ME`/`none` are rejected; the
token is never logged). The T2 wrappers deliberately refuse the destructive paths (HCC
attribute only — never a MOVE; In-Memory per-object only — never pool sizing; partition
add/drop only — never a heap conversion; sharding inspection only — never GDSCTL) and point
to the runbook instead. SYSDBA is a T3-only, interactively-supplied exception. Always
confirm a fresh, verified backup via the **backup-and-recovery** skill
(`scripts/checks/check_backup_status.sh`) before any structural change. **When in doubt,
runbook.**

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas (compression syntax,
In-Memory dynamic sizing, partitioning, Sharding RAFT replication, Result Cache).