Oracle Dba

Oracle Autonomous Database - Expert Knowledge

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.

-- 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 2→4 ECPU costs $526/month extra. If root cause is bad SQL, that is wasted money.

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:
  Compute: $0 (stopped)
  Storage: $0.025/GB/month CONTINUES
  Backups: Retention charges CONTINUE

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

NEVER create manual backups without retention (kept forever)

# WRONG - charged $0.025/GB/month FOREVER
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
# 1TB × $0.025 × 12 months = $300/year if forgotten

NEVER enable auto-scaling without setting a max ECPU limit

Auto-scaling bills for PEAK usage each hour.
Base 2 ECPU → can scale to 6 ECPU (3× hard limit).
Without max cap: $526/month → $1,578/month surprise.

RIGHT: Set Max ECPU = 4 in console (2× base) to cap at $1,052/month.

NEVER use ROWNUM with ORDER BY (wrong results)

-- 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

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

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Step 3: Create and run SQL Tuning Task

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:

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, FOREVER retention until manually deleted, $0.025/GB/month
Cost trap: 10 manual backups × 1TB × $0.025 = $250/month ongoing

---

Version Feature Matrix

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

---

Common ADB Errors

---

Reference Files

Load [`references/oci-cli-adb.md`](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`](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`](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`](references/adb-ha-dr.md) for: Autonomous Data Guard setup, cross-region DR, RTO/RPO targets.

See [`references/adb-security.md`](references/adb-security.md) for: mTLS wallet configuration, private endpoints, VCN Service Gateway setup.

Pricing reference: See `references/cost-reference.md` for ECPU/storage pricing tables and auto-scaling cost calculations.

Autonomous Database HA/DR Reference

High Availability and Disaster Recovery patterns for Oracle Autonomous Database.

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                    ADB HA/DR Options                                │
├─────────────────────────────────────────────────────────────────────┤
│                                                                     │
│  ┌─────────────┐         ┌─────────────┐         ┌─────────────┐  │
│  │  Auto HA    │         │  Local DG   │         │ Cross-Region│  │
│  │  (Built-in) │         │  (Same AD)  │         │ DG/Backup   │  │
│  ├─────────────┤         ├─────────────┤         ├─────────────┤  │
│  │ RTO: mins   │         │ RTO: mins   │         │ RTO: mins   │  │
│  │ RPO: 0      │         │ RPO: 0      │         │ RPO: ~secs  │  │
│  │ Auto        │         │ Configurable│         │ Manual      │  │
│  └─────────────┘         └─────────────┘         └─────────────┘  │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

Built-in High Availability

ADB includes automatic HA features requiring no configuration:

Automatic Features

SLA Guarantees

• Availability: 99.995% (Shared), 99.995% (Dedicated)
• Data Durability: 99.999999999% (11 nines)

Autonomous Data Guard

Real-time disaster recovery with synchronous standby.

Enable Local Data Guard

# Enable via OCI CLI
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --is-local-data-guard-enabled true

# Verify status
oci db autonomous-database get --autonomous-database-id $ADB_ID \
  --query 'data.{"standby-lag-time":"standby-lag-time-in-seconds","standby-state":"standby-db.lifecycle-state"}'

Cross-Region Data Guard

# Create cross-region standby
oci db autonomous-database create-cross-region-data-guard-standby \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $REMOTE_COMPARTMENT_ID \
  --region $REMOTE_REGION \
  --display-name "ADB-Standby-Frankfurt"

# Monitor replication lag
oci db autonomous-database get --autonomous-database-id $STANDBY_ID \
  --query 'data.standby-lag-time-in-seconds'

Switchover (Planned)

# Switchover to standby (makes standby primary)
oci db autonomous-database switchover \
  --autonomous-database-id $ADB_ID

# Verify new primary
oci db autonomous-database get --autonomous-database-id $ADB_ID \
  --query 'data.role'

Failover (Unplanned)

# Failover to standby (when primary unavailable)
oci db autonomous-database failover \
  --autonomous-database-id $STANDBY_ID

# Note: After failover, old primary needs manual reinstatement

Reinstate Failed Primary

# Reinstate old primary as standby
oci db autonomous-database reinstate \
  --autonomous-database-id $OLD_PRIMARY_ID

Backup-Based Disaster Recovery

Cross-region backup replication for cost-effective DR.

Configure Cross-Region Backup

# Enable cross-region backup
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --is-remote-data-guard-enabled false \
  --remote-disaster-recovery-type BACKUP_BASED

# Set backup destination region
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --backup-destination '{"type":"REMOTE_REGION","region":"eu-frankfurt-1"}'

Cross-Region Restore

# List backups available in remote region
oci db autonomous-database-backup list \
  --autonomous-database-id $ADB_ID \
  --region $REMOTE_REGION

# Create new ADB from backup in remote region
oci db autonomous-database create-from-backup \
  --compartment-id $REMOTE_COMPARTMENT_ID \
  --autonomous-database-backup-id $BACKUP_ID \
  --db-name "RESTORED_ADB" \
  --display-name "Restored from Backup" \
  --region $REMOTE_REGION

Point-in-Time Recovery (PITR)

Restore to any point within retention period.

PITR Capabilities

Perform PITR

# Restore to specific timestamp
oci db autonomous-database restore \
  --autonomous-database-id $ADB_ID \
  --timestamp "2024-01-15T10:30:00Z"

# Wait for restore to complete
oci db autonomous-database get --autonomous-database-id $ADB_ID \
  --query 'data.lifecycle-state'

PITR via SQL

-- Check available restore range
SELECT * FROM v$restore_range;

-- Note: PITR itself is performed via OCI Console/CLI
-- But you can verify flashback capabilities
SELECT flashback_on FROM v$database;

Backup Management

Automatic Backups

• Incremental: Daily
• Full: Weekly
• Retention: 60 days (standard), up to 95 days

Manual Backups

# Create manual backup
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "Pre-Upgrade-Backup-$(date +%Y%m%d)"

# Long-term backup (1 year)
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "Annual-Backup-2024" \
  --retention-period-in-days 365

# List backups
oci db autonomous-database-backup list \
  --autonomous-database-id $ADB_ID \
  --query 'data[*].{name:"display-name",state:"lifecycle-state",type:"type",time:"time-ended"}'

Restore from Backup

# Restore from specific backup
oci db autonomous-database restore-from-backup \
  --autonomous-database-id $ADB_ID \
  --autonomous-database-backup-id $BACKUP_ID

# Monitor restore progress
oci db autonomous-database get --autonomous-database-id $ADB_ID \
  --query 'data.lifecycle-state'

Cloning for DR Testing

Refreshable Clones

Create read-only clones that auto-refresh from source.

# Create refreshable clone
oci db autonomous-database create-clone \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $C \
  --clone-type REFRESHABLE_CLONE \
  --db-name "DR_TEST" \
  --display-name "DR Test Clone"

# Manual refresh
oci db autonomous-database refresh \
  --autonomous-database-id $CLONE_ID

# Disconnect clone (for DR drill - makes it read-write)
oci db autonomous-database update \
  --autonomous-database-id $CLONE_ID \
  --is-refreshable-clone false

Point-in-Time Clone

# Clone from specific point in time
oci db autonomous-database create-clone \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $C \
  --clone-type FULL \
  --timestamp "2024-01-15T10:30:00Z" \
  --db-name "PITR_CLONE" \
  --display-name "PITR Clone"

Connection Management During Failover

Connection Strings

ADB provides multiple connection services for HA:

Application Configuration

// JDBC with Fast Connection Failover
String url = "jdbc:oracle:thin:@" +
  "(DESCRIPTION=" +
  "(CONNECT_TIMEOUT=90)(RETRY_COUNT=20)(RETRY_DELAY=3)" +
  "(ADDRESS=(PROTOCOL=tcps)(HOST=adb.region.oraclecloud.com)(PORT=1522))" +
  "(CONNECT_DATA=(SERVICE_NAME=adbname_high.adb.oraclecloud.com)))";

// Connection properties
Properties props = new Properties();
props.setProperty("oracle.net.CONNECT_TIMEOUT", "90000");
props.setProperty("oracle.jdbc.ReadTimeout", "120000");
props.setProperty("oracle.net.READ_TIMEOUT", "120000");

TAF (Transparent Application Failover)

-- Check TAF configuration (enabled by default in ADB)
SELECT username, failover_type, failover_method, failed_over
FROM v$session
WHERE username = 'APP_USER';

-- TAF is configured in the connection descriptor
-- ADB connection strings include TAF by default

Monitoring HA/DR Status

Data Guard Status

-- Check Data Guard status
SELECT name, database_role, protection_mode, protection_level
FROM v$database;

-- Check standby lag
SELECT name, value, time_computed
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');

-- Check log shipping
SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
WHERE dest_id = 2
ORDER BY sequence# DESC
FETCH FIRST 10 ROWS ONLY;

OCI Monitoring

# Data Guard lag metric
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace oci_autonomous_database \
  --query-text 'StandbyLagInSeconds[1h]{resourceId="'$ADB_ID'"}.mean()'

# Backup status
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace oci_autonomous_database \
  --query-text 'BackupStatus[24h]{resourceId="'$ADB_ID'"}.latest()'

DR Runbook

Pre-Failover Checklist

• [ ] Verify standby database status
• [ ] Check replication lag (should be minimal)
• [ ] Notify stakeholders
• [ ] Document current primary state

Failover Steps

1. Assess: Confirm primary is truly unavailable 2. Communicate: Notify stakeholders of planned failover 3. Execute: Run failover command 4. Verify: Confirm new primary is operational 5. Update: Update DNS/connection strings if needed 6. Test: Validate application connectivity

Post-Failover Tasks

• [ ] Verify all applications reconnected
• [ ] Check for data consistency
• [ ] Plan reinstatement of old primary
• [ ] Document incident and timeline

Best Practices

Architecture

• Enable Autonomous Data Guard for production workloads
• Use cross-region for regional disaster scenarios
• Implement backup-based DR for cost-sensitive workloads

Testing

• Perform quarterly DR drills
• Use refreshable clones for DR testing
• Test application failover behavior
• Document and update runbooks

Monitoring

• Alert on standby lag exceeding threshold
• Monitor backup completion status
• Track failover events
• Set up notifications for DR events

Application Design

• Use ADB connection strings (include retry logic)
• Implement proper connection pooling
• Handle transient errors gracefully
• Design for idempotency where possible

Autonomous Database Security Reference

Comprehensive security configuration for Oracle Autonomous Database.

Security Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                    Security Layers                          │
├─────────────────────────────────────────────────────────────┤
│  Network Security    │ VCN, Private Endpoint, ACL, NSG     │
├─────────────────────────────────────────────────────────────┤
│  Identity & Access   │ IAM, Database Users, Roles          │
├─────────────────────────────────────────────────────────────┤
│  Data Protection     │ TDE, Data Redaction, Data Masking   │
├─────────────────────────────────────────────────────────────┤
│  Access Control      │ Database Vault, Label Security      │
├─────────────────────────────────────────────────────────────┤
│  Monitoring          │ Unified Audit, Data Safe, SQL Firewall│
└─────────────────────────────────────────────────────────────┘

Transparent Data Encryption (TDE)

TDE is automatically enabled for all Autonomous Databases. All data at rest is encrypted using AES-256.

Key Management Options

Configure Customer-Managed Keys

# Create OCI Vault key
oci kms management key create \
  --compartment-id $C \
  --display-name "adb-master-key" \
  --key-shape '{"algorithm":"AES","length":256}' \
  --endpoint $VAULT_ENDPOINT

# Associate key with ADB
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --kms-key-id $KEY_OCID \
  --vault-id $VAULT_OCID

Rotate Encryption Keys

-- Check current key status
SELECT * FROM v$encryption_wallet;

-- Rotate TDE master key (automatic in ADB)
-- Triggered via OCI Console or API when using customer-managed keys

Database Vault

Prevents privileged user access to application data.

Enable Database Vault

-- Check if enabled
SELECT * FROM dba_dv_status;

-- Configure Database Vault owner and account manager
BEGIN
  DVSYS.DBMS_MACADM.ENABLE_DV(
    'C##DV_OWNER',
    'C##DV_ACCTMGR'
  );
END;
/

-- Restart required after enabling

Create Realm

-- Create realm to protect schema
BEGIN
  DVSYS.DBMS_MACADM.CREATE_REALM(
    realm_name        => 'FINANCE_REALM',
    description       => 'Protect finance schema',
    enabled           => DVSYS.DBMS_MACUTL.G_YES,
    audit_options     => DVSYS.DBMS_MACUTL.G_REALM_AUDIT_FAIL,
    realm_type        => 0
  );
END;
/

-- Add objects to realm
BEGIN
  DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name   => 'FINANCE_REALM',
    object_owner => 'FINANCE',
    object_name  => '%',
    object_type  => '%'
  );
END;
/

-- Authorize users
BEGIN
  DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
    realm_name  => 'FINANCE_REALM',
    grantee     => 'FINANCE_APP',
    auth_options => DVSYS.DBMS_MACUTL.G_REALM_AUTH_OWNER
  );
END;
/

Command Rules

-- Prevent DROP TABLE during business hours
BEGIN
  DVSYS.DBMS_MACADM.CREATE_COMMAND_RULE(
    command      => 'DROP TABLE',
    rule_set_name => 'BUSINESS_HOURS_RULE',
    object_owner  => 'FINANCE',
    object_name   => '%',
    enabled       => DVSYS.DBMS_MACUTL.G_YES
  );
END;
/

Data Redaction

Mask sensitive data in real-time for unauthorized users.

Redaction Policies

-- Full redaction (replace with fixed value)
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema       => 'HR',
    object_name         => 'EMPLOYEES',
    column_name         => 'SALARY',
    policy_name         => 'REDACT_SALARY',
    function_type       => DBMS_REDACT.FULL,
    expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR_ADMIN'''
  );
END;
/

-- Partial redaction (show last 4 digits)
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema       => 'HR',
    object_name         => 'EMPLOYEES',
    column_name         => 'SSN',
    policy_name         => 'REDACT_SSN',
    function_type       => DBMS_REDACT.PARTIAL,
    function_parameters => 'VVVVVVVVV,VVVVVVVVV,X,1,5',
    expression          => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR_ADMIN'''
  );
END;
/

-- Regular expression redaction (mask email domain)
BEGIN
  DBMS_REDACT.ADD_POLICY(
    object_schema          => 'HR',
    object_name            => 'EMPLOYEES',
    column_name            => 'EMAIL',
    policy_name            => 'REDACT_EMAIL',
    function_type          => DBMS_REDACT.REGEXP,
    regexp_pattern         => '@.*',
    regexp_replace_string  => '@redacted.com',
    expression             => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR_ADMIN'''
  );
END;
/

Manage Redaction Policies

-- List policies
SELECT * FROM redaction_policies;
SELECT * FROM redaction_columns;

-- Disable policy
BEGIN
  DBMS_REDACT.ALTER_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'REDACT_SALARY',
    action        => DBMS_REDACT.DISABLE
  );
END;
/

-- Drop policy
BEGIN
  DBMS_REDACT.DROP_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'REDACT_SALARY'
  );
END;
/

Label Security

Row-level security based on data classification labels.

Configure Label Security

-- Create policy
BEGIN
  SA_SYSDBA.CREATE_POLICY(
    policy_name      => 'DOC_POLICY',
    column_name      => 'DOC_LABEL',
    default_options  => 'READ_CONTROL,WRITE_CONTROL'
  );
END;
/

-- Create levels
BEGIN
  SA_COMPONENTS.CREATE_LEVEL(
    policy_name => 'DOC_POLICY',
    level_num   => 10,
    short_name  => 'PUB',
    long_name   => 'PUBLIC'
  );
  SA_COMPONENTS.CREATE_LEVEL(
    policy_name => 'DOC_POLICY',
    level_num   => 20,
    short_name  => 'CONF',
    long_name   => 'CONFIDENTIAL'
  );
  SA_COMPONENTS.CREATE_LEVEL(
    policy_name => 'DOC_POLICY',
    level_num   => 30,
    short_name  => 'SEC',
    long_name   => 'SECRET'
  );
END;
/

-- Apply policy to table
BEGIN
  SA_POLICY_ADMIN.APPLY_TABLE_POLICY(
    policy_name   => 'DOC_POLICY',
    schema_name   => 'APP',
    table_name    => 'DOCUMENTS',
    table_options => 'READ_CONTROL,WRITE_CONTROL,LABEL_DEFAULT'
  );
END;
/

-- Set user label authorization
BEGIN
  SA_USER_ADMIN.SET_USER_LABELS(
    policy_name => 'DOC_POLICY',
    user_name   => 'APP_USER',
    max_level   => 'CONF'
  );
END;
/

Unified Auditing

Centralized audit framework (enabled by default in ADB).

Audit Policies

-- Create audit policy
CREATE AUDIT POLICY sensitive_data_access
  ACTIONS SELECT ON hr.employees,
          SELECT ON hr.salaries,
          UPDATE ON hr.salaries
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') NOT IN (''HR_ADMIN'')'
  EVALUATE PER SESSION;

-- Enable policy
AUDIT POLICY sensitive_data_access;

-- Audit all DBA activities
AUDIT POLICY ORA_DBA_POLICY;

-- Audit schema changes
CREATE AUDIT POLICY schema_changes
  ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE,
          CREATE INDEX, DROP INDEX,
          CREATE VIEW, DROP VIEW;
AUDIT POLICY schema_changes;

-- Audit logon/logoff
AUDIT POLICY ORA_LOGON_FAILURES;

Query Audit Trail

-- Recent audit events
SELECT event_timestamp, dbusername, action_name, object_schema, object_name,
       return_code, client_program_name
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE - 1
ORDER BY event_timestamp DESC
FETCH FIRST 100 ROWS ONLY;

-- Failed login attempts
SELECT event_timestamp, dbusername, os_username, userhost,
       authentication_type, return_code
FROM unified_audit_trail
WHERE action_name = 'LOGON' AND return_code != 0
ORDER BY event_timestamp DESC;

-- Privileged user activity
SELECT event_timestamp, dbusername, action_name, sql_text
FROM unified_audit_trail
WHERE dbusername IN ('ADMIN', 'SYS')
ORDER BY event_timestamp DESC;

Manage Audit Data

-- Check audit trail size
SELECT COUNT(*), ROUND(SUM(LENGTH(sql_text))/1024/1024,2) AS size_mb
FROM unified_audit_trail;

-- Archive old audit data (before purging)
CREATE TABLE audit_archive AS
SELECT * FROM unified_audit_trail
WHERE event_timestamp < SYSDATE - 90;

-- Purge audit trail (requires AUDIT_ADMIN role)
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => FALSE
  );
END;
/

SQL Firewall (26ai+)

Protect against SQL injection and unauthorized SQL.

Configure SQL Firewall

-- Enable SQL Firewall
BEGIN
  DBMS_SQL_FIREWALL.ENABLE;
END;
/

-- Start capture mode for user
BEGIN
  DBMS_SQL_FIREWALL.CREATE_CAPTURE(
    username     => 'APP_USER',
    top_level_only => TRUE,
    start_capture  => TRUE
  );
END;
/

-- Stop capture and generate allow list
BEGIN
  DBMS_SQL_FIREWALL.STOP_CAPTURE(username => 'APP_USER');
  DBMS_SQL_FIREWALL.GENERATE_ALLOW_LIST(username => 'APP_USER');
END;
/

-- Enable enforcement
BEGIN
  DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST(
    username => 'APP_USER',
    enforce  => DBMS_SQL_FIREWALL.ENFORCE_ALL,
    block    => TRUE
  );
END;
/

Data Safe Integration

Oracle Data Safe provides centralized security management for ADB.

Features

• Security Assessment: Evaluate database security posture
• User Assessment: Analyze user privileges and risks
• Data Discovery: Find sensitive data automatically
• Data Masking: Mask production data for dev/test
• Activity Auditing: Centralized audit collection

Enable Data Safe

# Register ADB with Data Safe (OCI Console or CLI)
oci data-safe target-database create \
  --compartment-id $C \
  --database-details '{"autonomousDatabaseId":"'$ADB_ID'","databaseType":"AUTONOMOUS_DATABASE"}'

User Management Best Practices

Create Application User

-- Standard application user
CREATE USER app_user IDENTIFIED BY :password
  DEFAULT TABLESPACE data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON data;

-- Grant minimal privileges
GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT, UPDATE ON app_schema.orders TO app_user;
GRANT EXECUTE ON app_schema.process_order TO app_user;

-- No direct table access - use views
CREATE VIEW app_user.orders_v AS
  SELECT order_id, customer_id, order_date, status
  FROM app_schema.orders
  WHERE customer_id = SYS_CONTEXT('APP_CTX', 'CUSTOMER_ID');

GRANT SELECT ON app_user.orders_v TO app_user;

Password Policies

-- Create password profile
CREATE PROFILE app_profile LIMIT
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 12
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1/24;

-- Apply to user
ALTER USER app_user PROFILE app_profile;

Least Privilege Roles

-- Read-only role
CREATE ROLE read_only_role;
GRANT SELECT ANY TABLE TO read_only_role;
GRANT SELECT ON dba_tables TO read_only_role;

-- Developer role
CREATE ROLE developer_role;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW,
      CREATE PROCEDURE, CREATE SEQUENCE TO developer_role;

-- Assign roles
GRANT read_only_role TO report_user;
GRANT developer_role TO dev_user;

Network Security

Private Endpoint

# Configure private endpoint
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --subnet-id $PRIVATE_SUBNET_OCID \
  --nsg-ids '["'$NSG_OCID'"]'

Access Control Lists

# Restrict to specific IPs
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --whitelisted-ips '["192.168.1.0/24","10.0.0.0/8"]'

# Allow VCN only
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --is-access-control-enabled true \
  --are-primary-whitelisted-ips-used false

Security Checklist

Initial Setup

• [ ] Enable TDE with customer-managed keys (if required)
• [ ] Configure private endpoint
• [ ] Set up Access Control Lists
• [ ] Create application-specific users (not ADMIN)
• [ ] Enable unified auditing
• [ ] Register with Data Safe

Ongoing Operations

• [ ] Review audit logs weekly
• [ ] Run security assessments monthly
• [ ] Rotate credentials quarterly
• [ ] Update ACLs when IPs change
• [ ] Review user privileges semi-annually

Reference Documentation for Oracle Dba

This is a placeholder for detailed reference documentation. Replace with actual reference content or delete if not needed.

Example real reference docs from other skills:

• product-management/references/communication.md - Comprehensive guide for status updates
• product-management/references/context_building.md - Deep-dive on gathering context
• bigquery/references/ - API references and query examples

When Reference Docs Are Useful

Reference docs are ideal for:

• Comprehensive API documentation
• Detailed workflow guides
• Complex multi-step processes
• Information too lengthy for main SKILL.md
• Content that's only needed for specific use cases

Structure Suggestions

API Reference Example

• Overview
• Authentication
• Endpoints with examples
• Error codes
• Rate limits

Workflow Guide Example

• Prerequisites
• Step-by-step instructions
• Common patterns
• Troubleshooting
• Best practices

ADB Cost Reference

ECPU Pricing

Calculation: ECPU × hourly rate × 730 hours/month

• License-Included: $0.36/ECPU-hour
• BYOL: $0.18/ECPU-hour

Storage Pricing

• All tiers (Standard, Archive): $0.025/GB/month
• Charged even when ADB is stopped

Examples:

• 1 TB: $25/month
• 5 TB: $125/month

Auto-Scaling Cost Impact

Base 2 ECPU with auto-scaling (1-3×):

• Without auto-scaling: $526/month (fixed)
• With auto-scaling (spiky load example):
• 50% of time at 2 ECPU: $263
• 30% of time at 4 ECPU: $315
• 20% of time at 6 ECPU: $315
• Total: ~$893/month (70% increase over base)

Auto-scaling makes sense when:

• Load is spiky (not sustained high)
• Manual scaling lag is unacceptable
• 3× cost increase is acceptable at peak

Set Max ECPU cap in console to limit exposure (e.g., 4 ECPU cap = max $1,052/month).

Manual Backup Cost Trap

Manual backups: $0.025/GB/month with FOREVER retention (until manually deleted).

Example: 10 manual backups × 1TB × $0.025 = $250/month ongoing. Always set --retention-days when creating manual backups.

Oracle MCP Server Tools Reference

Complete catalog of Oracle MCP server tools for AI-assisted database management.

Oracle SQLcl MCP Server

The SQLcl MCP server enables AI agents to execute SQL and manage database connections.

Installation

# Install via npx (requires SQLcl 25.1+)
npx @anthropics/model-context-protocol run oracle-sqlcl

# Or configure in MCP settings
{
  "mcpServers": {
    "oracle-sqlcl": {
      "command": "npx",
      "args": ["@anthropics/model-context-protocol", "run", "oracle-sqlcl"]
    }
  }
}

Tools

Usage Pattern

1. list-connections → Get available connections
2. connect → Establish connection
3. run-sql/run-sqlcl → Execute queries
4. disconnect → Clean up

Example: Query Execution

{
  "tool": "run-sql",
  "arguments": {
    "sql": "SELECT table_name, num_rows FROM user_tables ORDER BY num_rows DESC FETCH FIRST 5 ROWS ONLY"
  }
}

Oracle Database MCP Server (100+ Tools)

Comprehensive database management capabilities.

Installation

# Clone and install
git clone https://github.com/oracle-samples/oracle-mcp-servers.git
cd oracle-mcp-servers/database-mcp-server
npm install
npm run build

Tool Categories

Schema Discovery

Query Execution

Performance Analysis

Security Management

Backup and Recovery

Space Management

Data Dictionary

Example: Performance Analysis

{
  "tool": "list_top_sql",
  "arguments": {
    "metric": "elapsed_time",
    "limit": 10,
    "since_hours": 1
  }
}

Oracle DB Documentation MCP Server

Search official Oracle documentation.

Installation

cd oracle-mcp-servers/oracle-db-doc-mcp-server
npm install
npm run build

Tools

Example: Documentation Search

{
  "tool": "search_oracle_database_documentation",
  "arguments": {
    "query": "VECTOR_DISTANCE function syntax",
    "max_results": 5
  }
}

OCI Database Tools MCP Server

Manage OCI Database Tools connections.

Tools

Best Practices

Connection Management

• Always disconnect when done to free resources
• Use connection pooling for high-frequency operations
• Store credentials securely (OCI Vault, environment variables)

Query Safety

• Use bind variables for all user input
• Validate SQL before execution in production
• Set appropriate timeouts for long-running queries

Performance Monitoring

• Use AWR/ADDM tools for baseline analysis
• Monitor wait events for bottleneck identification
• Check top SQL regularly for optimization opportunities

Security

• Use least-privilege accounts for MCP connections
• Enable unified auditing for MCP operations
• Rotate credentials periodically

Troubleshooting

Connection Failures

# Test connectivity
sql -L admin@adb_high

# Check TNS resolution
tnsping adb_high

Permission Errors

-- Grant necessary privileges
GRANT SELECT ON v_$session TO mcp_user;
GRANT SELECT ON dba_tables TO mcp_user;
GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO mcp_user;

Timeout Issues

• Increase MCP server timeout configuration
• Optimize slow queries identified via explain plan
• Consider async execution for long-running operations

OCI CLI for Autonomous Database Operations

Direct OCI CLI commands for ADB management. Use these instead of MCP server calls.

Prerequisites

# Verify OCI CLI is configured
oci --version

# Test connectivity
oci iam region list --output table

# Set default profile (optional)
export OCI_CLI_PROFILE=DEFAULT

List and Discover

List All Autonomous Databases

# In specific compartment
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --output table

# Filter by display name
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "prod-adb" \
  --output json | jq '.data[] | {id, name: .["display-name"], state: .["lifecycle-state"]}'

# All compartments (requires tenancy permissions)
oci db autonomous-database list \
  --compartment-id ocid1.tenancy.oc1..xxx \
  --all \
  --output table

Get ADB Details

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output json | jq '{
    name: .data["display-name"],
    ecpu: .data["cpu-core-count"],
    storage: .data["data-storage-size-in-tbs"],
    state: .data["lifecycle-state"],
    version: .data["db-version"],
    autoscaling: .data["is-auto-scaling-enabled"]
  }'

List Backups

# Automatic backups
oci db autonomous-database-backup list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output table

# Filter by type
oci db autonomous-database-backup list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data[] | select(.type == "FULL")'

Create and Provision

Create Autonomous Database

oci db autonomous-database create \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "prod-adb" \
  --db-name "PRODADB" \
  --cpu-core-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password 'SecurePass123!' \
  --db-version "19c" \
  --license-model LICENSE_INCLUDED \
  --is-auto-scaling-enabled false \
  --wait-for-state AVAILABLE

# With auto-scaling and specific version
oci db autonomous-database create \
  --compartment-id ocid1.compartment.oc1..xxx \
  --display-name "dev-adb-23ai" \
  --db-name "DEVADB" \
  --cpu-core-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password 'SecurePass123!' \
  --db-version "23ai" \
  --license-model LICENSE_INCLUDED \
  --is-auto-scaling-enabled true \
  --wait-for-state AVAILABLE

Create Clone

# Full clone
oci db autonomous-database create-from-clone \
  --compartment-id ocid1.compartment.oc1..xxx \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "prod-adb-clone" \
  --db-name "PRODCLONE" \
  --clone-type FULL \
  --wait-for-state AVAILABLE

# Metadata clone (70% cheaper - no data)
oci db autonomous-database create-from-clone \
  --compartment-id ocid1.compartment.oc1..xxx \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "dev-schema-only" \
  --db-name "DEVSCHEMA" \
  --clone-type METADATA \
  --wait-for-state AVAILABLE

Scale and Update

Scale ECPUs

# Scale from 2 to 4 ECPUs
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4 \
  --wait-for-state AVAILABLE

# Enable auto-scaling (1-3x base ECPU, cannot configure max via CLI)
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-auto-scaling-enabled true \
  --wait-for-state AVAILABLE

# IMPORTANT: Auto-scaling limits are FIXED (cannot change via CLI):
# - Min: 1x base ECPU
# - Max: 3x base ECPU (hard limit)
# - Scaling trigger: CPU > 80% for 5+ minutes
# - Scale-down: CPU < 60% for 10+ minutes
#
# Cost impact example (Base 2 ECPU):
# - Without auto-scaling: 2 × $0.36 × 730 = $526/month (fixed)
# - With auto-scaling peak: 6 × $0.36 × 730 = $1,578/month (if sustained)
#
# To limit costs: Start with higher base ECPU, disable auto-scaling

Scale Storage

# Scale from 1TB to 2TB
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --data-storage-size-in-tbs 2 \
  --wait-for-state AVAILABLE

Change License Type

# Switch to BYOL (50% cost reduction)
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --license-model BRING_YOUR_OWN_LICENSE \
  --wait-for-state AVAILABLE

Lifecycle Management

Stop ADB

oci db autonomous-database stop \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state STOPPED

# IMPORTANT: Storage charges continue even when stopped!
# 1TB ADB stopped = $25/month storage cost

Start ADB

oci db autonomous-database start \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

Delete ADB

# Delete immediately
oci db autonomous-database delete \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --force

# Delete with final backup
oci db autonomous-database-backup create \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "final-backup-before-delete" \
  --retention-days 30

oci db autonomous-database delete \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --force

Backup Operations

Create Manual Backup

# With retention (CRITICAL - prevents forever storage)
oci db autonomous-database-backup create \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "pre-upgrade-backup" \
  --retention-days 30 \
  --wait-for-state ACTIVE

# NEVER create without retention - costs $0.025/GB/month FOREVER
# 1TB backup × $0.025 × ∞ = $300/year perpetually

Restore from Backup

oci db autonomous-database restore \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --timestamp "2026-01-28T10:00:00Z" \
  --wait-for-state AVAILABLE

Delete Manual Backup

oci db autonomous-database-backup delete \
  --autonomous-database-backup-id ocid1.autonomousdatabasebackup.oc1..xxx \
  --force

Wallet Management

Download Wallet

# Regional wallet (one wallet for this ADB)
oci db autonomous-database generate-wallet \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --password 'WalletPass123!' \
  --file ~/wallets/adb_wallet.zip

# Extract and use
mkdir ~/wallets/adb_wallet
unzip ~/wallets/adb_wallet.zip -d ~/wallets/adb_wallet
export TNS_ADMIN=~/wallets/adb_wallet

Rotate Wallet

# Generate new wallet (invalidates old ones)
oci db autonomous-database generate-wallet \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --password 'NewWalletPass123!' \
  --file ~/wallets/adb_wallet_new.zip

Monitoring and Metrics

Get Connection Strings

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data["connection-strings"]'

# Extract specific service
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["connection-strings"]["profiles"][] | select(.["consumer-group"] == "HIGH") | .value'

Check ECPU Usage (requires monitoring API)

# Query metrics namespace
oci monitoring metric-data summarize-metrics-data \
  --namespace oci_autonomous_database \
  --compartment-id ocid1.compartment.oc1..xxx \
  --query-text 'CpuUtilization[1m].mean()' \
  --start-time "2026-01-28T00:00:00Z" \
  --end-time "2026-01-28T23:59:59Z" \
  --output table

Cost Management

Check Current Cost Configuration

oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '{
    ecpu: .data["cpu-core-count"],
    storage_tb: .data["data-storage-size-in-tbs"],
    license: .data["license-model"],
    autoscaling: .data["is-auto-scaling-enabled"],
    state: .data["lifecycle-state"]
  }'

# Calculate monthly cost
# License-Included: ECPU × $0.36/hr × 730 hrs + Storage_TB × 1000 × $0.025
# BYOL: ECPU × $0.18/hr × 730 hrs + Storage_TB × 1000 × $0.025

Switch to BYOL (50% compute savings)

oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --license-model BRING_YOUR_OWN_LICENSE \
  --wait-for-state AVAILABLE

# Savings: 2 ECPU × ($0.36 - $0.18) × 730 = $263/month

Advanced Operations

Update to Latest Version

# Update to 23ai
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# CRITICAL: Cannot downgrade! Test in clone first.

Change Workload Type

# Switch from OLTP to Data Warehouse
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-workload "DW" \
  --wait-for-state AVAILABLE

Enable/Disable Features

# Enable operations insights
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-operations-insights-enabled true \
  --wait-for-state AVAILABLE

# Enable database management
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --is-database-management-enabled true \
  --wait-for-state AVAILABLE

High Availability and Disaster Recovery

Create Autonomous Data Guard (Standby Database)

# Enable Autonomous Data Guard (creates standby in different region)
oci db autonomous-database create-autonomous-database-dataguard-association \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --protection-mode MAXIMUM_PERFORMANCE \
  --wait-for-state AVAILABLE

# Check Data Guard status
oci db autonomous-database-dataguard-association list \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --output table

Failover (Disaster Recovery)

# Failover to standby (makes standby the new primary)
# Use when primary is unavailable
oci db autonomous-database failover \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

# CRITICAL: This is a disaster recovery operation
# Primary must be unavailable or you'll get an error

Switchover (Planned Maintenance)

# Switchover to standby (makes standby the new primary)
# Use for planned maintenance
oci db autonomous-database switchover \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

# After switchover:
# - Old primary becomes new standby
# - Old standby becomes new primary
# - Zero data loss

Reinstate Failed Primary

# After failover, reinstate old primary as new standby
oci db autonomous-database reinstate-autonomous-database-dataguard-association \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --wait-for-state AVAILABLE

Version-Specific Features

Check Available Versions

# List all available DB versions
oci db autonomous-db-version list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --db-workload OLTP \
  --output table

# Check features available in specific version
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq '.data["db-version"]'

Version-Specific Feature Matrix

Version-specific features (enabled by upgrading to version):

19c:
- Standard Oracle Database features
- Basic JSON support

21c:
- Property Graphs (CREATE PROPERTY GRAPH)
- Blockchain Tables (CREATE BLOCKCHAIN TABLE)
- Enhanced JSON (JSON_VALUE, JSON_QUERY)

23ai:
- JSON Relational Duality Views
- AI Vector Search (VECTOR data type, VECTOR_DISTANCE)
- SELECT AI (natural language queries)
- SQL Domains (domain data types)
- Annotations (metadata tags)

26ai:
- JavaScript Stored Procedures
- True Cache (application-consistent read cache)
- Enhanced vector search (hybrid search)
- All 23ai features

IMPORTANT: Features are enabled by database version, not OCI CLI flags.
To use 23ai features, upgrade database to version "23ai".

Upgrade Path Example

# Check current version
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["db-version"]'

# 1. Create clone for testing
oci db autonomous-database create-from-clone \
  --source-id ocid1.autonomousdatabase.oc1..xxx \
  --display-name "test-23ai-upgrade" \
  --db-name "TEST23AI" \
  --clone-type FULL \
  --wait-for-state AVAILABLE

# 2. Upgrade clone to 23ai
oci db autonomous-database update \
  --autonomous-database-id <clone-id> \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# 3. Test application with 23ai features

# 4. If successful, upgrade production
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --db-version "23ai" \
  --wait-for-state AVAILABLE

# CRITICAL: Cannot downgrade! Always test in clone first.

Troubleshooting

Get ADB State and Issues

# Check lifecycle state
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["lifecycle-state"]'

# Possible states:
# PROVISIONING, AVAILABLE, STOPPING, STOPPED, STARTING,
# TERMINATING, TERMINATED, UNAVAILABLE, RESTORE_IN_PROGRESS,
# BACKUP_IN_PROGRESS, SCALE_IN_PROGRESS, UPGRADE_IN_PROGRESS

Common Errors

Insufficient Quota

# Error: "Service limit exceeded for resource autonomous-database"
# Check quota:
oci limits quota list --compartment-id ocid1.tenancy.oc1..xxx

# Request increase via console or support ticket

Invalid Parameter

# Error: "InvalidParameter: cpu-core-count must be between 1 and 128"
# Check valid ranges:
oci db autonomous-database create --generate-param-json-input

Concurrent Operation

# Error: "ConflictingOperationException: Another operation is in progress"
# Wait for current operation to complete:
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["lifecycle-state"]'

# Poll until state returns to AVAILABLE

Scripting Patterns

Batch Operations with JQ

# Scale all dev ADBs to 1 ECPU
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --all \
  | jq -r '.data[] | select(.["display-name"] | startswith("dev-")) | .id' \
  | while read adb_id; do
      echo "Scaling $adb_id to 1 ECPU"
      oci db autonomous-database update \
        --autonomous-database-id "$adb_id" \
        --cpu-core-count 1 \
        --wait-for-state AVAILABLE
    done

Get Cost Summary

# Calculate total monthly cost for all ADBs in compartment
oci db autonomous-database list \
  --compartment-id ocid1.compartment.oc1..xxx \
  --all \
  | jq -r '.data[] | select(.["lifecycle-state"] != "TERMINATED") | [
    .["display-name"],
    .["cpu-core-count"],
    .["data-storage-size-in-tbs"],
    .["license-model"],
    (.["cpu-core-count"] * 0.36 * 730 + .["data-storage-size-in-tbs"] * 1000 * 0.025)
  ] | @tsv' \
  | awk '{print $1, "\t", $2, "ECPU\t", $3, "TB\t$" $5 "/month"}'

Best Practices

Always Use --wait-for-state

# ✅ GOOD - waits for operation to complete
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4 \
  --wait-for-state AVAILABLE

# ❌ BAD - returns immediately, operation may fail silently
oci db autonomous-database update \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  --cpu-core-count 4

Use JQ for JSON Parsing

# ✅ GOOD - robust JSON parsing
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | jq -r '.data["display-name"]'

# ❌ BAD - fragile grep/sed parsing
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx \
  | grep display-name | sed 's/.*: "\(.*\)".*/\1/'

Store OCIDs in Variables

# ✅ GOOD - reusable, readable
COMPARTMENT_ID="ocid1.compartment.oc1..xxx"
ADB_ID="ocid1.autonomousdatabase.oc1..xxx"

oci db autonomous-database get \
  --autonomous-database-id "$ADB_ID"

# ❌ BAD - error-prone, hard to maintain
oci db autonomous-database get \
  --autonomous-database-id ocid1.autonomousdatabase.oc1..xxx

When to Use OCI CLI

Use OCI CLI when you need to:

• Provision or delete ADB instances
• Scale ECPUs or storage
• Create backups or clones
• Download wallets
• Change configuration (auto-scaling, license type)
• Batch operations across multiple ADBs

Don't use OCI CLI for:

• SQL queries (use SQLcl instead - see sqlcl-workflows.md)
• Performance troubleshooting (use SQLcl + v$sql)
• Cost calculations (exact formulas in main SKILL.md)

OCI CLI Commands for Autonomous Database

Complete reference for managing Autonomous Database via OCI CLI.

Prerequisites

# Install OCI CLI
bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

# Configure credentials
oci setup config

# Verify setup
oci iam region list

Environment Variables

# Set compartment (use in all commands)
export C="ocid1.compartment.oc1..your_compartment_id"

# Set database ID for operations
export ADB_ID="ocid1.autonomousdatabase.oc1..your_db_id"

Autonomous Database Operations

List and Query

# List all ADBs in compartment
oci db autonomous-database list --compartment-id $C

# List with specific workload type
oci db autonomous-database list --compartment-id $C \
  --db-workload DW

# Get specific ADB details
oci db autonomous-database get --autonomous-database-id $ADB_ID

# Query output with jq
oci db autonomous-database list --compartment-id $C \
  --query 'data[*].{name:"display-name",state:"lifecycle-state",ecpu:"compute-count"}'

Create Autonomous Database

# Create ATP (Transaction Processing)
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "MYATP" \
  --display-name "My ATP Database" \
  --db-workload OLTP \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "ComplexPass123#"

# Create ADW (Data Warehouse)
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "MYADW" \
  --display-name "My ADW Database" \
  --db-workload DW \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "ComplexPass123#"

# Create with auto-scaling enabled
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "AUTOSCALE" \
  --display-name "Auto-Scaling DB" \
  --db-workload OLTP \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --is-auto-scaling-enabled true \
  --admin-password "ComplexPass123#"

# Create Free Tier ADB
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "FREEADB" \
  --display-name "Free Tier ADB" \
  --db-workload OLTP \
  --is-free-tier true \
  --admin-password "ComplexPass123#"

Start/Stop/Terminate

# Stop ADB (saves costs)
oci db autonomous-database stop --autonomous-database-id $ADB_ID

# Start ADB
oci db autonomous-database start --autonomous-database-id $ADB_ID

# Terminate ADB (irreversible!)
oci db autonomous-database delete --autonomous-database-id $ADB_ID \
  --force

Scaling

# Scale ECPU (compute)
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --compute-count 4

# Scale storage
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --data-storage-size-in-tbs 2

# Enable auto-scaling
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --is-auto-scaling-enabled true

# Disable auto-scaling
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --is-auto-scaling-enabled false

# Scale compute and storage together
oci db autonomous-database update --autonomous-database-id $ADB_ID \
  --compute-count 8 \
  --data-storage-size-in-tbs 4

Wallet Management

# Download wallet (regional)
oci db autonomous-database generate-wallet \
  --autonomous-database-id $ADB_ID \
  --password "WalletPass123#" \
  --file wallet.zip

# Download instance wallet
oci db autonomous-database generate-wallet \
  --autonomous-database-id $ADB_ID \
  --password "WalletPass123#" \
  --generate-type SINGLE \
  --file wallet_instance.zip

# Rotate wallet
oci db autonomous-database rotate-wallet \
  --autonomous-database-id $ADB_ID

Backup and Recovery

Backup Operations

# List backups
oci db autonomous-database-backup list \
  --autonomous-database-id $ADB_ID

# Create manual backup
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "pre-upgrade-backup"

# Create long-term backup (retained beyond automatic policy)
oci db autonomous-database-backup create \
  --autonomous-database-id $ADB_ID \
  --display-name "quarterly-backup" \
  --retention-period-in-days 365

# Get backup details
oci db autonomous-database-backup get \
  --autonomous-database-backup-id $BACKUP_ID

# Delete backup
oci db autonomous-database-backup delete \
  --autonomous-database-backup-id $BACKUP_ID

Point-in-Time Recovery

# Restore to specific timestamp
oci db autonomous-database restore \
  --autonomous-database-id $ADB_ID \
  --timestamp "2024-01-15T10:30:00Z"

# Restore from backup
oci db autonomous-database restore-from-backup \
  --autonomous-database-id $ADB_ID \
  --autonomous-database-backup-id $BACKUP_ID

Cloning

# Create full clone
oci db autonomous-database create-clone \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $C \
  --clone-type FULL \
  --db-name "CLONE01" \
  --display-name "Dev Clone"

# Create metadata clone (schema only)
oci db autonomous-database create-clone \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $C \
  --clone-type METADATA \
  --db-name "SCHEMA01" \
  --display-name "Schema Clone"

# Create refreshable clone
oci db autonomous-database create-clone \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $C \
  --clone-type REFRESHABLE_CLONE \
  --db-name "REFRESH01" \
  --display-name "Refreshable Clone"

# Refresh a refreshable clone
oci db autonomous-database refresh \
  --autonomous-database-id $CLONE_ID

# Disconnect refreshable clone (make independent)
oci db autonomous-database update \
  --autonomous-database-id $CLONE_ID \
  --is-refreshable-clone false

Data Guard

# Enable Autonomous Data Guard
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --is-local-data-guard-enabled true

# Enable cross-region Data Guard
oci db autonomous-database create-cross-region-data-guard-standby \
  --source-autonomous-database-id $ADB_ID \
  --compartment-id $REMOTE_COMPARTMENT_ID \
  --region $REMOTE_REGION

# Switchover (planned)
oci db autonomous-database switchover \
  --autonomous-database-id $ADB_ID

# Failover (unplanned)
oci db autonomous-database failover \
  --autonomous-database-id $ADB_ID

# Disable Data Guard
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --is-local-data-guard-enabled false

Networking

# Configure private endpoint
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --subnet-id $SUBNET_OCID \
  --nsg-ids '["ocid1.networksecuritygroup..."]'

# Configure ACL (IP allowlist)
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --whitelisted-ips '["192.168.1.0/24","10.0.0.5"]'

# Remove ACL (allow all)
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --whitelisted-ips '[]'

Maintenance

# List maintenance schedules
oci db autonomous-database-maintenance-schedule list \
  --compartment-id $C

# Update maintenance window
oci db autonomous-database update \
  --autonomous-database-id $ADB_ID \
  --scheduled-operations '[{"day-of-week":"SUNDAY","scheduled-start-time":"02:00","scheduled-stop-time":"06:00"}]'

Monitoring and Metrics

# Get CPU utilization
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace oci_autonomous_database \
  --query-text 'CpuUtilization[1h]{resourceId="'$ADB_ID'"}.mean()'

# Get storage utilization
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace oci_autonomous_database \
  --query-text 'StorageUtilization[1d]{resourceId="'$ADB_ID'"}.max()'

# Get session count
oci monitoring metric-data summarize-metrics-data \
  --compartment-id $C \
  --namespace oci_autonomous_database \
  --query-text 'Sessions[1h]{resourceId="'$ADB_ID'"}.mean()'

Work Requests

# List work requests for ADB
oci work-requests work-request list \
  --compartment-id $C \
  --resource-id $ADB_ID

# Get work request status
oci work-requests work-request get \
  --work-request-id $WORK_REQUEST_ID

# Get work request logs
oci work-requests work-request-log list \
  --work-request-id $WORK_REQUEST_ID

Output Formatting

# JSON output (default)
oci db autonomous-database get --autonomous-database-id $ADB_ID

# Table output
oci db autonomous-database list --compartment-id $C --output table

# JMESPath query
oci db autonomous-database list --compartment-id $C \
  --query 'data[?lifecycle-state==`AVAILABLE`].{name:"display-name",ecpu:"compute-count"}'

# Raw output (no formatting)
oci db autonomous-database get --autonomous-database-id $ADB_ID --raw-output

Scripting Patterns

Wait for Operation

# Create and wait
oci db autonomous-database create \
  --compartment-id $C \
  --db-name "WAITDB" \
  --display-name "Wait DB" \
  --db-workload OLTP \
  --compute-count 2 \
  --data-storage-size-in-tbs 1 \
  --admin-password "ComplexPass123#" \
  --wait-for-state AVAILABLE

Error Handling

#!/bin/bash
set -e

# Capture output and check status
output=$(oci db autonomous-database start --autonomous-database-id $ADB_ID 2>&1) || {
  echo "Error starting ADB: $output"
  exit 1
}
echo "ADB started successfully"

Batch Operations

# Stop all ADBs in compartment
for adb_id in $(oci db autonomous-database list --compartment-id $C \
  --query 'data[?lifecycle-state==`AVAILABLE`].id' --raw-output | jq -r '.[]'); do
  echo "Stopping $adb_id"
  oci db autonomous-database stop --autonomous-database-id $adb_id
done

SQL & PLSQL Patterns for Autonomous Database

Optimized SQL patterns and PLSQL best practices for Oracle ADB.

SQL Best Practices

Always Use Bind Variables

-- ✅ CORRECT: Bind variables
SELECT * FROM users WHERE id = :user_id;
SELECT * FROM orders WHERE created_at > :start_date;
UPDATE inventory SET quantity = :qty WHERE product_id = :pid;

-- ❌ WRONG: String concatenation (SQL injection risk)
SELECT * FROM users WHERE id = '${userId}';
EXECUTE IMMEDIATE 'SELECT * FROM users WHERE id = ''' || v_id || '''';

Oracle-Specific Syntax

-- Use FETCH FIRST (not LIMIT)
SELECT * FROM orders ORDER BY created_at DESC FETCH FIRST 10 ROWS ONLY;

-- With offset (pagination)
SELECT * FROM orders ORDER BY created_at DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Use NVL/COALESCE for nulls
SELECT NVL(nickname, first_name) AS display_name FROM users;
SELECT COALESCE(phone_mobile, phone_home, phone_work) AS contact FROM users;

-- Use SYS_GUID() for UUIDs
INSERT INTO orders (id, customer_id) VALUES (SYS_GUID(), :customer_id);

-- String concatenation with ||
SELECT first_name || ' ' || last_name AS full_name FROM users;

Date Operations

-- Current timestamp
SELECT SYSTIMESTAMP FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;  -- Session timezone

-- Date arithmetic
SELECT order_date + 7 AS delivery_date FROM orders;  -- Add 7 days
SELECT order_date + INTERVAL '2' HOUR FROM orders;   -- Add 2 hours

-- Date formatting
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM events;
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;
SELECT TO_TIMESTAMP('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Date truncation
SELECT TRUNC(order_date) AS order_day FROM orders;           -- Day
SELECT TRUNC(order_date, 'MM') AS order_month FROM orders;   -- Month
SELECT TRUNC(order_date, 'Q') AS order_quarter FROM orders;  -- Quarter

-- Date ranges
SELECT * FROM orders
WHERE created_at BETWEEN :start_date AND :end_date;

SELECT * FROM orders
WHERE created_at >= TRUNC(SYSDATE) - 30;  -- Last 30 days

JSON Operations

-- JSON column definition
CREATE TABLE documents (
  id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
  data CLOB CHECK (data IS JSON)
);

-- Insert JSON
INSERT INTO documents (data) VALUES ('{"name": "John", "age": 30}');

-- Query JSON values
SELECT JSON_VALUE(data, '$.name') AS name FROM documents;
SELECT JSON_VALUE(data, '$.age' RETURNING NUMBER) AS age FROM documents;

-- JSON query (returns JSON)
SELECT JSON_QUERY(data, '$.address') FROM documents;

-- JSON exists
SELECT * FROM documents WHERE JSON_EXISTS(data, '$.premium');

-- JSON table (unnest arrays)
SELECT d.id, jt.item_name, jt.quantity
FROM documents d,
     JSON_TABLE(d.data, '$.items[*]'
       COLUMNS (
         item_name VARCHAR2(100) PATH '$.name',
         quantity NUMBER PATH '$.qty'
       )
     ) jt;

-- Update JSON
UPDATE documents
SET data = JSON_TRANSFORM(data, SET '$.status' = 'active')
WHERE id = :id;

-- JSON Duality Views (23ai+)
CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS
  SELECT JSON {
    '_id': o.order_id,
    'customer': c.customer_name,
    'items': [
      SELECT JSON {'product': p.name, 'qty': oi.quantity}
      FROM order_items oi, products p
      WHERE oi.order_id = o.order_id AND oi.product_id = p.product_id
    ]
  }
  FROM orders o, customers c
  WHERE o.customer_id = c.customer_id;

AI Vector Search (23ai+)

Vector Column Definition

-- Create table with vector column
CREATE TABLE documents (
  id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
  content CLOB,
  embedding VECTOR(1024, FLOAT32)
);

-- Create vector index
CREATE VECTOR INDEX doc_embedding_idx ON documents(embedding)
  ORGANIZATION NEIGHBOR PARTITIONS
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

Vector Similarity Search

-- Basic similarity search
SELECT id, content,
       VECTOR_DISTANCE(embedding, :query_embedding, COSINE) AS score
FROM documents
ORDER BY score
FETCH FIRST 10 ROWS ONLY;

-- With threshold
SELECT id, content,
       VECTOR_DISTANCE(embedding, :query_embedding, COSINE) AS score
FROM documents
WHERE VECTOR_DISTANCE(embedding, :query_embedding, COSINE) < 0.5
ORDER BY score
FETCH FIRST 10 ROWS ONLY;

-- Hybrid search (vector + keyword)
SELECT id, content,
       (0.6 * vector_score + 0.4 * text_score) AS combined_score
FROM (
  SELECT id, content,
         VECTOR_DISTANCE(embedding, :query_embedding, COSINE) AS vector_score,
         CASE WHEN CONTAINS(content, :keywords, 1) > 0 THEN SCORE(1) ELSE 0 END AS text_score
  FROM documents
  WHERE CONTAINS(content, :keywords, 1) > 0
     OR VECTOR_DISTANCE(embedding, :query_embedding, COSINE) < 0.7
)
ORDER BY combined_score
FETCH FIRST 10 ROWS ONLY;

Generate Embeddings with DBMS_VECTOR

-- Generate embedding using OCI GenAI
DECLARE
  v_embedding VECTOR;
BEGIN
  v_embedding := DBMS_VECTOR.UTL_TO_EMBEDDING(
    'This is the text to embed',
    JSON('{"provider":"oci", "model":"cohere.embed-english-v3.0"}')
  );
  -- Use v_embedding...
END;
/

-- Batch embedding generation
INSERT INTO documents (content, embedding)
SELECT content,
       DBMS_VECTOR.UTL_TO_EMBEDDING(content,
         JSON('{"provider":"oci", "model":"cohere.embed-english-v3.0"}'))
FROM staging_documents;

Select AI (23ai+)

-- Configure AI profile
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OCI_GENAI',
    attributes   => '{"provider":"oci",
                      "model":"cohere.command-r-plus",
                      "oci_compartment_id":"ocid1.compartment...",
                      "object_list":[{"owner":"SALES","name":"ORDERS"},
                                     {"owner":"SALES","name":"CUSTOMERS"}]}'
  );
END;
/

-- Set profile for session
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');

-- Natural language to SQL
SELECT AI('Show me total sales by region for last month');

-- Chat mode
SELECT AI CHAT('What were our top 5 products by revenue?');

-- Narrate data
SELECT AI NARRATE('Explain this sales data') FROM sales_summary;

Performance Optimization

Efficient Joins

-- Use explicit JOIN syntax
SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= TRUNC(SYSDATE) - 30;

-- Use EXISTS instead of IN for subqueries
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
  AND o.order_date >= TRUNC(SYSDATE) - 30
);

Indexing Strategies

-- B-tree index (default, most common)
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

-- Function-based index
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM order_date));
CREATE INDEX idx_customers_upper_name ON customers(UPPER(last_name));

-- Bitmap index (low cardinality, DW workloads)
CREATE BITMAP INDEX idx_orders_status ON orders(status);

-- Invisible index (test without affecting optimizer)
CREATE INDEX idx_test INVISIBLE ON orders(region);
ALTER INDEX idx_test VISIBLE;

-- Monitor index usage
SELECT index_name, monitoring, used
FROM v$object_usage;

Query Hints

-- Force index use
SELECT /*+ INDEX(o idx_orders_date) */ *
FROM orders o WHERE order_date > SYSDATE - 30;

-- Force full table scan
SELECT /*+ FULL(o) */ * FROM orders o WHERE status = 'PENDING';

-- Parallel execution
SELECT /*+ PARALLEL(o, 4) */ COUNT(*) FROM orders o;

-- Optimizer goal
SELECT /*+ FIRST_ROWS(10) */ * FROM orders ORDER BY created_at DESC;
SELECT /*+ ALL_ROWS */ * FROM orders WHERE region = :region;

Execution Plan Analysis

-- Explain plan
EXPLAIN PLAN FOR
SELECT * FROM orders WHERE customer_id = :cid;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- With statistics
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

-- From cursor cache
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id, NULL, 'ALL'));

-- AWR historical
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(:sql_id));

PLSQL Patterns

Result Caching

CREATE OR REPLACE FUNCTION get_customer_tier(p_customer_id IN NUMBER)
  RETURN VARCHAR2
  RESULT_CACHE RELIES_ON (customers)
IS
  v_tier VARCHAR2(20);
BEGIN
  SELECT tier INTO v_tier
  FROM customers
  WHERE customer_id = p_customer_id;
  RETURN v_tier;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'STANDARD';
END;
/

Bulk Operations

-- Bulk collect
DECLARE
  TYPE t_orders IS TABLE OF orders%ROWTYPE;
  v_orders t_orders;
BEGIN
  SELECT * BULK COLLECT INTO v_orders
  FROM orders
  WHERE status = 'PENDING'
  FETCH FIRST 1000 ROWS ONLY;

  -- Process in batches
  FORALL i IN 1..v_orders.COUNT
    UPDATE orders
    SET status = 'PROCESSING'
    WHERE order_id = v_orders(i).order_id;

  COMMIT;
END;
/

-- FORALL with SAVE EXCEPTIONS
DECLARE
  TYPE t_ids IS TABLE OF NUMBER;
  v_ids t_ids := t_ids(1, 2, 3, 4, 5);
  bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    DELETE FROM orders WHERE order_id = v_ids(i);
EXCEPTION
  WHEN bulk_errors THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Error on index ' ||
        SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ': ' ||
        SQLERRM(-SQL%BULK_EXCEPTIONS(j).ERROR_CODE));
    END LOOP;
END;
/

Error Handling

CREATE OR REPLACE PROCEDURE process_order(p_order_id IN RAW)
IS
  e_order_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_order_not_found, -20001);
  v_order orders%ROWTYPE;
BEGIN
  SELECT * INTO v_order
  FROM orders
  WHERE order_id = p_order_id
  FOR UPDATE NOWAIT;

  -- Process order...

  UPDATE orders SET status = 'PROCESSED' WHERE order_id = p_order_id;
  COMMIT;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Order not found: ' || p_order_id);
  WHEN ORA_00054 THEN  -- Resource busy
    RAISE_APPLICATION_ERROR(-20002, 'Order locked by another session');
  WHEN OTHERS THEN
    ROLLBACK;
    -- Log error
    INSERT INTO error_log (error_code, error_message, context)
    VALUES (SQLCODE, SQLERRM, 'process_order:' || p_order_id);
    COMMIT;
    RAISE;
END;
/

Autonomous Transactions

-- Logging procedure that commits independently
CREATE OR REPLACE PROCEDURE log_event(
  p_event_type IN VARCHAR2,
  p_message IN VARCHAR2
)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log (event_type, message, created_at)
  VALUES (p_event_type, p_message, SYSTIMESTAMP);
  COMMIT;
END;
/

-- Usage: Log persists even if main transaction rolls back
BEGIN
  log_event('ORDER_START', 'Processing order ' || :order_id);

  -- Process order...
  -- If this fails and rolls back, log entry is preserved

  log_event('ORDER_COMPLETE', 'Order ' || :order_id || ' processed');
EXCEPTION
  WHEN OTHERS THEN
    log_event('ORDER_ERROR', SQLERRM);
    RAISE;
END;
/

Common Queries

Top N by Group

-- Top 3 orders per customer
SELECT customer_id, order_id, total_amount, rn
FROM (
  SELECT customer_id, order_id, total_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS rn
  FROM orders
)
WHERE rn <= 3;

Running Totals

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Gap Detection

-- Find gaps in sequence
SELECT prev_id + 1 AS gap_start, id - 1 AS gap_end
FROM (
  SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id
  FROM sequence_table
)
WHERE id > prev_id + 1;

Pivot/Unpivot

-- Pivot: Rows to columns
SELECT * FROM (
  SELECT region, quarter, sales
  FROM quarterly_sales
)
PIVOT (
  SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);

-- Unpivot: Columns to rows
SELECT region, quarter, sales
FROM quarterly_wide
UNPIVOT (sales FOR quarter IN (q1, q2, q3, q4));

Hierarchical Queries

-- Employee hierarchy
SELECT LPAD(' ', 2 * LEVEL - 2) || employee_name AS tree,
       employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

-- Recursive CTE (23ai+)
WITH org_tree (emp_id, emp_name, mgr_id, lvl) AS (
  SELECT employee_id, employee_name, manager_id, 1
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.employee_name, e.manager_id, t.lvl + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.emp_id
)
SELECT * FROM org_tree;

SQLcl Workflows for ADB Operations

SQLcl is Oracle's command-line SQL tool. Use it directly via Bash for database operations.

Connection Patterns

Connect to ADB (with wallet)

# Set wallet location
export TNS_ADMIN=/path/to/wallet

# Connect
sql admin/password@adb_high

Common Connection Services

• adb_high: Low latency, high concurrency (OLTP)
• adb_medium: Balanced (reporting, batch jobs)
• adb_low: Highest parallelism (background tasks, ETL)

Performance Analysis Workflows

Find Top SQL by Elapsed Time

sql admin/password@adb_high <<EOF
SET PAGESIZE 50
SET LINESIZE 200

SELECT sql_id,
       ROUND(elapsed_time/executions/1000, 2) AS avg_ms,
       executions,
       SUBSTR(sql_text, 1, 80) AS sql_text
FROM v\$sql
WHERE executions > 0
  AND last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;
EXIT;
EOF

Get Execution Plan for SQL_ID

sql admin/password@adb_high <<EOF
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
EXIT;
EOF

Check Wait Events

sql admin/password@adb_high <<EOF
SELECT event,
       ROUND(time_waited_micro/1000000, 2) AS wait_sec,
       total_waits
FROM v\$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;
EXIT;
EOF

Schema Discovery

List Large Tables

sql admin/password@adb_high <<EOF
SELECT table_name,
       num_rows,
       ROUND(blocks * 8192 / 1024 / 1024, 2) AS size_mb
FROM user_tables
WHERE num_rows > 0
ORDER BY num_rows DESC
FETCH FIRST 20 ROWS ONLY;
EXIT;
EOF

Get Table DDL

sql admin/password@adb_high <<EOF
SET LONG 100000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL('TABLE', 'ORDERS') FROM DUAL;
EXIT;
EOF

SQL Tuning Workflow

Create SQL Tuning Task

sql admin/password@adb_high <<EOF
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);
  DBMS_OUTPUT.PUT_LINE('Tuning task created: ' || task_name);
END;
/

-- Get recommendations
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_slow_query') FROM DUAL;
EXIT;
EOF

Data Operations

Export Table (Data Pump)

# Create directory (one-time setup)
sql admin/password@adb_high <<EOF
CREATE DIRECTORY export_dir AS '/tmp/exports';
GRANT READ, WRITE ON DIRECTORY export_dir TO ADMIN;
EXIT;
EOF

# Export
expdp admin/password@adb_high \
  tables=ORDERS \
  directory=export_dir \
  dumpfile=orders.dmp \
  logfile=orders_export.log

Import Table

impdp admin/password@adb_high \
  tables=ORDERS \
  directory=export_dir \
  dumpfile=orders.dmp \
  logfile=orders_import.log \
  table_exists_action=REPLACE

Monitoring

Check Active Sessions

sql admin/password@adb_high <<EOF
SELECT sid,
       serial#,
       username,
       status,
       sql_id,
       event
FROM v\$session
WHERE status = 'ACTIVE'
  AND username IS NOT NULL
ORDER BY sid;
EXIT;
EOF

Find Blocking Sessions

sql admin/password@adb_high <<EOF
SELECT blocking_session,
       sid AS blocked_sid,
       username,
       event,
       seconds_in_wait
FROM v\$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;
EXIT;
EOF

Best Practices

Use Heredoc for Multi-Line SQL

# GOOD - heredoc preserves formatting
sql admin/password@adb_high <<EOF
SELECT *
FROM orders
WHERE status = 'PENDING';
EXIT;
EOF

Set Output Formatting

sql admin/password@adb_high <<EOF
SET PAGESIZE 100       -- Rows per page
SET LINESIZE 200       -- Characters per line
SET FEEDBACK ON        -- Show "N rows selected"
SET TIMING ON          -- Show execution time
SET SQLFORMAT ANSICONSOLE  -- Color output

-- Your query here
EXIT;
EOF

Silent Mode (Scripts)

sql -S admin/password@adb_high <<EOF
-- Suppress banner and prompts
SELECT COUNT(*) FROM orders;
EXIT;
EOF

Common Errors

Wallet Not Found

# Error: ORA-29024: Certificate validation failure
# Fix: Set TNS_ADMIN
export TNS_ADMIN=/path/to/wallet_dir

Connection Timeout

# Error: ORA-12170: TNS:Connect timeout occurred
# Check: Network connectivity, service name
tnsping adb_high

Insufficient Privileges

# Error: ORA-01031: insufficient privileges
# Fix: Grant required privileges
sql admin/password@adb_high <<EOF
GRANT SELECT ON v\$sql TO app_user;
EXIT;
EOF

When to Use SQLcl

Use SQLcl when you need to:

• Execute ad-hoc SQL queries to troubleshoot issues
• Get execution plans for slow SQL_ID
• Check current wait events or active sessions
• Export/import data for backups or migrations
• Generate DDL for schema objects
• Run SQL tuning tasks

Don't use SQLcl for:

• Bulk operations (use Data Pump instead)
• Programmatic automation (use OCI CLI for ADB management)
• Long-running queries (connection may timeout)