Oracle Dba

Oracle DBA & DevOps Skill

Expert Oracle Database administration and DevOps engineering for Autonomous Database (ADB) on Oracle Cloud Infrastructure.

Overview

This skill provides comprehensive guidance for:

• Production DBA: Performance tuning, backup/recovery, monitoring, patching
• Security DBA: TDE, Database Vault, Data Safe, unified auditing, SQL Firewall
• Cloud DBA: OCI operations, scaling, Data Guard, cost optimization

When to Use This Skill

• Managing Oracle Autonomous Database (Shared/Dedicated/Free Tier)
• Writing optimized SQL queries and PLSQL procedures
• Configuring database security and compliance
• Implementing high availability and disaster recovery
• Using Oracle MCP servers for AI-assisted database operations
• Automating database tasks with OCI CLI
• Troubleshooting performance issues with AWR/ADDM

Quick Reference

Connect to ADB via SQLcl

# Using wallet
sql admin@charlstn_high?TNS_ADMIN=/path/to/wallet

# Using Cloud Shell (no wallet needed)
sql -cloudconfig wallet.zip admin@adb_name_high

Common OCI CLI Commands

# List Autonomous Databases
oci db autonomous-database list --compartment-id $C

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

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

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

SQL Best Practices

-- Always use bind variables
SELECT * FROM users WHERE id = :user_id;

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

-- Vector similarity search (26ai)
SELECT id, content, VECTOR_DISTANCE(embedding, :query_vec, COSINE) AS score
FROM documents
ORDER BY score
FETCH FIRST 5 ROWS ONLY;

MCP Server Integration

This skill integrates with three Oracle MCP servers for AI-assisted database operations:

1. Oracle SQLcl MCP Server

Enables AI agents to execute SQL queries and manage database connections.

Key Tools:

Usage Pattern:

1. Call list-connections to see available connections
2. Call connect with connection name
3. Call run-sql to execute queries
4. Call disconnect when done

2. Oracle Database MCP Server (100+ Tools)

Comprehensive database management through MCP tools.

Tool Categories:

• Schema Discovery: list tables, columns, constraints, indexes
• Query Execution: run SQL, explain plans, execution stats
• Performance: AWR reports, session analysis, wait events
• Security: user management, privilege grants, audit settings
• Backup/Recovery: backup status, restore points, PITR

3. Oracle DB Documentation MCP Server

Search official Oracle documentation from within AI conversations.

Tool:

Workflow Decision Tree

Database Task Required
├── Performance Issue?
│   ├── Slow Query → references/sql-patterns.md (query optimization)
│   ├── High CPU/Wait → AWR/ADDM analysis via MCP tools
│   └── Scaling Needed → OCI CLI scale commands
├── Security Task?
│   ├── Encryption → references/adb-security.md (TDE)
│   ├── Access Control → Database Vault, Label Security
│   └── Auditing → Unified Audit, Data Safe
├── HA/DR Task?
│   ├── Standby Setup → references/adb-ha-dr.md (Autonomous Data Guard)
│   ├── Backup/Restore → Automatic backups, PITR (95 days)
│   └── Failover → Switchover/Failover procedures
└── Development Task?
    ├── SQL Query → references/sql-patterns.md
    ├── Vector Search → DBMS_VECTOR, AI Vector Search
    └── JSON Processing → JSON Relational Duality

ADB Feature Summary

Automatic Features (No DBA Action Required)

• Auto Indexing: Automatic index creation based on workload
• Auto Scaling: CPU scales 1-3x based on demand (when enabled)
• Auto Backup: Daily incremental, weekly full (60 days retention)
• Auto Patching: Security and bug fixes applied automatically
• Auto Tuning: SQL Plan Baselines, Segment Advisor

DBA-Managed Features

• Manual Scaling: Adjust base ECPU/storage via console or CLI
• Autonomous Data Guard: Enable cross-region standby
• Backup-Based DR: Cross-region backup replication
• Point-in-Time Recovery: Restore to any point (up to 95 days)
• Refreshable Clones: Read-only clones with auto-refresh

Version-Specific Features

Common Operations

Performance Troubleshooting

-- Find top SQL by elapsed time (last hour)
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, executions,
       ROUND(elapsed_time/executions/1000,2) AS avg_ms
FROM v$sql
WHERE executions > 0 AND last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Check session wait events
SELECT event, total_waits, 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;

-- Generate AWR report (requires DBA privilege)
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => 1,
  l_bid => :begin_snap_id,
  l_eid => :end_snap_id
));

Security Configuration

-- Enable TDE for tablespace (auto-enabled in ADB)
ALTER TABLESPACE users ENCRYPTION USING 'AES256' ENCRYPT;

-- Create read-only user
CREATE USER report_user IDENTIFIED BY :password;
GRANT CREATE SESSION TO report_user;
GRANT SELECT ON schema.table TO report_user;

-- Enable unified auditing for schema
CREATE AUDIT POLICY audit_sales_schema
  ACTIONS ALL ON sales.orders, ALL ON sales.customers;
AUDIT POLICY audit_sales_schema;

Backup and Recovery

# Restore to point in time (OCI Console or CLI)
oci db autonomous-database restore \
  --autonomous-database-id $ADB_ID \
  --timestamp "2024-01-15T10:30:00Z"

# Create refreshable clone
oci db autonomous-database create-clone \
  --source-autonomous-database-id $SOURCE_ID \
  --compartment-id $C \
  --clone-type REFRESHABLE_CLONE \
  --db-name "dev_clone" \
  --display-name "Development Clone"

Known Issues and Workarounds

PDB Visibility Delay

• Issue: New PDBs don't appear in console for several hours
• Workaround: PDBs are operational via SQL; console sync is eventual

TDE Wallet Migration (12c R1/R2)

• Issue: File-based to customer-managed key migration fails
• Workaround: Use dbaascli --skip_patch_check true

Backup to Object Storage Failures

• Issue: SSL certificate changes cause RMAN backup failures
• Workaround: Update Oracle Database Cloud Backup Module

Resources

For detailed reference information, see:

• references/mcp-tools.md - Complete MCP server tool catalog
• references/oci-cli.md - OCI CLI commands for Autonomous Database
• references/adb-security.md - Security configuration (TDE, Vault, Data Safe)
• references/adb-ha-dr.md - High availability and disaster recovery
• references/sql-patterns.md - SQL/PLSQL patterns optimized for ADB

External Documentation

• Oracle Database 26ai Documentation
• Autonomous Database Documentation
• OCI CLI Reference
• Oracle MCP Servers
• SQLcl Documentation

⚠️ DEPRECATED — This repository has been consolidated into acedergren/agentic-tools.

>

Install the oracle-dba skill with:

```bash

npx skills add acedergren/agentic-tools@oracle-dba -g

```

This repo is kept for reference only and will no longer be updated.

Oracle DBA Skill for Claude Code

A comprehensive Oracle Database Administration skill for Claude Code that provides expert DBA and DevOps guidance for Oracle Autonomous Database on OCI.

Overview

This skill transforms Claude into an expert Oracle DBA with deep knowledge of:

• Oracle Autonomous Database (Shared, Dedicated, Free Tier)
• Database Versions: 19c, 21c, 23ai, 26ai
• Security: TDE, Database Vault, Data Safe, SQL Firewall
• High Availability: Autonomous Data Guard, PITR, Backup-Based DR
• Performance: AWR, ADDM, SQL Tuning, Auto Indexing
• OCI CLI: Complete automation commands for ADB
• Oracle MCP Servers: AI-assisted database operations

Installation

For Claude Code Users

1. Download or clone this repository 2. Add the skill to your Claude Code configuration:

# Copy to your Claude Code skills directory
cp -r oracle-dba-skill ~/.claude/skills/

Or add to your project's .claude/skills/ directory.

Skill Structure

oracle-dba-skill/
├── SKILL.md                    # Main skill file
├── README.md                   # This file
├── LICENSE                     # MIT License
└── references/
    ├── mcp-tools.md           # Oracle MCP server tool catalog
    ├── oci-cli.md             # OCI CLI commands for ADB
    ├── adb-security.md        # Security configuration guide
    ├── adb-ha-dr.md           # HA/DR patterns and procedures
    └── sql-patterns.md        # SQL/PLSQL best practices

Features

MCP Server Integration

Works with Oracle's official MCP servers:

• Oracle SQLcl MCP Server - Execute SQL and manage connections
• Oracle Database MCP Server - 100+ database management tools
• Oracle DB Documentation MCP Server - Search Oracle docs

Comprehensive Reference Guides

Key Capabilities

• Performance Troubleshooting: AWR/ADDM analysis, execution plans, wait events
• Security Configuration: TDE encryption, Database Vault realms, audit policies
• HA/DR Management: Data Guard setup, failover procedures, PITR recovery
• SQL Optimization: Query tuning, indexing strategies, bulk operations
• AI Vector Search: Vector similarity queries, hybrid search (23ai/26ai)
• Select AI: Natural language to SQL (23ai/26ai)

Usage Examples

Once installed, Claude will automatically use this skill when you ask about Oracle databases:

> How do I enable Autonomous Data Guard for my ADB?

> Write a query to find top 10 slow SQL statements

> Set up TDE with customer-managed keys in OCI Vault

> Create a refreshable clone for DR testing

> Implement vector similarity search for RAG

Requirements

• Claude Code CLI (any version supporting skills)
• For MCP integration: Oracle SQLcl 25.1+ (optional)
• For OCI CLI commands: OCI CLI installed and configured (optional)

Related Resources

• Oracle Database 26ai Documentation
• Autonomous Database Documentation
• Oracle MCP Servers
• OCI CLI Reference
• SQLcl Documentation

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License - see LICENSE for details.

Author

Created by acedergren

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

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