In Part 3, we collaborated with MCP through some of the tasks that data architects will perform such as extending the schema, documenting it, auditing the design, indexing the foreign keys it flagged, and pulling back a schema ER diagram. In this Part 4 of the series, we will work with MCP to perform some typical database administrator's tasks.
Asking for Database Information
A DBA's first question on any new database is "What am I looking at?" That covers the database name, version, mode, redo log configuration, tablespaces, datafiles, and total space allocated on the file system or ASM. Historically, I would run half a dozen queries against V$DATABASE, V$LOG, V$LOGFILE, DBA_TABLESPACES, DBA_DATA_FILES, and DBA_TEMP_FILES to get this kind of information. Through MCP, one prompt gets us the information that we need.
❯ show me the database information including redo logs,
tablespaces and datafiles. show me the total size of
the database.Claude Code (Claude) ran the queries, gathered the results, and returned a structured report. Here's a snippet of that report that it produced:
ITEM VALUE
----------------- ---------------------------
Name FREE
DBID 1497779610
Container in use FREEPDB1 (PDB, CON_ID 3)
CDB YES
Database role PRIMARY
Open mode READ WRITE
Log mode ARCHIVELOG
Platform Linux OS (AARCH64)
Version 23.26.1.0.0
Created 20-FEB-2026
Current SCN 11,583,555The full report revealed three online redo log groups (200 MB each, 600 MB in total), every permanent and temporary tablespace, every datafile path and size, and a database footprint of about 2.26 GB across the PDB (since this environment is a FREE Oracle AI Database 26ai on Docker). About a quarter of the output is shown above to keep the post focused on the power of MCP rather than the row count.
Schema Owners and Space Consumption
After the database-level overview, the next question was "Who is using the database and how much space is being consumed?" One prompt covered objects, object types, and segment sizes across every application schema.
❯ show me all the schema owners and number of objects that they
have broken down by object type and show me space consumptionClaude filtered out the Oracle internal accounts, queried DBA_OBJECTS for the object inventory, queried DBA_SEGMENTS for space consumption, and returned three tables. A snippet of each is below.
Schema summary (top four by space)
OWNER TOTAL_OBJECTS SPACE_MB
----------------- ------------- --------
CO 41 4.63
SELECTAI_USER 36 3.13
C##CLOUD$SERVICE 327 2.75
VENDOME 20 2.00Object breakdown (top four by object count)
OWNER TABLES INDEXES LOBS VIEWS OTHER
----------------- ------ ------- ---- ----- -----
C##CLOUD$SERVICE 32 102 91 65 37
VECSYS 13 24 8 - 1
CO 7 21 3 4 6
SELECTAI_USER 11 13 1 - 11Space by segment type (top four by total)
OWNER TABLES INDEXES LOBS TOTAL_MB
----------------- ------ ------- ---- --------
CO 0.75 1.94 1.94 4.63
SELECTAI_USER 0.94 0.88 1.31 3.13
C##CLOUD$SERVICE 0.19 0.06 2.50 2.75
VENDOME 0.38 0.31 1.31 2.00↳Claude's Assessment
Total user-schema footprint across the PDB is about 15.4 MB across seven schemas. CO is the largest at 4.63 MB, driven by indexes and LOB segments. C##CLOUD$SERVICE has the highest object count at 327 because of the cloud management infrastructure that ships with Oracle AI Database 26ai Free, but its actual space footprint is modest. SCRUM has a single table consuming 0.75 MB with no indexes, which is worth investigating.
From here, we can drill down even further. Each of the following is one prompt away:
- Show me the top 5 largest objects in the database.
- Show me the top 10 tables that have the largest row counts.
- Show me all objects with zero statistics and give me intel about their statistics.
- Show me all the scheduled jobs and their details.
- Show me invalid objects across every schema and tell me what is broken.
The list goes on and on, but this is just to show you the kind of information that we can collect. Each prompt produces a structured report with the same level of detail you just saw, and any one of those would have taken a DBA several minutes of hand-written SQL against the data dictionary. Any individual query is straightforward to compose, but the value of MCP is that you can chain a dozen of them in a single conversation, and the agent carries forward what it has already found so that each follow-up prompt builds on the last.
Adding a Tablespace
The next request was for a new tablespace, and the pattern was the same as the queries above. A single English sentence was enough to drive the work, and no DDL was written by hand.
❯ let's add a tablespace called data size 64mbClaude generated the DDL, ran it, and verified the result against DBA_TABLESPACES and DBA_DATA_FILES.
CREATE BIGFILE TABLESPACE data
DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/data01.dbf' SIZE 64M
AUTOEXTEND ON NEXT 64M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;PROPERTY VALUE
-------------------- ------------------------------------------------
Name DATA
Contents PERMANENT
Status ONLINE
Type BIGFILE
Segment management AUTO
Datafile /opt/oracle/oradata/FREE/FREEPDB1/data01.dbf
Initial size 64 MB
Autoextend ON, NEXT 64 MBTwo details about the DDL Claude chose are worth pointing out. It picked BIGFILE to match the convention already in use for USERS, SYSTEM, SYSAUX, and UNDOTBS1 tablespaces in this database. It also set SEGMENT SPACE MANAGEMENT AUTO to match the USERS tablespace. Both choices were inferred from the existing tablespace inventory rather than from a hardcoded template.
Moving the Schema to the New Tablespace
With the DATA tablespace in place, the next step was to relocate the working schema into it. Two simple prompts covered the tables and the indexes, and I asked for both moves to be performed online.
❯ lets move all the tables for the selectai schema to the data
tablespace. can you move the tables online.Claude Code inventoried the schema, hit ORA-01950 on the first move because SELECTAI_USER had no quota on DATA, granted the quota itself with ALTER USER selectai_user QUOTA UNLIMITED ON data, then ran ALTER TABLE ... MOVE ONLINE TABLESPACE data against each table. A snippet of the verification output is shown below.
TABLE_NAME OLD_TS NEW_TS
---------------- ------- ------
CONSULTANT_TIME USERS DATA
COUNTRIES USERS DATA
DEPARTMENTS USERS DATA
EMPLOYEES USERS DATATen of eleven tables moved cleanly. The eleventh, DBTOOLS$MCP_LOG, is the SQLcl MCP audit log and is protected by an MCP-layer policy that rejects any non-SELECT statement against it, so the move is rejected before it ever reaches the database. To relocate that table, you would have to run the move outside the MCP connection.
The same pattern applied to indexes.
❯ let's also move the indexes to the data tablespace. let's do
it online alsoBefore issuing any rebuild, Claude Code did its own read of the index inventory and surfaced the gotcha up front.
13 indexes in USERS. Two belong to DBTOOLS$MCP_LOG (the PK and a system-managed LOB index). Those will hit the same MCP guardrail, and the LOB index cannot be rebuilt directly anyway. Rebuilding the remaining 11 online to DATA now.
That read saved an attempt that would have failed twice. The eleven user-owned indexes were rebuilt with ALTER INDEX ... REBUILD ONLINE TABLESPACE data and verified VALID after the move. A snippet of the verification output is shown below.
INDEX_NAME TABLE_NAME OLD_TS NEW_TS
------------------- ------------- ------- ------
COUNTRY_C_ID_PK COUNTRIES USERS DATA
DEPT_ID_PK DEPARTMENTS USERS DATA
EMP_EMAIL_UK EMPLOYEES USERS DATA
EMP_EMP_ID_PK EMPLOYEES USERS DATAAfter both moves, eleven of eleven user tables and eleven of thirteen indexes live in DATA. The two remaining indexes both belong to the protected DBTOOLS$MCP_LOG table.
The two orphaned indexes still needed to land somewhere. Since the MCP guardrail blocks any write to DBTOOLS$MCP_LOG and its supporting objects, I asked Claude to generate a script I could run by hand from a regular SQLcl session outside the MCP layer.
❯ show me the sql statement and also write it out to a .sql
file for me to execute to manually move the indexes for the
DBTOOLS$MCP_LOG table to the data tablespaceClaude returned the DDL inline and wrote the file at move-dbtools-mcp-log-indexes.sql. The two statements that do the work are below.
-- Regular PK index, rebuilt online into DATA.
ALTER INDEX selectai_user."SYS_C008886" REBUILD ONLINE TABLESPACE data;
-- LOB index cannot be rebuilt directly. Move the LOB segment
-- and the system-managed LOB index follows.
ALTER TABLE selectai_user."DBTOOLS$MCP_LOG"
MOVE ONLINE
LOB (LOG_MESSAGE) STORE AS (TABLESPACE data);The reason the script is split into two statements is that LOB indexes in the SYS_IL... family cannot be rebuilt with ALTER INDEX REBUILD. The supported way to relocate them is to move the LOB segment, and the index follows along automatically. Claude recognized this constraint up front and structured the script accordingly, rather than issuing two ALTER INDEX REBUILD statements that would have failed on the second one.
One last housekeeping step kept the schema consistent. The SELECTAI_USER default tablespace was still USERS, which would send any future table or index back there unless an explicit TABLESPACE clause was provided. One more prompt pointed it back to the DATA tablespace.
❯ let's also change the default tablespace for selectai to be dataALTER USER selectai_user DEFAULT TABLESPACE data;Auditing Archive Log Generation
The database is in ARCHIVELOG mode, which means every committed change ends up in an archive log file. Knowing how many of those a workload produces, and when, drives backup retention sizing, recovery point planning, and storage forecasting. The next prompt is a textbook DBA question.
❯ i see that i am in archivelog mode. how many archive logs have i
generated this week broken down by day and by the hourClaude queried V$ARCHIVED_LOG, aggregated the results across the past seven days, and returned a three-part report covering summary, daily breakdown, and hourly breakdown. The first four rows of the hourly view are shown below.
LOG_DAY HR LOGS SIZE_MB
---------- -- ---- -------
2026-04-09 19 1 176.4
2026-04-10 01 1 176.4
2026-04-10 07 1 174.1
2026-04-10 19 1 175.9The full report covered 23 archive logs totaling about 3.89 GB across the week.
↳Claude's Assessment
After returning the data, Claude added its own read of what the numbers said, and three observations stood out.
Saturday and Sunday each produced five archive logs, more than double the average weekday, which points to scheduled batch loads or weekend reporting jobs concentrated on those two days. Each archive log lands at roughly 175 to 200 MB, and that matches the 200 MB online redo group configuration on this database, so redo is filling and switching naturally rather than being forced. One outlier on April 13 at 09:00 came in at 91.9 MB, well below the steady-state size, and the most likely cause is a manual log switch or a clean shutdown event rather than a regular redo cycle.
Treating that read-back as a deliverable rather than an aside changes the feel of MCP-driven DBA work, because the agent does not stop at a result set, it goes on to interpret it.
Generating Operational Tooling
For this last example of demonstrating the power of MCP, I asked Claude to write a complete, runnable shell script that automates a daily DBA health check.
❯ Write a comprehensive Oracle database health check shell
script that monitors tablespace usage over 85%, checks for
long-running sessions over 60 minutes, validates RMAN backup
completion, checks ASM diskgroup usage, and alerts via email.
Format output as HTMLClaude returned a 200-line bash script with configurable thresholds, four queries against DBA_TABLESPACE_USAGE_METRICS, V$SESSION, V$RMAN_BACKUP_JOB_DETAILS, and V$ASM_DISKGROUP, an HTML report builder, MIME-encoded email delivery via sendmail, exit codes for cron and Nagios integration, and graceful handling of non-ASM environments. The first 20 lines are below.
#!/usr/bin/env bash
# =============================================================
# oracle-healthcheck.sh
#
# Runs four health checks against an Oracle database, renders
# the results as an HTML report, and emails it. Designed to be
# scheduled from cron.
#
# Checks:
# 1. Tablespaces over TS_THRESHOLD_PCT (default 85%)
# 2. User sessions active longer than SESSION_THRESHOLD_MIN
# minutes (default 60)
# 3. RMAN backup jobs in the last RMAN_WINDOW_HOURS hours,
# flagging missing or non-COMPLETED jobs
# 4. ASM diskgroups over ASM_THRESHOLD_PCT (default 85%)
#
# Exit codes:
# 0 all checks passed
# 1 at least one alert
# 2 script setup error (bad config, SQL connect failed)The full script is available here: oracle-healthcheck.sh. Drop it on a database host, override the environment variables at the top of the file to match your environment, and schedule it from cron.
Generating an RMAN Backup Strategy
The health check script monitors the database. The next prompt asked Claude Code to protect it. I described the backup strategy I wanted in plain English and let it produce the RMAN script.
❯ Create me a comprehensive RMAN backup script that performs
level 0 backups on the weekend night (Saturday) and
incremental level 1 backups all the other nights. I want
archive logs to be backed up 2x before it is deleted. I want
to maintain 15 days of backups of database files and
archivelogs.Claude created a professional grade RMAN shell script with configurable thresholds via environment variables, pre-flight validation, structured logging, post-run error scanning, and a 30 day log retention cleanup. The first 20 lines of the code examples are shown below.
#!/usr/bin/env bash
# =============================================================
# rman-backup.sh
#
# Comprehensive RMAN backup script for Oracle databases.
#
# Schedule:
# - Saturday night: Level 0 (full) incremental backup
# - All other nights: Level 1 incremental backup
#
# Policies:
# - Archive logs backed up 2 times before deletion
# - 15-day recovery window for database files and archive logs
# - Compressed backupsets with parallelism 2
# - Controlfile and SPFILE backed up every run
#
# Maintenance:
# - Crosscheck and remove expired/obsolete backups each run
#
# Usage:
# chmod +x rman-backup.shThree design decisions in the script are worth a closer look.
The archive log deletion respects the 2x backup policy. The script configures ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK and then uses DELETE ALL INPUT in the archive log backup step, so RMAN will only remove an archive log from disk after it has been backed up at least twice.
The Saturday detection uses date +%u. The script checks the day of the week (6 = Saturday) and sets LEVEL=0 for a full backup or LEVEL=1 for an incremental backup. Both the Saturday and the weeknight schedules run from the same crontab entry without any cron splitting.
Even when RMAN exits with code 0, the script greps the log for RMAN- messages and exits with a non-zero exit code if any are found. This prevents the script from reporting success when RMAN logged warnings that need attention.
The full script is available here: rman-backup.sh. You can copy it to a database host, set the environment variables, add the crontab entry, and the backup strategy is live.
Your AI is Being Carefully Watched
Every SQL statement that MCP executes against the database is logged automatically in a table called DBTOOLS$MCP_LOG. The table is auto-created in the first schema that connects through the SQLcl MCP server. Every subsequent statement is recorded with the MCP client name, the LLM model that generated it, the endpoint called, the full SQL text, and a timestamp.
SELECT id, mcp_client, model, end_point_name,
TO_CHAR(created_on, 'YYYY-MM-DD HH24:MI:SS') created_on
FROM DBTOOLS$MCP_LOG
ORDER BY id DESC
FETCH FIRST 20 ROWS ONLY;ID MCP_CLIENT MODEL END_POINT_NAME CREATED_ON
--- ------------ ----------------- --------------- -------------------
16 claude-code claude-opus-4-6 run-sql 2026-04-16 14:38:17
15 claude-code claude-opus-4-6 run-sql 2026-04-16 14:38:05
14 claude-code claude-opus-4-6 run-sql 2026-04-16 14:37:47
13 claude-code claude-opus-4-6 run-sql 2026-04-16 14:37:13The LOG_MESSAGE column stores the full SQL text of each statement. If you look at any of those entries, you will notice that every query includes an embedded comment that identifies which LLM generated it:
SELECT /* LLM in use is claude-opus-4-6 */ owner, table_name
FROM dba_tables
WHERE owner = 'SELECTAI_USER';That comment is injected by the SQLcl MCP server automatically. It means every SQL statement that runs through MCP is traceable in the audit log table and in the database's own SQL monitoring views like V$SQL and V$ACTIVE_SESSION_HISTORY. If a query shows up in an AWR report or a performance trace, the comment immediately identifies it as AI-generated and tells you which model produced it.
The full column inventory of DBTOOLS$MCP_LOG includes ID, MCP_CLIENT, MODEL, END_POINT_TYPE, END_POINT_NAME, LOG_MESSAGE (CLOB), CREATED_ON, CREATED_BY, UPDATED_ON, and UPDATED_BY. That gives you a complete audit trail of who connected, what model they used, what SQL was generated, and when it ran. For organizations that require change management approvals or SOX compliance on database operations, this table is the artifact that proves what happened and when.
Conclusion
In this blog post, we worked through a full range of database administration tasks using MCP and Claude Code, and each of them ran from a single English prompt. We pulled a complete profile of the database including version, mode, redo log configuration, tablespaces, datafiles, and total size on disk. We then surveyed every application schema owner, broke their objects down by type, and measured space consumption by segment type. We created a new DATA tablespace with conventions inferred from the existing inventory, and we moved all of the user tables and eleven of the thirteen indexes to it online with zero downtime, handling a quota grant along the way and generating a manual script for the two objects protected by the MCP guardrail. We also changed the default tablespace for the schema so that future objects would land in the right place without an explicit clause.
On the operational side, we queried a week of archive log generation broken down by day and hour, and we let Claude Code interpret the results in plain English. We produced a 200-line bash script that monitors tablespace usage, long-running sessions, RMAN backup completion, and ASM diskgroup usage, renders the results as HTML, and emails the report. We generated a production-ready RMAN wrapper with Level 0 full backups on Saturday nights, incremental Level 1 backups every other night, a 2x archive log deletion policy, 15-day retention, and post-run log scanning for silent failures. Finally, we queried the DBTOOLS$MCP_LOG table to show how every SQL statement run through MCP is logged with the client, the model, the full SQL text, and a timestamp, along with the embedded /* LLM in use is ... */ comment that makes every AI-generated query traceable in AWR and V$SQL.
You did not open a SQLcl session, you did not compose a dictionary query against the data dictionary, and you did not write a shell script from scratch. The work that would have taken a DBA the better part of an afternoon was finished in a conversation.
↳A Word on AI, Compliance, and Common Sense
Everything in this post ran against a sandbox database. Before pointing MCP at anything that matters, there are a few ground rules worth stating plainly.
The first rule is to keep AI away from production. Use a development or staging environment for exploration, and if you are working with production data, treat the AI session the way you would treat a junior DBA on their first week. That means read-only access, no DDL privileges, and a senior engineer reviewing every output before it touches a live system.
The second rule is to start with least privilege and build trust over time. Grant the MCP connection a database user with SELECT-only access on the views and tables it needs to do the job, and as you gain confidence in the outputs, you can widen the permissions incrementally. There is no reason to hand an AI agent DBA-level access on day one.
The third rule is to have AI generate code rather than execute it. When the task involves production changes, ask Claude Code to produce the DDL, the shell script, or the RMAN strategy as a deliverable. Review it, test it in a lower environment, get a second pair of eyes on it, and then run it yourself. The RMAN backup script and the health check script in this post were both handled that way. Claude Code wrote them, and a human decides when and where they run.
Treat AI as a force multiplier on tasks a DBA already knows how to do. The value is in the speed and the coverage it brings to that work, and the judgment about when and where to run the output is still yours. The guardrails are yours to set within your own environment.
What you have seen here is a small slice of what MCP unlocks for a DBA. Performance tuning, security posture reviews, patching workflows, Data Guard management, and capacity planning all live in the same toolbelt, and each of them could carry its own post.
Charles is an Oracle ACE Director, VMware vExpert, and author of over a dozen books in the Oracle ecosystem.