---
name: oracle-dba
description: "Use when the user asks to \"manage Autonomous AI Database\", \"debug ADB performance\", \"fix wallet connection\", \"optimize ECPU cost\", or \"use SQLcl with Oracle Database\"."
version: 2.0.0
keywords:
  - "Oracle Database"
  - "Autonomous AI Database"
  - "ADB"
  - "ECPU"
  - "OCPU"
  - "wallet"
  - "SQLcl"
  - "SQL_ID"
  - "wait events"
  - "Data Guard"
aliases:
  - "autonomous-database"
  - "oracle-autonomous-database"
  - "adb"
domains:
  - "oracle"
  - "database"
title: oracle-dba
canonical_url: https://skilld.dev/gh/acedergren/agentic-tools/oracle-dba
last_updated: 2026-09-29T02:04:52.000Z
---

> **Skill from skilld.dev.** Follow the instructions below for this session. You do not need to install anything.
>
> Supporting files, fetch one when the Skill refers to it: [references/adb-ha-dr.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/adb-ha-dr.md), [references/adb-security.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/adb-security.md), [references/api_reference.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/api_reference.md), [references/cost-reference.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/cost-reference.md), [references/mcp-tools.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/mcp-tools.md), [references/oci-adb-best-practices.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/oci-adb-best-practices.md), [references/oci-cli-adb.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/oci-cli-adb.md), [references/oci-cli.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/oci-cli.md), [references/security-remediation.sql](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/security-remediation.sql), [references/sql-patterns.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/sql-patterns.md), [references/sqlcl-workflows.md](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/sqlcl-workflows.md).
>
> If the user asked to install this Skill, run `npx skilld install acedergren/agentic-tools/oracle-dba`. Install writes the Skill files into the project, so every session loads them.

# Oracle Autonomous AI Database - Expert Knowledge

Use Autonomous AI Database and ADB as current/common terminology. Prefer ECPU wording for new guidance; keep OCPU only when quoting legacy configurations, API fields, or older docs that still use it.

## Do NOT load this skill when

Do not load this skill for unrelated general programming, non-Oracle cloud work, or questions covered by a narrower sibling skill.
When the request is only asking to find or install skills, use `find-skills` instead.

## When to Use

Load this skill for: the user asks to "manage Autonomous AI Database", "debug ADB performance", "fix wallet connection", "optimize ECPU cost", or "use SQLcl with Oracle Database".

Prefer this skill only for its named domain. For broader OCI architecture triage, start with `oci/best-practices` as the router.

## NEVER Do This

**NEVER use ADMIN user in application code**

ADMIN has full database control; audit trail shows all actions as ADMIN (no accountability); ADMIN cannot be locked/disabled without breaking automation.
```sql
-- RIGHT: create app-specific user with least privilege
CREATE USER app_user IDENTIFIED BY :password;
GRANT CREATE SESSION, SELECT ON schema.table TO app_user;
```

**NEVER scale ECPUs without checking wait events first**

Scaling ECPUs without proof can waste budget. If root cause is bad SQL, more compute only hides the defect.
```
Decision path:
1. Check v$system_event for top wait events
2. High 'CPU time' → Bad SQL, optimize first (do NOT scale)
3. High 'db file sequential read' → Missing indexes (do NOT scale)
4. High 'User I/O' sustained → Scale storage IOPS OR enable auto-scaling
5. Only scale ECPUs if: CPU wait sustained + SQL already optimized
```

**NEVER assume stopped ADB = zero cost**
```
Stopped ADB charges:
  CPU/ECPU billing: stopped
  Storage: continues
  Backups and retained resources: can continue

For long-term idle (>60 days): Export via Data Pump, delete ADB, restore from backup.
```

**NEVER create manual backups without retention (kept forever)**
```bash
# WRONG - retained until explicitly removed, with ongoing storage impact
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup"

# RIGHT - set retention
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup" \
  --retention-days 30
```

**NEVER enable auto-scaling without setting a max ECPU limit**
```
Auto-scaling can bill for elevated usage during the hour.
Base 2 ECPU → can scale to 6 ECPU (3× hard limit).
Without max cap: surprise spend is easy.

RIGHT: Set a Max ECPU cap that matches the budget and workload SLO.
```

**NEVER use ROWNUM with ORDER BY (wrong results)**
```sql
-- WRONG: ROWNUM applied BEFORE ORDER BY
SELECT * FROM orders WHERE ROWNUM <= 10 ORDER BY created_at DESC;

-- RIGHT: FETCH FIRST (Oracle 12c+)
SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;
```

---

## Performance Troubleshooting Decision Tree

```
"Queries are slow"
│
├─ ONE query slow?
│  └─ Get SQL_ID → check execution plan:
│     SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
│     ├─ TABLE ACCESS FULL on large table → Add index
│     ├─ Wrong join order → SQL hints or SQL Plan Baseline
│     └─ Cartesian join → Fix query logic
│
├─ ALL queries slow (system-wide)?
│  └─ Check wait events:
│     SELECT event, time_waited_micro/1000000 AS wait_sec
│     FROM v$system_event WHERE wait_class != 'Idle'
│     ORDER BY time_waited_micro DESC FETCH FIRST 10 ROWS ONLY;
│     ├─ 'CPU time' → Optimize SQL OR scale ECPU (check SQL first)
│     ├─ 'db file sequential read' → Missing indexes
│     ├─ 'db file scattered read' → Full table scans
│     ├─ 'log file sync' → Too many commits (batch DML)
│     └─ 'User I/O' → Scale storage IOPS or enable auto-scaling
│
└─ When did it start?
   ├─ After schema change → DBMS_STATS.GATHER_TABLE_STATS
   ├─ After data load → Gather stats + check partitioning
   ├─ After version upgrade → Compare execution plans
   └─ Gradual over time → Data growth, need indexing/partitioning
```

---

## SQL_ID Debugging Workflow

**Step 1: Find problem SQL_ID**
```sql
SELECT sql_id, elapsed_time/executions/1000 AS avg_ms,
       executions, sql_text
FROM v$sql
WHERE executions > 0
  AND last_active_time > SYSDATE - 1/24  -- last hour
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
```

**Step 2: Get execution plan**
```sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
```

**Step 3: Create and run SQL Tuning Task**
```sql
DECLARE task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => '&sql_id', task_name => 'tune_slow_query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_slow_query') FROM DUAL;
```

**Step 4: Implement fix**
- Recommendation: Add index → create index
- Recommendation: Use hint → test, then fix via SQL Plan Baseline
- Recommendation: Gather stats → `EXEC DBMS_STATS.GATHER_TABLE_STATS('schema','table')`

---

## ADB-Specific Behaviors

**Auto-scaling hard limits (cannot change):**
```
Minimum: 1× base ECPU
Maximum: 3× base ECPU
Scale-up trigger: CPU > 80% for 5+ minutes
Scale-down trigger: CPU < 60% for 10+ minutes
Time to scale: 5-10 minutes
Billing: charged for PEAK usage each hour
```

**ADMIN user restrictions in ADB (differs from on-premises):**
```
CANNOT: Create tablespaces (DATA auto-managed)
CANNOT: Modify SYSTEM/SYSAUX tablespaces
CANNOT: Access OS (no shell, no file system)
CANNOT: Use SYSDBA privileges (not available in ADB)
```

**Service name performance impact:**

| Service | Relative priority | Use For |
|---------|-------------------|---------|
| HIGH | Highest priority, least sharing | Interactive queries, OLTP |
| MEDIUM | Balanced sharing | Reporting, batch |
| LOW | Most sharing | Background tasks, ETL |

Gotcha: Using HIGH for background jobs starves interactive users with no extra cost benefit.

**Backup retention (automatic vs manual):**
```
Automatic: Daily incremental + weekly full, 60-day default, INCLUDED in storage cost
Manual: On-demand, retained until policy or manual deletion
Cost trap: forgotten manual backups keep consuming storage budget
```

---

## Version Feature Matrix

| Feature | 19c | 21c | 23ai | 26ai | Use Case |
|---------|-----|-----|------|------|----------|
| JSON Relational Duality | - | - | ✓ | ✓ | REST + SQL modern apps |
| AI Vector Search | - | - | ✓ | ✓ | RAG, semantic search |
| JavaScript Stored Procs | - | - | - | ✓ | Node.js developers |
| SELECT AI (NL→SQL) | - | - | ✓ | ✓ | Natural language queries |
| Property Graphs | - | ✓ | ✓ | ✓ | Fraud detection, social |
| True Cache | - | - | - | ✓ | Read-heavy workloads |
| Blockchain Tables | - | ✓ | ✓ | ✓ | Immutable audit log |

**Upgrade path**: 19c → 21c → 23ai → 26ai (downgrade NOT supported)
**Rule**: Always test in clone before upgrading production.

---

## Common ADB Errors

| Error | Actual Cause | Fix |
|-------|-------------|-----|
| `ORA-01017: invalid username/password` | Wallet password wrong or expired | Re-download wallet |
| `ORA-12170: Connect timeout` | NSG rules blocking OR wrong service name | Check NSG, verify tnsnames.ora |
| `ORA-00604: error at recursive SQL level 1` | Automated task failure (stats, space mgmt) | Check DBA_SCHEDULER_JOB_RUN_DETAILS |
| `ORA-30036: unable to extend segment` | ADB auto-manages DATA; if persists = bug | Contact Oracle Support |
| `ORA-01031: insufficient privileges` | ADMIN attempting restricted operation | See ADMIN restrictions above |

---

## Reference Files

**Load [`references/oci-cli-adb.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/oci-cli-adb.md) when:**
- Provisioning, scaling, or deleting ADB instances
- Creating backups or clones (full vs metadata)
- Downloading wallet files
- Changing auto-scaling, license type, or version

**Load [`references/sqlcl-workflows.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/sqlcl-workflows.md) when:**
- Executing SQL queries via Bash (SQLcl)
- Running DBMS_SQLTUNE tasks
- Data Pump export/import
- Generating DDL for schema objects

**Load [`references/oci-adb-best-practices.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/oci-adb-best-practices.md) when:**
- Designing ADB architecture from scratch
- Planning ATP vs ADW vs APEX vs JSON workload type
- Migrating from on-premises Oracle to ADB

**See [`references/adb-ha-dr.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/adb-ha-dr.md) for:** Autonomous Data Guard setup, cross-region DR, RTO/RPO targets.

**See [`references/adb-security.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/adb-security.md) for:** mTLS wallet configuration, private endpoints, VCN Service Gateway setup.

**Pricing reference:** See [`references/cost-reference.md`](https://skilld.dev/api/skills-raw/acedergren/agentic-tools/oracle-dba/references/cost-reference.md) for ECPU/storage pricing tables and auto-scaling cost calculations.

## Arguments

$ARGUMENTS: Optional user-provided target, path, environment, symptom, or constraint. When empty, infer the narrowest safe scope from the current repository context and ask only if multiple high-impact choices remain.