I have been working with Oracle databases as a DBA for many years.
I know how to investigate blocking sessions, wait events, SQL performance and database problems.
But AI is changing the way we interact with technology.
So I Decided to Try Something Very Practical:
Can I connect an AI coding agent directly to an Oracle database and make it behave like a DBA assistant?
Instead of building another chatbot that simply talks about Oracle, I wanted the AI to actually query my Oracle database, retrieve real database information and reason about what it finds.
The Lab Stack Used:
1. The Architecture
The final architecture is surprisingly simple:
The important thing to understand is that MCP is not Oracle software. MCP is a standard way for an AI application to interact with external tools and data sources. Cursor supports MCP servers and can configure local Python servers through mcp.json.
2. My Oracle Environment
I already had Oracle running inside Docker:
PS C:\Users\gokha> docker ps
CONTAINER ID: oracle26ai | PORTS: 1521->1521, 5500->5500
PS C:\Users\gokha> docker exec -it oracle26ai sqlplus system/****@FREEPDB1
The database was accessible through localhost:1521/FREEPDB1 and was fully working before starting the AI experiment.
3. Python & Drivers
I checked Python version and the Oracle Python driver (python-oracledb):
python --version
Python 3.14.3
python -c "import oracledb; print(oracledb.__version__)"
4.0.2
Before involving AI, I tested the connection directly:
python -c "import oracledb; c=oracledb.connect(user='system', password='****', dsn='localhost:1521/FREEPDB1'); print('Connected!'); print('Oracle version:', c.version); c.close()"
Connected!
Oracle version: 23.26.2.0.0
An important detail: Oracle AI Database 26ai uses a 23.26.x database version internally.
4. Installing MCP
I installed the Python MCP SDK:
python -c "import mcp; print('MCP OK')"
MCP OK
5. Creating the Server Project
I created the project structure:
├── server.py
└── .cursor/
└── mcp.json
6. First Problem: MCP Version Confusion
This was one of the first interesting failures. When I ran python server.py, I got an error referring to FastMCP and MCP version differences.
Engineering Lesson Learned:
Don't blindly upgrade or reinstall everything when an MCP example fails. First establish exactly which SDK version is installed (python -m pip show mcp showed version 1.29.1). Version pinning matters when following examples from different documentation generations.
7. The MCP Server Tool (`server.py`)
Here is the full Python code for server.py using FastMCP and python-oracledb:
import os
import oracledb
from mcp.server.fastmcp import FastMCP
mcp = FastMCP("Oracle DBA")
def get_connection():
return oracledb.connect(
user=os.environ["ORACLE_USER"],
password=os.environ["ORACLE_PASSWORD"],
dsn=os.environ["ORACLE_DSN"],
)
@mcp.tool()
def get_database_info() -> str:
"""Get basic Oracle database information."""
conn = get_connection()
try:
cursor = conn.cursor()
cursor.execute("SELECT banner FROM v$version WHERE banner LIKE 'Oracle%'")
version = cursor.fetchone()[0]
cursor.execute("SELECT sys_context('USERENV', 'DB_NAME'), sys_context('USERENV', 'SERVICE_NAME') FROM dual")
db_name, service_name = cursor.fetchone()
return f"Database: {db_name}\nService: {service_name}\nVersion: {version}"
finally:
conn.close()
@mcp.tool()
def get_blocking_sessions() -> str:
"""Find Oracle sessions that are blocking other sessions."""
conn = get_connection()
try:
cursor = conn.cursor()
cursor.execute("""
SELECT s1.sid AS blocker_sid, s1.serial# AS blocker_serial, s1.username AS blocker_user,
s2.sid AS blocked_sid, s2.serial# AS blocked_serial, s2.username AS blocked_user,
s2.event AS wait_event, s2.seconds_in_wait
FROM v$lock l1 JOIN v$session s1 ON s1.sid = l1.sid
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
JOIN v$session s2 ON s2.sid = l2.sid
WHERE l1.block = 1 AND l2.request > 0 AND l1.sid <> l2.sid
""")
rows = cursor.fetchall()
if not rows: return "No blocking sessions found."
results = [f"BLOCKER: SID={r[0]}, SERIAL={r[1]}, USER={r[2]}\nBLOCKED: SID={r[3]}, SERIAL={r[4]}, USER={r[5]}\nWAIT EVENT: {r[6]}\nSECONDS WAITING: {r[7]}" for r in rows]
return "\n\n".join(results)
finally:
conn.close()
@mcp.tool()
def get_blocking_details() -> str:
"""Show blocker, blocked session, SQL text, and wait information."""
conn = get_connection()
try:
cursor = conn.cursor()
cursor.execute("""
SELECT blocker.sid, blocker.serial#, blocker.username, blocker.status,
blocked.sid, blocked.serial#, blocked.username, blocked.event, blocked.seconds_in_wait,
blocker_sql.sql_text, blocked_sql.sql_text
FROM v$session blocker
JOIN v$session blocked ON blocked.blocking_session = blocker.sid
LEFT JOIN v$sql blocker_sql ON blocker.sql_id = blocker_sql.sql_id
LEFT JOIN v$sql blocked_sql ON blocked.sql_id = blocked_sql.sql_id
WHERE blocked.blocking_session IS NOT NULL
""")
rows = cursor.fetchall()
if not rows: return "No blocking sessions found."
results = [f"BLOCKER\n SID: {r[0]}\n SERIAL#: {r[1]}\n USER: {r[2]}\n STATUS: {r[3]}\n\nBLOCKED\n SID: {r[4]}\n SERIAL#: {r[5]}\n USER: {r[6]}\n WAIT EVENT: {r[7]}\n SECONDS WAITING: {r[8]}\n\nBLOCKER SQL:\n{r[9]}\n\nBLOCKED SQL:\n{r[10]}" for r in rows]
return "\n\n-----------------------------\n\n".join(results)
finally:
conn.close()
if __name__ == "__main__":
mcp.run()
8. Connecting MCP to Cursor
I configured Cursor's local stdio MCP configuration in .cursor/mcp.json:
"mcpServers": {
"oracle-dba": {
"command": "python",
"args": ["C:/Users/gokha/oracle-mcp/server.py"],
"env": {
"ORACLE_USER": "system",
"ORACLE_PASSWORD": "****",
"ORACLE_DSN": "localhost:1521/FREEPDB1"
}
}
}
}
9. Environment Variables Issue
The first time Cursor called the tool, it failed because PowerShell environment variables were not automatically passed to Cursor's spawned child process. Adding the variables directly to mcp.json solved it.
10. First Successful AI → Oracle Call
I asked Cursor Agent:
Question:
Use the Oracle MCP tool and tell me the database name, service name, and Oracle version. Do not modify anything.
Oracle returned:
Database name: FREEPDB1
Service name: freepdb1
Oracle version: Oracle AI Database 26ai Free Release 23.26.2.0.0
The AI wasn't guessing or reading static documentation. It called my MCP tool, the tool queried live Oracle, and the Agent interpreted the result.
11. How Does Cursor Actually Understand My Python Code?
When my server contains a tool definition like:
@mcp.tool()
def get_blocking_sessions():
"""Find Oracle sessions that are blocking other sessions."""
Cursor Agent sees the tool name and description. When a user asks "Find blocking sessions", the Agent reasons about the user intent, selects the relevant MCP tool, sends the invocation request, and receives the structured data back.
12. Creating a Real Blocking Scenario
I created a real blocking lock scenario in Oracle using two SQL*Plus sessions:
-- Session 1 (Uncommitted update holding row lock):
UPDATE blocking_test SET message = 'Locked by Session 1' WHERE id = 1;
-- Session 2 (Waiting session):
UPDATE blocking_test SET message = 'Trying to update' WHERE id = 1;
13. Teaching MCP About Blocking
I added get_blocking_details() to my MCP server to fetch blocker SID, blocker user, status, blocked SID, wait event, seconds waiting, and SQL texts.
14. The AI DBA Test
I asked Cursor Agent to investigate the blocking session:
Question:
Use get_blocking_details and explain exactly which session is blocking which session, what SQL they are running, and why the blocked session is waiting. Do not modify anything.
The Agent's detailed explanation was:
Agent Response:
Session 36 (SERIAL# 50713, SYSTEM, INACTIVE) is blocking session 59 (SERIAL# 49207, SYSTEM).
The blocked session has been waiting 475 seconds on enq: TX - row lock contention. That wait means it needs a row that another transaction already locked and has not committed or rolled back.
SQL
Blocker (SID 36): no current SQL. Status is INACTIVE, so it is not running a statement right now. It almost certainly already updated (or otherwise locked) the row and is holding the transaction lock while idle.
Blocked (SID 59): UPDATE blocking_test SET message = 'Trying to update' WHERE id = 1
Why SID 59 is waiting
SID 59 is trying to update blocking_test row id = 1. SID 36 already has a TX (transaction) lock on that row. Oracle will not let SID 59 change the same row until SID 36 commits or rolls back. Until then, SID 59 sits on enq: TX - row lock contention.
This is a classic uncommitted-row-lock: the blocker is idle with an open transaction, and the other session is stuck on the same row. Nothing was changed; this is a read-only explanation of the current lock situation.
To get more specific, I asked:
Question:
which table blocking and which sid blocker
The Agent responded:
Agent Response:
Blocker SID: 36 (SERIAL# 50713, user SYSTEM, INACTIVE)
Table: blocking_test (row id = 1)
SID 59 is waiting on that row because SID 36 still holds the transaction lock.
15. Key DBA Observation
The Important DBA Observation:
The AI correctly explained that SID 36 was INACTIVE and still blocking SID 59.
Every experienced Oracle DBA knows this, but developers often miss it: a session can be currently INACTIVE (not executing SQL) while holding an uncommitted transaction row lock. The wait event enq: TX - row lock contention perfectly confirmed this.
16. Read → Recommend → Act (Autonomy Model)
I then tried to push the Agent a step further:
Question:
how can i fix problem
As instructed by the prompt "Do not modify anything", the Agent only investigated and did not propose direct actions. In a production environment, an AI assistant should follow a strict 3-tier autonomy model:
Read-only diagnostics. Find blocking sessions, wait events, tablespace usage.
Analyzes database and suggests options (COMMIT, ROLLBACK, kill session) with risk analysis.
Executes modifications (e.g. kill_session) ONLY after explicit human approval.
Security is paramount: the first version of any AI DBA assistant should remain read-only.
17. What Can MCP Actually Do?
MCP itself doesn't magically know Oracle. MCP gives the AI a standardized mechanism for using tools. We could expose performance, wait events, tablespace usage, RAC, or Data Guard tools.
18. What I Learned
The biggest lesson was the clear separation of responsibilities between Cursor Agent (reasoning), MCP (tool interface), server.py (DBA tools), python-oracledb (driver), and Oracle (source of truth).
19. The Final Architecture
20. What Comes Next?
This is Version 0.1 of my AI DBA experiment. Future tools will cover performance (get_top_sql), space (get_tablespace_usage), and enterprise features (get_rac_status).
21. Conclusion & Next Steps
Can an old-school Oracle DBA learn to work with AI Agents? The answer so far is yes.
The interesting part isn't replacing the DBA. The interesting part is combining: DBA Knowledge + Oracle + Python + MCP + AI Agent.
I asked an AI Agent to investigate a real Oracle blocking problem, and it identified the blocker, blocked session, SQL, wait event and root cause from the live database.
Technical References
MCP configuration, local servers, mcp.json, environment variables and tool usage.
Official Python SDK and MCP server protocol concepts.
Oracle AI Database 26ai Free Release documentation & Docker resources.