OracleDB MCP Server

MCP server for Oracle SQL performance diagnostics and AWR analysis, compatible with any MCP client that supports stdio or HTTP transports.

Tools Included

This server now includes a broad DBRE-focused Oracle toolset:

• health and readonly query execution

• SQL rewrite and bind-template generation

• AWR/ASH analysis and comparisons

• long-running query detection (OEM-style)

• bind-based SQL rewrite benchmark assistant (original vs rewritten with plan/timing diff)

• SQL plan regression detection and rescue playbooks

• SPM baseline create/manage/pack/unpack

• SQL Profile script generation (coe_xfr_sql_profile style)

• locking and blocking analysis

• privilege/schema/stats/index audits

• CPU/latency/memory/session pressure analytics

• child cursor explosion and bind sensitivity analysis

• alert log analyzer

• SQL patch quarantine helper

• SQL dependency impact map

• local SQL hotlist manager

Full per-tool catalog (signature, purpose, example call):

• docs/TOOL_CATALOG.md (auto-generated)

• Current tool count: 54

Related MCP server: MCP Oracle Server

Setup

cd <repo_root>
python3 -m pip install -r requirements.txt

Compatibility

• Oracle versions: designed for 11.2.0.4 through 23ai/23c.

• RAC: tools use GV$ views where cluster-wide context matters.

• SQL syntax compatibility: server now includes automatic fallback for FETCH FIRST ... ROWS ONLY to legacy ROWNUM pattern when running against older versions.

• Feature-dependent tools (AWR/ASH/SQL Monitor/SPM/SQL Patch/SQL Profile) require corresponding Oracle options, privileges, and pack licensing.

Observability (OpenTelemetry)

MCP tool spans can be exported with OpenTelemetry.

Enable tracing:

export TRACING=enabled
export OTEL_TRACES_EXPORTER=otlp
export OTEL_EXPORTER_OTLP_ENDPOINT="http://localhost:4318/v1/traces"
# optional:
export OTEL_EXPORTER_OTLP_HEADERS="Authorization=Bearer <token>"

Console exporter (local debug):

export TRACING=enabled
export OTEL_TRACES_EXPORTER=console

MCP context propagation

The server extracts trace context from MCP request _meta (when provided), including:

• traceparent

• tracestate

• baggage

This supports parent-child trace continuity between MCP clients and this server.

Reference:

• OpenTelemetry MCP semantic conventions (context propagation): docs/gen-ai/mcp.md#context-propagation in open-telemetry/semantic-conventions

Run Server

STDIO (best for most MCP clients)

python3 oracledb_mcp.py --transport stdio

Streamable HTTP

python3 oracledb_mcp.py --transport streamable-http --port 8020

Client Configuration Examples

VS Code MCP (example)

{
  "mcpServers": {
    "oracledb-mcp": {
      "command": "python3",
      "args": ["<repo_root>/oracledb_mcp.py", "--transport", "stdio"],
      "env": {
        "ORACLE_USER": "<db_username>",
        "ORACLE_PASSWORD": "<db_password>",
        "ORACLE_DSN": "host:1521/service"
      }
    }
  }
}

Troubleshooting

• Symptom: MCP client shows Running then immediately Stopped.

• Fix checklist:

  • Ensure server entry uses stdio and points to <repo_root>/oracledb_mcp.py.

  • Ensure Python environment can import all requirements (pip install -r requirements.txt).

  • Confirm Oracle env vars are present: ORACLE_USER, ORACLE_PASSWORD, ORACLE_DSN.

  • Run directly to validate process startup:

    • python3 <repo_root>/oracledb_mcp.py --transport stdio

  • Run registration test:

    • python3 -m pytest -q tests/test_oracledb_mcp.py

Cursor MCP (example)

{
  "mcpServers": {
    "oracledb-mcp": {
      "command": "python3",
      "args": ["<repo_root>/oracledb_mcp.py", "--transport", "stdio"],
      "env": {
        "ORACLE_USER": "<db_username>",
        "ORACLE_PASSWORD": "<db_password>",
        "ORACLE_DSN": "host:1521/service"
      }
    }
  }
}

Claude Desktop MCP (example)

{
  "mcpServers": {
    "oracledb-mcp": {
      "command": "python3",
      "args": ["<repo_root>/oracledb_mcp.py", "--transport", "stdio"],
      "env": {
        "ORACLE_USER": "<db_username>",
        "ORACLE_PASSWORD": "<db_password>",
        "ORACLE_DSN": "host:1521/service"
      }
    }
  }
}

Example Calls

• Query rewrite analysis:

  • oracle_suggest_query_rewrite(sql_text="select * from orders where trunc(created_at)=:d", sql_id="8f6t6uk2y6fht")

• Bind-template generation:

  • oracle_generate_bind_query_from_vsql(sql_id="8f6t6uk2y6fht")

• AWR analysis from file:

  • oracle_analyze_awr_report(report_path="/tmp/awr_12345_12346.txt")

• AWR comparison:

  • oracle_compare_awr_reports(baseline_report_path="/tmp/awr_before.txt", target_report_path="/tmp/awr_after.txt")

• Wait hotspot analysis:

  • oracle_waits_hotspots(hours=2, top_n=20)

• Blocking tree:

  • oracle_blocking_sessions_analyzer(top_n=20)

• Privilege audit:

  • oracle_role_privilege_audit(username="TARGET_USER", include_object_privileges=true)

• OEM-style long running SQL:

  • oracle_oem_long_running_queries(min_elapsed_seconds=5, window_minutes=60, top_n=20, only_active=true)

• Bind-based A/B SQL test:

  • oracle_test_query_with_binds(original_sql="...", candidate_sql="...", bind_sets=[...], iterations=3, fetch_rows=200)

• End-to-end rewrite advisor + benchmark:

  • oracle_sql_rewrite_benchmark_assistant(sql_id="3mrzy6ugwvvz4", rewritten_sql="select ...", use_captured_binds=true, iterations=3, fetch_rows=200)

OEM-Style Tuning Workflow

1. Find active long-running SQL (for example >5s):

   • oracle_oem_long_running_queries(min_elapsed_seconds=5, window_minutes=60, top_n=20, only_active=true)

2. Deep diagnostics on selected SQL_ID:

   • oracle_planx_sql_id(sql_id="...", lookback_days=7)

3. Benchmark original vs rewritten SQL with bind sets:

   • oracle_sql_rewrite_benchmark_assistant(sql_id="...", rewritten_sql="...", use_captured_binds=true, iterations=3)

4. If rewritten SQL is consistently better and correct, roll out app change.

Examples Folder

examples/ now contains LLM-formatted prompt/response documentation (not raw JSON):

• examples/TOOL_EXAMPLES.md

Testing

Registration test:

cd <repo_root>
python3 -m pytest -q tests/test_oracledb_mcp.py

Live Oracle integration test (executes all tools):

cd <repo_root>
ORACLE_USER='<db_username>' ORACLE_PASSWORD='<db_password>' ORACLE_DSN='<host:port/service>' python3 tests/integration_oracledb_mcp.py

Open Source

• Contribution guide: CONTRIBUTING.md

• CI pipeline: .github/workflows/ci.yml

• Catalog generator: scripts/generate_tool_catalog.py

Privileges Recommended

At minimum, grant access to required views/packages for your operational model:

• V_$SQL

• V_$SQLAREA

• V_$SQL_BIND_CAPTURE

• V_$DATABASE

• V_$INSTANCE

• DBMS_WORKLOAD_REPOSITORY

Notes

• oracle_execute_readonly_query intentionally blocks non-SELECT/WITH SQL for safety.

• Bind capture visibility depends on Oracle version, cursor lifecycle, and capture settings.

• AWR tooling works best with text exports. HTML can still work but parsed detail may be reduced.