mcp-oracle-db
An MCP server for Oracle Database, built with Spring Boot and Spring AI.
接入你的智能体
從發布者安裝。不是 PlugMCP 端點。
遠端 URL 還是本機指令? 兩種安裝方式的差異
Let any MCP-compatible client (Claude Desktop, Cursor, Cline, Continue, VS Code, Windsurf, …) introspect an Oracle schema and run SQL — safely.
CREATE/ALTER/DROP/GRANT/… with ORA-01031.Who is it for? DBAs, backend developers and AI-assisted workflows that need a local, read-heavy Oracle companion: explore a schema, diagnose performance, preview DML, and let an LLM run ad-hoc SQL without ever risking structural changes.
Executed
V$ views.INSERT, UPDATE, DELETE, MERGE on existing tables.call_procedure (requires GRANT EXECUTE on the target).COMMENT ON ....Rejected by Oracle (the least-privilege user has no DDL/DCL privileges)
CREATE ... forms, ALTER, DROP, TRUNCATE,
RENAME.GRANT, REVOKE, PURGE, FLASHBACK, LOCK TABLE, AUDIT,
NOAUDIT, ANALYZE.mvnw wrapper is enough)Run db/setup_least_privilege_user.sql as a DBA. Edit the & placeholders first.
# Linux / macOS
sqlplus system/<dba-password>@//<host>:1521/<service> @db/setup_least_privilege_user.sql
:: Windows (cmd / PowerShell)
sqlplus system/<dba-password>@//<host>:1521/<service> @db\setup_least_privilege_user.sql
It creates a user with CREATE SESSION, SELECT_CATALOG_ROLE, and per-object
SELECT/INSERT/UPDATE/DELETE grants on the target schema's tables (plus
SELECT on views and sequences). It deliberately grants no DDL and no
tablespace quota.
Which schema does the server use? There is no
schemasetting. In Oracle the schema equals the connected user, so the default schema is theORACLE_DB_USERNAMEyou configure. Because the server introspects through theALL_*views, it can see any schema where that user has grants — pass the desired schema name as a tool parameter (e.g.list_tables(schema="HR")). Which schemas are visible is controlled entirely by the grants indb/setup_least_privilege_user.sql.
# Linux / macOS
./mvnw clean package -DskipTests
# Windows (PowerShell)
.\mvnw.cmd clean package -DskipTests
This produces target/mcp-oracle-db-0.0.1-SNAPSHOT.jar (Spring Boot executable jar).
The JSON shape below is the de-facto standard used by Claude Desktop, Cursor, Cline, Windsurf and Continue. See MCP client configuration for the exact config-file path per client.
{
"mcpServers": {
"oracle-db": {
"command": "/path/to/jdk/bin/java",
"args": ["-jar", "/absolute/path/mcp-oracle-db-0.0.1-SNAPSHOT.jar"],
"env": {
"ORACLE_DB_URL": "jdbc:oracle:thin:@//db.host:1521/ORCLPDB1",
"ORACLE_DB_USERNAME": "mcp_user",
"ORACLE_DB_PASSWORD": "s3cret"
}
}
}
}
Restart the client after saving. Verify with the test_connection tool, which
reports the database version, name, current user, current schema and instance.
Once the client shows the server connected, try these in order:
test_connection — confirm reachability and the connected user/schema.list_tables(schema="HR") — see the tables you can access.describe_table(schema="HR", table="EMPLOYEES") — columns, types, nullability.get_sample_data(schema="HR", table="EMPLOYEES") — first N rows, no risk.run_query(sql="SELECT department_id, COUNT(*) FROM \"HR\".\"EMPLOYEES\" GROUP BY department_id")
— ad-hoc SQL, capped by max-rows.All STDIO clients share the same server block shown in step 4. They differ only in where that block is read from:
Paths can change between client versions — when in doubt, check the client's own docs. What matters is that the
command/args/envblock is identical for all of them.
VS Code note — its schema uses servers instead of mcpServers, and each
entry needs "type": "stdio":
{
"servers": {
"oracle-db": {
"type": "stdio",
"command": "/path/to/jdk/bin/java",
"args": ["-jar", "/absolute/path/mcp-oracle-db-0.0.1-SNAPSHOT.jar"],
"env": {
"ORACLE_DB_URL": "jdbc:oracle:thin:@//db.host:1521/ORCLPDB1",
"ORACLE_DB_USERNAME": "mcp_user",
"ORACLE_DB_PASSWORD": "s3cret"
}
}
}
}
Optional tuning lives in application.yaml under oracle.mcp:
Logging levels via LOG_LEVEL (default WARN) and APP_LOG_LEVEL (default
INFO). Keep in mind that stdout is the JSON-RPC channel — logs go to
stderr/file, never stdout.
Opt-in per-session SQL audit. When enabled, every SQL statement that reaches
Oracle is appended to a .txt file — one file per session.
Add the two variables to the env block of your MCP server entry — this is the
only place you need to touch. Example for Claude Desktop / Cursor
(claude_desktop_config.json or .cursor/mcp.json):
{
"mcpServers": {
"oracle-db": {
"command": "/path/to/jdk/bin/java",
"args": ["-jar", "/absolute/path/mcp-oracle-db-0.0.1-SNAPSHOT.jar"],
"env": {
"ORACLE_DB_URL": "jdbc:oracle:thin:@//db.host:1521/ORCLPDB1",
"ORACLE_DB_USERNAME": "mcp_user",
"ORACLE_DB_PASSWORD": "s3cret",
"MCP_AUDIT_ENABLED": "true",
"MCP_AUDIT_DIR": "/var/log/mcp-oracle-db"
}
}
}
}
After saving, restart the client so it relaunches the MCP server with the
new environment. One .txt per session is then created in MCP_AUDIT_DIR and
appended on every SQL the server launches against Oracle.
To validate quickly without the client, run the jar directly:
# Linux / macOS
MCP_AUDIT_ENABLED=true MCP_AUDIT_DIR=./mcp-audit-logs \
ORACLE_DB_URL=jdbc:oracle:thin:@//host:1521/SVC \
ORACLE_DB_USERNAME=mcp_user ORACLE_DB_PASSWORD=... \
java -jar target/mcp-oracle-db-0.0.1-SNAPSHOT.jar
# Windows (PowerShell) — set env vars in the same shell, then launch
$env:MCP_AUDIT_ENABLED="true"; $env:MCP_AUDIT_DIR="./mcp-audit-logs"
$env:ORACLE_DB_URL="jdbc:oracle:thin:@//host:1521/SVC"
$env:ORACLE_DB_USERNAME="mcp_user"; $env:ORACLE_DB_PASSWORD="..."
java -jar target/mcp-oracle-db-0.0.1-SNAPSHOT.jar
Filename format: <serverName>-<dd-MM-yyyy>-<sessionId8>-log.txt.
Each entry records the tool name, parameters, SQL text, outcome, row count and duration:
===== 2026-06-27T14:30:52.884 =====================================
tool : get_sample_data
params : schema=HR, table=EMPLOYEES, rows=10
kind : Select
type : READ
sql : SELECT * FROM "HR"."EMPLOYEES" FETCH FIRST 10 ROWS ONLY
outcome : OK
rows : 10
duration : 23 ms
-------------------------------------------------------------------
Notes:
Strict layering. Dependencies point inward (tools → services → persistence).
An audit aspect observes every SQL reaching OracleDataAccess without altering
SQL text or results.
flowchart TD
Client["MCP client<br/>(Claude Desktop, Cursor, Cline, …)"]
subgraph Server["mcp-oracle-db (Spring Boot · STDIO)"]
Tools["@McpTool endpoints<br/><i>tools/*Tools.java</i>"]
Services["Services<br/><i>Metadata · Query · DML · Plsql ·<br/>Performance · IndexAdvisor · Graph · …</i>"]
Access["OracleDataAccess<br/><i>JdbcTemplate · named-param · callable</i>"]
Audit["Audit aspects<br/><i>SqlAuditAspect · ToolAuditAspect (MDC)</i>"]
Tools --> Services --> Access
Audit -. observes .-> Access
end
Oracle[("Oracle DB<br/>least-privilege user<br/>= the only barrier")]
Client <-->|JSON-RPC over STDIO| Tools
Access --> Oracle
Oracle -. "ORA-01031 on DDL/DCL" .-> Access
70 tools, grouped by category. Read-only never modifies data; write executes on the database.
13 completions total — schema names (6), table names (3), object names (3), and audit session IDs (1). Limited by the MCP spec to resource URI variables and prompt arguments.
Single-layer security: the least-privilege Oracle user is the only barrier.
There is no application-layer SQL gate — SqlGuard was removed deliberately.
Every statement a tool sends is executed verbatim against Oracle, and Oracle's
privilege model decides allow/deny.
The server connects as a dedicated Oracle user with CREATE SESSION +
SELECT_CATALOG_ROLE + per-object SELECT/INSERT/UPDATE/DELETE grants,
EXECUTE on specific procedures and packages (à la carte), and no DDL/DCL
privileges or tablespace quota. Any CREATE/ALTER/DROP/GRANT/REVOKE/
TRUNCATE/… returns an Oracle error (typically ORA-01031: insufficient privileges) which surfaces to the client as an explanatory message.
AI SQL ──▶ OracleDataAccess ──▶ Oracle (least-priv user)
│ │
│ Oracle enforces allow/deny
│ (ORA-01031 on DDL/DCL)
▼ ▼
result / error string returned to MCP client
All internal metadata queries use ALL_* views with named parameters
(:param). Identifiers are validated and double-quoted (see SqlIdentifiers).
One statement per tool call — batches are rejected.
The suite is layered so the bulk runs on every commit with no database required, and the live-Oracle path is opt-in.
Tier 1 — unit + smoke (every ./mvnw test, no DB). Tests across
SqlIdentifiers, IndexAdvisor, Graph, SystemService health,
AuditLog reader + writer, every tool / resource / prompt / completion, plus
McpOracleDbApplicationSmokeTest which boots the full Spring context on an
in-memory H2 (Oracle-compat) datasource and asserts all 70 @McpTool endpoints
register across the 11 tool hosts. Startup regressions (a bad logback config, a
missing bean, a broken auto-config) are caught here.
Tier 2 — end-to-end MCP STDIO harness (opt-in, needs Oracle).
McpStdioE2ETest spawns the built jar as a subprocess, performs the real MCP
initialize handshake, lists tools, and runs SELECT-only checks
(describe_table, run_query, count_rows) over each table you configure,
then asserts Oracle rejects CREATE TABLE (insufficient privileges). It is
dual-gated so it never runs by accident:
Copy src/test/resources/application-e2e.example.yaml to
application-e2e.yaml (gitignored) and fill in the jar path, Oracle
credentials, schema and a tables: [...] array.
Run with the mcp-e2e profile:
# Linux / macOS
./mvnw test -Pmcp-e2e # or run-e2e.bat on Windows (builds the jar first)
# Windows (PowerShell)
.\mvnw.cmd test -Pmcp-e2e
Env-gated integration test. The full context/integration test
(McpOracleDbApplicationTests.contextLoads) runs only when ORACLE_DB_URL is
set, so ./mvnw test is green without a database.
ORACLE_DB_URL=... ORACLE_DB_USERNAME=... ORACLE_DB_PASSWORD=... ./mvnw test
ORA-01031: insufficient privileges when running CREATE/ALTER/DROP/GRANT.
This is by design, not a bug. The least-privilege user has no DDL/DCL
privileges and no tablespace quota, so Oracle rejects the statement. If you
genuinely need structural changes, run them as a DBA — do not widen the MCP
user's grants (see Security model).
ORA-12541: TNS:no listener / ORA-12514: TNS:listener … service. The JDBC
URL in ORACLE_DB_URL is wrong or the DB is unreachable. Check the host, port
and service name. Prefer the EZ-connect form
jdbc:oracle:thin:@//host:1521/SERVICE (no tnsnames.ora needed).
The client shows the server "failed to start" / no output. stdout is the
JSON-RPC channel — any stray System.out print corrupts framing. The server
disables the Spring banner (spring.main.banner-mode= off) for this reason; if
you fork the config, keep it off. Logs go to stderr/file only.
explain_plan returns an Oracle error. It needs write access to a
PLAN_TABLE. Grant it (see the optional line in db/setup_least_privilege_user.sql,
section 5) or skip the tool.
Pool exhaustion / hangs after a DB restart. HikariCP is configured with
Oracle JDBC socket timeouts (oracle.jdbc.readTimeout) precisely to recycle dead
connections. If you override application.yaml, keep a read-timeout greater than
query-timeout-seconds but below the MCP request-timeout (150s).
Which schema does the server introspect? There is no schema setting — the
default schema is ORACLE_DB_USERNAME. Pass any accessible schema as a tool
parameter (e.g. describe_table(schema="HR", table="EMPLOYEES")). Visibility is
bounded by the grants in the setup script.
Can I run multiple SQL statements in one call? No — one statement per tool
call. Batches, PL/SQL blocks and ;-chains are rejected. This keeps the
execution model predictable and the connection pool healthy.
explain_plan needs write access to a PLAN_TABLE (see the optional grant in
the setup script). Without it, that tool returns the Oracle error.ALL_*
views.DmlService
(INSERT/UPDATE/DELETE/MERGE, else UNKNOWN). It no longer gates
anything; Oracle is authoritative.com.oracle.database.jdbc:ojdbc-bom:23.26.2.0.0.
orai18n is not on Central under that group — it is omitted; ojdbc17 alone
works.MIT © Sadot Hdz. Moreno
MCP server for Oracle Database. Introspect schemas, run SQL/DML, call PL/SQL, and diagnose performance over STDIO — with safety enforced by Oracle's least-privilege model.
工具
尚無工具清單。遠端伺服器會在首次健康檢查後取得工具清單。
常見問題
在本頁複製適用於 Cursor、Claude Desktop、Claude Code 或 VS Code 的 stdio 設定。它使用發布者的安裝方式,而不是 PlugMCP 代理伺服器。
這筆記錄未列出必要的環境變數。
MCP Oracle Database 透過 stdio 在本機執行。
上次檢查時間:2026-10-28。 MCP server for Oracle Database. Introspect schemas, run SQL/DML, call PL/SQL, and diagnose performance over STDIO — with safety enforced by Oracle's least-privilege model.