Our MCP Servers ship (SQLcl) or optionally offer (OCI) a run_sql tool. It’s great! We built it, we use it, and it works exactly as intended — give the agent a way to execute SQL (generated or otherwise) against your Oracle database.
But “works as intended” and “right tool for every job” aren’t the same thing. And if you’re thinking about putting an AI assistant in front of a junior DBA, or automating routine database maintenance tasks, reaching for run_sql as your only option is leaving a lot on the table.
I want to dig deeper on this – maybe a more bespoke MCP Tools is called for.
The tool isn’t the problem. The context is.
run_sql is great for exploration and ad-hoc analysis. A developer investigating a schema, a DBA debugging a slow query, someone doing one-off data work — that’s exactly what it’s for. The flexibility is the point.
The risk shows up when you take that same flexibility and drop it into a workflow that’s supposed to be repeatable, reliable, and bounded. Maintenance tasks. Automated pipelines. An assistant helping a less-experienced person do the right thing consistently.
In those contexts, you don’t actually want the agent improvising SQL. You want it doing the right thing the same way every time. AI Skills can help with this, so can agent memory, or various other context engineering techniques.
And beyond this, we’ve also always said: use the database to protect the database. Oracle has row-level security, VPD, SQL Firewall, Data Vault, invoker rights, DBMS_ASSERT — all of it enforces your rules at the layer that can’t be prompted around. Relying on ‘the front end’ to secure your back end, is not a good choice.
What about Deep Data Security? Read the official blog, and yes, we ARE building in support for this, stay tuned!
But defense in depth means you’re also thinking about what the agent is even able to ask for. That’s where the need for a custom MCP tool might come into play.
What a purpose-built tool gives you
I’ve been building a perf_ops wrapper over the past few days — a PL/SQL package that handles five common DBA operations:
- gathering optimizer statistics,
- rebuilding indexes,
- refreshing materialized views,
- taking AWR snapshots, and
- canceling runaway SQL.
It’s exposed as a single MCP tool, and here’s what that buys us:
Consistent, typed output
run_sql returns whatever the query returns. Column names, data types, row order — all shaped by however the NL2SQL came out that particular time. If anything downstream needs to consume that output reliably, you’re in trouble the first time the agent phrases things differently.
A custom tool returns a defined JSON envelope. Every single time. {"status":"SUCCESS","operation":"GATHER_STATS","num_rows":1908,...}. The structure is guaranteed because the PL/SQL produces it, not the agent.
The agent can’t do what isn’t in the tool
Say hello to “Jeff,’ our new-hire college fresher Jr. DBA. We trusted him enough to hire him, but not enough to give him access to the SYS account on any meaningful system. Now say you want “Jeff’ to have an AI Assistant. Maybe…maybe you don’t that combo of Jr DBA + AI Agent to have any sort of autonomy in a database. Codex perhaps will provide AI assistance for Jeff when it comes to routine maintenance — gathering stats, rebuilding bloated indexes, taking an AWR snapshot before or after a perf tuning exercise.
With run_sql, the agent figures out the SQL on the fly. Most of the time it’ll be fine. But NL2SQL isn’t perfect, and for operational tasks “most of the time” isn’t the bar you want. A slightly wrong DBMS_STATS call with bad parameters, an index rebuild that wasn’t ONLINE, a CANCEL SQL with a mangled literal — these aren’t catastrophic but they’re annoying and avoidable.
With perf_ops, the agent doesn’t generate SQL for these operations at all. It passes a JSON payload to a tool, and the PL/SQL does the work correctly — with DBMS_ASSERT validation, proper error handling, and a clean result back. The junior DBA assistant is good at exactly the operations you’ve defined. Not because you’ve restricted it with a prompt (prompts are suggestions, not enforcement), but because the other operations simply don’t exist in the interface.
One tool, multiple operations
Every tool in your MCP Server costs context. The agent has to reason about what each one does, when to reach for it, whether it’s the right fit. Proliferating thin single-operation tools multiplies that overhead.
perf_ops dispatches five operations through a single JSON input. The agent doesn’t choose between gather_table_stats, rebuild_index, take_awr_snapshot, and so on. It knows there’s a perf_ops tool and passes the right operation key. Cleaner reasoning, fewer tokens, more reliable behavior. One well-designed tool beats five thin wrappers every time.
What this looks like
The Custom Tool I’ve defined for my OCI Database Tools MCP Server is invoked with this simple SQL –
SQL
SELECT DBMS_LOB.SUBSTR(
perf_ops.perform_operation(:json_payload),
4000,
1)
FROM DUAL;One bind variable, always a VARCHAR2. The package unpacks it via JSON_VALUE, validates identifiers through DBMS_ASSERT, dispatches to the right private function, and returns typed JSON.
Calling it from the agent looks like this:
JSON
{
"operation":"GATHER_STATS",
"owner":"JEFF",
"object_name":"TRANSACTIONS"
}So once this is deployed to our MCP Server, I can simply ask my agent to refresh and summarize stats on an object in my schema, no SQL/PLSQL generation required!
And the agents still excel at what agents were built for – summarizing, putting large amounts of data into context!
💡🥃 Idea for a new drinking game, take a shot every time someone says the words ‘tokens’ or ‘context.’
The database still protects the database!
None of this replaces proper database security. The invoker rights on perf_ops mean the calling user needs the actual privileges to do the work — you don’t get GATHER_STATS for free just because you called the tool. SQL Firewall, VPD, Data Vault — all of that is still in play underneath.
What custom tools add is a layer of intentionality on top of that foundation. You’ve decided what operations make sense for this assistant, built them correctly once, and now the agent just calls them. The database enforces the rest.
That’s not a skeleton key. That’s a purpose-built set of tools for a specific job. And for a Jr. DBA AI assistant, that’s exactly what you want.
How I built it
When defining new MCP Server Toolsets, one of your options in our OCI offering is to define a ‘custom’ tool.
Here’s what mine looks like, from the console definition and how the tool looks to my Agent:
I have security on this tool locked down to my Operators/Admins group members.
The PL/SQL code
PLSQL
--------------------------------------------------------------------------------
-- perf_ops package body
--
-- Backend implementation for an MCP Server tool used by Oracle DBAs for a
-- focused set of performance operations:
--
-- * GATHER_STATS - gather schema or table optimizer statistics
-- * REBUILD_INDEX - rebuild an index online and report size/status
-- * REFRESH_MVIEW - refresh a materialized view and report row count
-- * AWR_SNAPSHOT - create an AWR snapshot
-- * CANCEL_SQL - cancel a currently running SQL statement
--
-- The public entry point is perform_operation(p_json). It accepts a JSON
-- request and returns a JSON CLOB, which makes it convenient for MCP clients
-- and other tool runners that prefer structured text responses.
--
-- Publishing/security review notes:
-- * The package spec should make an intentional AUTHID choice. Definer rights
-- are powerful for an MCP tool; invoker rights are often safer but require
-- direct privileges for the caller.
-- * Run this behind a least-privilege database account, not as SYS/SYSTEM.
-- * Every operation here can change workload behavior or interrupt work, so
-- production use should include approval, logging, and guardrails.
-- * AWR features require the appropriate Diagnostics Pack licensing.
--------------------------------------------------------------------------------
create or replace PACKAGE BODY JEFF.perf_ops
AS
--------------------------------------------------------------------------------
-- Decode DBMS_STATS RAW endpoint values.
--
-- ALL_TAB_COLUMNS.LOW_VALUE and HIGH_VALUE are stored as RAW. Returning a
-- readable string makes stats output useful to the MCP client without asking the
-- client to understand Oracle's internal stats encoding.
--
-- The function is DETERMINISTIC because the output depends only on the RAW value
-- and data type supplied. Keep that promise in mind if you add NLS-sensitive
-- formatting later.
--------------------------------------------------------------------------------
FUNCTION decode_stats_raw(
p_raw IN RAW,
p_data_type IN VARCHAR2
) RETURN VARCHAR2 DETERMINISTIC
IS
l_dt DATE;
l_hex VARCHAR2(60);
l_yyyy NUMBER;
l_ns NUMBER;
BEGIN
-- Preserve NULL instead of showing an empty hex string.
IF p_raw IS NULL THEN
RETURN NULL;
END IF;
-- Decode common scalar types explicitly; fall back to hex when the datatype
-- is unsupported or the raw value cannot be safely converted.
CASE
WHEN p_data_type IN ('NUMBER','FLOAT') THEN
RETURN TO_CHAR(UTL_RAW.CAST_TO_NUMBER(p_raw));
WHEN p_data_type = 'BINARY_FLOAT' THEN
RETURN TO_CHAR(UTL_RAW.CAST_TO_BINARY_FLOAT(p_raw));
WHEN p_data_type = 'BINARY_DOUBLE' THEN
RETURN TO_CHAR(UTL_RAW.CAST_TO_BINARY_DOUBLE(p_raw));
WHEN p_data_type IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR') THEN
RETURN UTL_RAW.CAST_TO_VARCHAR2(p_raw);
WHEN p_data_type = 'DATE' THEN
DBMS_STATS.CONVERT_RAW_VALUE(p_raw, l_dt);
RETURN TO_CHAR(l_dt, 'YYYY-MM-DD HH24:MI:SS');
WHEN p_data_type LIKE 'TIMESTAMP%' THEN
-- TIMESTAMP values in column stats use Oracle's internal byte
-- layout. This decodes the common timestamp bytes enough for
-- human-readable reporting.
l_hex := RAWTOHEX(p_raw);
l_yyyy := (TO_NUMBER(SUBSTR(l_hex, 1, 2), 'XX') - 100) * 100
+ TO_NUMBER(SUBSTR(l_hex, 3, 2), 'XX') - 100;
l_ns := CASE WHEN LENGTH(l_hex) >= 22
THEN TO_NUMBER(SUBSTR(l_hex, 15, 8), 'XXXXXXXX')
ELSE 0 END;
RETURN TO_CHAR(l_yyyy, 'FM0000')
||'-'|| LPAD(TO_NUMBER(SUBSTR(l_hex, 5, 2), 'XX'), 2, '0')
||'-'|| LPAD(TO_NUMBER(SUBSTR(l_hex, 7, 2), 'XX'), 2, '0')
||' '|| LPAD(TO_NUMBER(SUBSTR(l_hex, 9, 2), 'XX') - 1, 2, '0')
||':'|| LPAD(TO_NUMBER(SUBSTR(l_hex, 11, 2), 'XX') - 1, 2, '0')
||':'|| LPAD(TO_NUMBER(SUBSTR(l_hex, 13, 2), 'XX') - 1, 2, '0')
|| CASE WHEN l_ns > 0 THEN '.' || LPAD(TO_CHAR(l_ns), 9, '0')
ELSE '' END;
ELSE
RETURN RAWTOHEX(p_raw);
END CASE;
EXCEPTION
WHEN OTHERS THEN
-- Stats RAW values can be surprising across datatypes and database
-- versions. Returning hex keeps the JSON response useful instead of
-- failing the entire operation.
RETURN RAWTOHEX(p_raw);
END decode_stats_raw;
--------------------------------------------------------------------------------
-- Gather optimizer statistics for a schema or one table.
--
-- If p_table is NULL this gathers schema stats; otherwise it gathers stats for
-- one table. The response includes elapsed time and either the tables touched or
-- detailed column stats for the target table.
--------------------------------------------------------------------------------
FUNCTION op_gather_stats(
p_owner IN VARCHAR2,
p_table IN VARCHAR2,
p_estimate_pct IN NUMBER
) RETURN CLOB
IS
l_start TIMESTAMP := SYSTIMESTAMP;
l_interval INTERVAL DAY TO SECOND;
l_duration NUMBER;
l_owner VARCHAR2(128);
l_table VARCHAR2(128);
l_count NUMBER;
l_estimate NUMBER;
l_cols CLOB;
l_tables CLOB;
l_response CLOB;
BEGIN
IF p_owner IS NULL THEN
RAISE_APPLICATION_ERROR(-20010, 'p_owner is required for GATHER_STATS');
END IF;
-- Normalize to conventional data dictionary names and reject injected object
-- syntax. This intentionally does not support quoted mixed-case identifiers.
l_owner := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_owner));
l_estimate := NVL(p_estimate_pct, DBMS_STATS.AUTO_SAMPLE_SIZE);
-- Fail early with a friendly error before invoking DBMS_STATS.
SELECT COUNT(*) INTO l_count FROM all_users WHERE username = l_owner;
IF l_count = 0 THEN
RAISE_APPLICATION_ERROR(-20011, 'Schema not found: ' || l_owner);
END IF;
IF p_table IS NULL THEN
-- Schema-level stats can be expensive. MCP callers should treat this as
-- an operation requiring explicit DBA intent, especially in production.
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => l_owner,
estimate_percent => l_estimate,
cascade => TRUE
);
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
-- Approximate the set of tables changed during this request. LAST_ANALYZED
-- is DATE-based, so this is good operational context, not an audit log.
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'table_name' VALUE table_name,
'num_rows' VALUE num_rows,
'blocks' VALUE blocks,
'last_analyzed' VALUE TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS')
)
ORDER BY table_name
RETURNING CLOB
)
INTO l_tables
FROM all_tables
WHERE owner = l_owner AND last_analyzed >= l_start;
-- Return JSON instead of DBMS_OUTPUT so the MCP client can parse and
-- present the result deterministically.
SELECT JSON_OBJECT(
'operation' VALUE 'GATHER_STATS',
'scope' VALUE 'SCHEMA',
'status' VALUE 'SUCCESS',
'schema' VALUE l_owner,
'estimate_percent' VALUE l_estimate,
'duration_seconds' VALUE l_duration,
'tables_analyzed' VALUE TREAT(l_tables AS JSON)
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END IF;
l_table := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_table));
-- Verify the table exists and is visible to this package/caller before
-- doing the DBMS_STATS call.
SELECT COUNT(*) INTO l_count
FROM all_tables
WHERE owner = l_owner AND table_name = l_table;
IF l_count = 0 THEN
RAISE_APPLICATION_ERROR(-20012,
'Table not found: ' || l_owner || '.' || l_table);
END IF;
-- Table-level stats are usually the safer default for a tuning workflow
-- because the blast radius is limited to one object.
DBMS_STATS.GATHER_TABLE_STATS(
ownname => l_owner,
tabname => l_table,
estimate_percent => l_estimate,
cascade => TRUE
);
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
-- Include column stats so the DBA can spot stale cardinality signals,
-- suspicious histograms, and skew-sensitive columns from the MCP response.
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'column_name' VALUE c.column_name,
'data_type' VALUE c.data_type,
'low_value' VALUE perf_ops.decode_stats_raw(c.low_value, c.data_type),
'high_value' VALUE perf_ops.decode_stats_raw(c.high_value, c.data_type),
'num_distinct' VALUE c.num_distinct,
'num_nulls' VALUE c.num_nulls,
'density' VALUE c.density,
'histogram' VALUE c.histogram
)
ORDER BY c.column_id
RETURNING CLOB
)
INTO l_cols
FROM all_tab_columns c
WHERE c.owner = l_owner AND c.table_name = l_table;
SELECT JSON_OBJECT(
'operation' VALUE 'GATHER_STATS',
'scope' VALUE 'TABLE',
'status' VALUE 'SUCCESS',
'schema' VALUE l_owner,
'table_name' VALUE l_table,
'estimate_percent' VALUE l_estimate,
'duration_seconds' VALUE l_duration,
'num_rows' VALUE (SELECT num_rows FROM all_tables
WHERE owner = l_owner AND table_name = l_table),
'blocks' VALUE (SELECT blocks FROM all_tables
WHERE owner = l_owner AND table_name = l_table),
'last_analyzed' VALUE (SELECT TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS')
FROM all_tables
WHERE owner = l_owner AND table_name = l_table),
'columns' VALUE TREAT(l_cols AS JSON)
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END op_gather_stats;
--------------------------------------------------------------------------------
-- Rebuild an index online and report before/after metadata.
--
-- This is intentionally narrow: validate the owner/index names, check the index
-- exists, rebuild it, then report status and segment size deltas. ONLINE reduces
-- blocking but does not make the operation free; it can still consume space, CPU,
-- I/O, and locks.
--------------------------------------------------------------------------------
FUNCTION op_rebuild_index(
p_owner IN VARCHAR2,
p_index IN VARCHAR2
) RETURN CLOB
IS
l_start TIMESTAMP := SYSTIMESTAMP;
l_interval INTERVAL DAY TO SECOND;
l_duration NUMBER;
l_owner VARCHAR2(128);
l_index VARCHAR2(128);
l_qualified VARCHAR2(257);
l_count NUMBER;
l_size_before NUMBER;
l_size_after NUMBER;
l_status VARCHAR2(8);
l_response CLOB;
BEGIN
IF p_owner IS NULL OR p_index IS NULL THEN
RAISE_APPLICATION_ERROR(-20020,
'owner and object_name are required for REBUILD_INDEX');
END IF;
l_owner := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_owner));
l_index := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_index));
-- ALL_INDEXES keeps the existence check scoped to objects visible to the
-- package. The later DBA_SEGMENTS lookup may require catalog privileges.
SELECT COUNT(*) INTO l_count
FROM all_indexes
WHERE owner = l_owner AND index_name = l_index;
IF l_count = 0 THEN
RAISE_APPLICATION_ERROR(-20021,
'Index not found: ' || l_owner || '.' || l_index);
END IF;
BEGIN
-- Dynamic SQL avoids a compile-time dependency on DBA_SEGMENTS, but the
-- runtime execution context still needs privileges to read it.
EXECUTE IMMEDIATE
'SELECT NVL(SUM(bytes),0) FROM dba_segments
WHERE owner = :o AND segment_name = :n'
INTO l_size_before USING l_owner, l_index;
EXCEPTION
WHEN NO_DATA_FOUND THEN l_size_before := 0;
END;
-- DBMS_ASSERT protects the object name portion of this DDL. DDL cannot use
-- bind variables for object identifiers.
l_qualified := DBMS_ASSERT.QUALIFIED_SQL_NAME(l_owner || '.' || l_index);
-- Review before broad production use: bitmap/domain/partitioned indexes and
-- edition/license constraints may need specialized handling.
EXECUTE IMMEDIATE 'ALTER INDEX ' || l_qualified || ' REBUILD ONLINE';
BEGIN
-- Capture post-rebuild size using the same query as the baseline.
EXECUTE IMMEDIATE
'SELECT NVL(SUM(bytes),0) FROM dba_segments
WHERE owner = :o AND segment_name = :n'
INTO l_size_after USING l_owner, l_index;
EXCEPTION
WHEN NO_DATA_FOUND THEN l_size_after := 0;
END;
SELECT status INTO l_status
FROM all_indexes
WHERE owner = l_owner AND index_name = l_index;
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
SELECT JSON_OBJECT(
'operation' VALUE 'REBUILD_INDEX',
'status' VALUE 'SUCCESS',
'schema' VALUE l_owner,
'index_name' VALUE l_index,
'index_status' VALUE l_status,
'size_before_mb' VALUE ROUND(l_size_before / 1048576, 2),
'size_after_mb' VALUE ROUND(l_size_after / 1048576, 2),
'size_delta_mb' VALUE ROUND((l_size_after - l_size_before) / 1048576, 2),
'duration_seconds' VALUE l_duration
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END op_rebuild_index;
--------------------------------------------------------------------------------
-- Refresh a materialized view and report the resulting refresh metadata.
--
-- p_method follows DBMS_MVIEW.REFRESH method codes:
-- C = complete, F = fast, P = partition, A = always, ? = force
--------------------------------------------------------------------------------
FUNCTION op_refresh_mview(
p_owner IN VARCHAR2,
p_mview IN VARCHAR2,
p_method IN VARCHAR2
) RETURN CLOB
IS
l_start TIMESTAMP := SYSTIMESTAMP;
l_interval INTERVAL DAY TO SECOND;
l_duration NUMBER;
l_owner VARCHAR2(128);
l_mview VARCHAR2(128);
l_method VARCHAR2(1);
l_count NUMBER;
l_row_count NUMBER;
l_last_refresh_date DATE;
l_last_refresh_type VARCHAR2(8);
l_response CLOB;
BEGIN
IF p_owner IS NULL OR p_mview IS NULL THEN
RAISE_APPLICATION_ERROR(-20030,
'owner and object_name are required for REFRESH_MVIEW');
END IF;
l_owner := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_owner));
l_mview := DBMS_ASSERT.SIMPLE_SQL_NAME(UPPER(p_mview));
-- Default to FORCE refresh, letting Oracle choose fast refresh when possible
-- and complete refresh when necessary.
l_method := NVL(UPPER(p_method), '?');
IF l_method NOT IN ('C','F','P','?','A') THEN
RAISE_APPLICATION_ERROR(-20031,
'Invalid method: ' || p_method ||
'. Valid: C(complete) F(fast) P(partition) A(always) ?(force)');
END IF;
-- Validate visibility before calling DBMS_MVIEW so failures are clear to the
-- MCP client.
SELECT COUNT(*) INTO l_count
FROM all_mviews
WHERE owner = l_owner AND mview_name = l_mview;
IF l_count = 0 THEN
RAISE_APPLICATION_ERROR(-20032,
'Materialized view not found: ' || l_owner || '.' || l_mview);
END IF;
-- Materialized view refresh can be long-running and can generate significant
-- undo/redo depending on refresh method and mview definition.
DBMS_MVIEW.REFRESH(
list => l_owner || '.' || l_mview,
method => l_method
);
SELECT last_refresh_date, last_refresh_type
INTO l_last_refresh_date, l_last_refresh_type
FROM all_mviews
WHERE owner = l_owner AND mview_name = l_mview;
-- Count rows through dynamic SQL because the mview name is runtime input.
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '
|| DBMS_ASSERT.QUALIFIED_SQL_NAME(l_owner || '.' || l_mview)
INTO l_row_count;
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
SELECT JSON_OBJECT(
'operation' VALUE 'REFRESH_MVIEW',
'status' VALUE 'SUCCESS',
'schema' VALUE l_owner,
'mview_name' VALUE l_mview,
'method_requested' VALUE l_method,
'last_refresh_type' VALUE l_last_refresh_type,
'last_refresh_date' VALUE TO_CHAR(l_last_refresh_date,'YYYY-MM-DD HH24:MI:SS'),
'row_count' VALUE l_row_count,
'duration_seconds' VALUE l_duration
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END op_refresh_mview;
--------------------------------------------------------------------------------
-- Create an AWR snapshot and return its snapshot metadata.
--
-- This requires access to DBMS_WORKLOAD_REPOSITORY and DBA_HIST_SNAPSHOT, and
-- production use requires the appropriate Diagnostics Pack license.
--------------------------------------------------------------------------------
FUNCTION op_awr_snapshot(
p_flush_level IN VARCHAR2
) RETURN CLOB
IS
l_start TIMESTAMP := SYSTIMESTAMP;
l_interval INTERVAL DAY TO SECOND;
l_duration NUMBER;
l_flush_level VARCHAR2(10);
l_snap_id NUMBER;
l_begin TIMESTAMP;
l_end TIMESTAMP;
l_response CLOB;
BEGIN
l_flush_level := NVL(UPPER(p_flush_level), 'TYPICAL');
IF l_flush_level NOT IN ('TYPICAL','ALL') THEN
RAISE_APPLICATION_ERROR(-20040,
'Invalid flush_level: ' || p_flush_level || '. Valid: TYPICAL | ALL');
END IF;
-- CREATE_SNAPSHOT is invoked dynamically to keep package compilation less
-- coupled to optional management-pack privileges.
EXECUTE IMMEDIATE
'BEGIN :snap := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level => :lvl); END;'
USING OUT l_snap_id, IN l_flush_level;
BEGIN
-- Pull the interval timestamps back from AWR for a more useful tool
-- response. In RAC, review whether the current instance is the right
-- scope for your MCP operation.
EXECUTE IMMEDIATE
'SELECT begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE snap_id = :s AND instance_number = :i'
INTO l_begin, l_end
USING l_snap_id, SYS_CONTEXT('USERENV','INSTANCE');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_begin := NULL;
l_end := NULL;
END;
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
SELECT JSON_OBJECT(
'operation' VALUE 'AWR_SNAPSHOT',
'status' VALUE 'SUCCESS',
'snap_id' VALUE l_snap_id,
'flush_level' VALUE l_flush_level,
'begin_interval_time' VALUE TO_CHAR(l_begin, 'YYYY-MM-DD HH24:MI:SS'),
'end_interval_time' VALUE TO_CHAR(l_end, 'YYYY-MM-DD HH24:MI:SS'),
'instance_number' VALUE SYS_CONTEXT('USERENV','INSTANCE'),
'duration_seconds' VALUE l_duration
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END op_awr_snapshot;
--------------------------------------------------------------------------------
-- Cancel a currently running SQL statement.
--
-- This is intentionally not a session kill. ALTER SYSTEM CANCEL SQL asks Oracle
-- to cancel the current SQL call for the target session while leaving the session
-- alive. It is still disruptive and should be gated carefully in any MCP tool.
--------------------------------------------------------------------------------
FUNCTION op_cancel_sql(
p_sid IN NUMBER,
p_serial IN NUMBER,
p_sql_id IN VARCHAR2,
p_inst_id IN NUMBER
) RETURN CLOB
IS
l_start TIMESTAMP := SYSTIMESTAMP;
l_interval INTERVAL DAY TO SECOND;
l_duration NUMBER;
l_running_sql_id VARCHAR2(13);
l_running_text VARCHAR2(4000);
l_username VARCHAR2(128);
l_machine VARCHAR2(128);
l_cmd VARCHAR2(200);
l_literal VARCHAR2(200);
l_response CLOB;
BEGIN
IF p_sid IS NULL OR p_serial IS NULL THEN
RAISE_APPLICATION_ERROR(-20050,
'sid and serial are required for CANCEL_SQL');
END IF;
BEGIN
-- Review for RAC: if p_inst_id can point at another instance, switch this
-- lookup to GV$SESSION and filter by INST_ID.
EXECUTE IMMEDIATE
'SELECT sql_id, username, machine
FROM v$session
WHERE sid = :s AND serial# = :ser'
INTO l_running_sql_id, l_username, l_machine
USING p_sid, p_serial;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20051,
'No session found with SID=' || p_sid ||
', SERIAL#=' || p_serial);
END;
-- SQL_IDs are normally displayed as 13 lowercase base-32 characters. If your
-- client accepts uppercase input, normalize it before this validation.
IF p_sql_id IS NOT NULL
AND NOT REGEXP_LIKE(p_sql_id, '^[a-z0-9]{13}$')
THEN
RAISE_APPLICATION_ERROR(-20052,
'Invalid SQL_ID format: ' || p_sql_id ||
' (expected 13 lowercase alphanumeric characters)');
END IF;
BEGIN
-- Use the requested SQL_ID when supplied, otherwise describe the SQL
-- currently recorded for the target session.
EXECUTE IMMEDIATE
'SELECT sql_text FROM v$sql
WHERE sql_id = :sid AND ROWNUM = 1'
INTO l_running_text
USING NVL(p_sql_id, l_running_sql_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN l_running_text := NULL;
END;
-- Build the literal required by ALTER SYSTEM CANCEL SQL:
-- 'sid, serial#'
-- 'sid, serial#, @inst_id'
-- 'sid, serial#, sql_id'
-- 'sid, serial#, @inst_id, sql_id'
l_literal := p_sid || ', ' || p_serial;
IF p_inst_id IS NOT NULL THEN
l_literal := l_literal || ', @' || p_inst_id;
END IF;
IF p_sql_id IS NOT NULL THEN
l_literal := l_literal || ', ' || p_sql_id;
END IF;
-- ENQUOTE_LITERAL is the important safety boundary here. Object-name asserts
-- do not apply because this command expects a single string literal.
l_cmd := 'ALTER SYSTEM CANCEL SQL '
|| DBMS_ASSERT.ENQUOTE_LITERAL(l_literal);
EXECUTE IMMEDIATE l_cmd;
l_interval := SYSTIMESTAMP - l_start;
l_duration := EXTRACT(DAY FROM l_interval) * 86400
+ EXTRACT(HOUR FROM l_interval) * 3600
+ EXTRACT(MINUTE FROM l_interval) * 60
+ EXTRACT(SECOND FROM l_interval);
SELECT JSON_OBJECT(
'operation' VALUE 'CANCEL_SQL',
'status' VALUE 'SUCCESS',
'sid' VALUE p_sid,
'serial' VALUE p_serial,
'inst_id' VALUE p_inst_id,
'sql_id' VALUE NVL(p_sql_id, l_running_sql_id),
'session_user' VALUE l_username,
'session_machine' VALUE l_machine,
'canceled_sql' VALUE SUBSTR(l_running_text, 1, 2000),
'command_issued' VALUE l_cmd,
'duration_seconds' VALUE l_duration
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END op_cancel_sql;
--------------------------------------------------------------------------------
-- Public MCP-facing dispatcher.
--
-- p_json is the tool contract: the client sends operation plus operation-specific
-- parameters, and the package returns a JSON CLOB with status SUCCESS or ERROR.
--
-- The autonomous transaction keeps tool-side commits/rollbacks independent from
-- any caller transaction. That is useful for MCP command execution, but it also
-- means callers cannot roll these operations back.
--------------------------------------------------------------------------------
FUNCTION perform_operation(
p_json IN VARCHAR2
) RETURN CLOB
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_op VARCHAR2(50);
l_owner VARCHAR2(128);
l_object_name VARCHAR2(128);
l_method VARCHAR2(10);
l_estimate_pct NUMBER;
l_sid NUMBER;
l_serial NUMBER;
l_sql_id VARCHAR2(13);
l_inst_id NUMBER;
l_response CLOB;
l_err_code NUMBER;
l_err_message VARCHAR2(4000);
l_err_backtrace VARCHAR2(4000);
BEGIN
-- Extract all supported fields up front. Unused fields remain NULL for
-- operations that do not need them.
l_op := UPPER(TRIM(JSON_VALUE(p_json, '$.operation')));
l_owner := JSON_VALUE(p_json, '$.owner');
l_object_name := JSON_VALUE(p_json, '$.object_name');
l_method := JSON_VALUE(p_json, '$.method');
l_estimate_pct := JSON_VALUE(p_json, '$.estimate_pct' RETURNING NUMBER);
l_sid := JSON_VALUE(p_json, '$.sid' RETURNING NUMBER);
l_serial := JSON_VALUE(p_json, '$.serial' RETURNING NUMBER);
l_sql_id := JSON_VALUE(p_json, '$.sql_id');
l_inst_id := JSON_VALUE(p_json, '$.inst_id' RETURNING NUMBER);
IF l_op IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,
'operation is required. Valid: GATHER_STATS, REBUILD_INDEX, ' ||
'REFRESH_MVIEW, AWR_SNAPSHOT, CANCEL_SQL');
END IF;
-- Dispatch only a small allow-list of operations. This keeps the MCP surface
-- area intentionally constrained instead of accepting arbitrary SQL.
CASE l_op
WHEN 'GATHER_STATS' THEN l_response := op_gather_stats(l_owner, l_object_name, l_estimate_pct);
WHEN 'REBUILD_INDEX' THEN l_response := op_rebuild_index(l_owner, l_object_name);
WHEN 'REFRESH_MVIEW' THEN l_response := op_refresh_mview(l_owner, l_object_name, l_method);
WHEN 'AWR_SNAPSHOT' THEN l_response := op_awr_snapshot(l_method);
WHEN 'CANCEL_SQL' THEN l_response := op_cancel_sql(l_sid, l_serial, l_sql_id, l_inst_id);
ELSE
RAISE_APPLICATION_ERROR(-20002,
'Unknown operation: ' || l_op ||
'. Valid: GATHER_STATS, REBUILD_INDEX, REFRESH_MVIEW, ' ||
'AWR_SNAPSHOT, CANCEL_SQL');
END CASE;
-- Most operations here either perform DDL-like work or call packages that
-- manage their own transactional side effects. The explicit COMMIT closes
-- the autonomous transaction cleanly for the tool invocation.
COMMIT;
RETURN l_response;
EXCEPTION
WHEN OTHERS THEN
-- Convert exceptions into structured JSON so the MCP server can return a
-- clean tool result instead of an unhandled PL/SQL error stack.
l_err_code := SQLCODE;
l_err_message := SQLERRM;
l_err_backtrace := SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000);
ROLLBACK;
SELECT JSON_OBJECT(
'operation' VALUE NVL(l_op, JSON_VALUE(p_json, '$.operation')),
'status' VALUE 'ERROR',
'error_code' VALUE l_err_code,
'error_message' VALUE l_err_message,
'error_stack' VALUE l_err_backtrace
RETURNING CLOB
)
INTO l_response FROM dual;
RETURN l_response;
END perform_operation;
END perf_ops;
Expand
