I’ve moved on from my previous script which generated SELECT statements on Oracle databases using an LLM. I’m using Claude 4.6 through AWS Bedrock. In this post I will describe my latest tool which I cleverly named “readonly” for lack of better creativity. The point is that I let Claude generate commands that I then run against our databases and their hosts, but these commands are safe because they are read only. They don’t change anything.
Like my previous post this is a purely human post. I’m not using any AI to generate or check the text. I’m not even using my normal spell check and grammar checker. This is pure me which hopefully is a good thing.
I haven’t really written a business oriented script. I’ve stuck with making a tool that would help me as a DBA to do my job better. Of course the things I’m learning could be applied to business needs as well. I’m just more knowledgable about the DBA tasks that I’ve been doing than some business financial details that you might want to apply AI to.
I’ll get right into the heart of it. Here is the prompt I am using in my readonly.py script:
You are a database administration assistant that can run several commands to answer
a question about our databases. You have the following commands available:
query(database,select_statement) - You pass the name of one of our databases and a select statement and you get the results of the query back.
Example: query("ORCL","SELECT * from DUAL;")
dblist() - Returns a list of database names and the hosts that they run on.
Example: dblist()
readfile(host,file) - Returns up to 1000 lines from the given file on the given host.
Example: readfile("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace/alert_MY19DB.log")
standards() - Describes our standards for Oracle databases.
Example: standards()
directory(host,path) - Runs ls -altr on the given path and host to show list of files sorted by reverse date and time
Example: directory("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace")
tracefiledir(database) - Returns the filesystem path for the trace files and alert log
Example: tracefiledir("MY19DB")
diskfree(host) - Shows the free space on the host's filesystems
Example: diskfree("mylinuxdbhost")
awrlasthour(database) - Does a text AWR report for the most recent pair of hourly snapshots
Example: awrlasthour("MY19DB")
oracleprocesses(host) - Does a ps -ef | grep oracle on the database host to show oracle owned processes
Example: oracleprocesses("mylinuxdbhost")
networkcheck(host) - Tests network connectivity and latency from the local machine to a host
Example: networkcheck("mylinuxdbhost")
oraclehome(database) - Gets the path where the Oracle database software is installed for the database
Example: oraclehome("MY19DB")
patches(database) - Show the patches that are installed in the database's software
Example: patches("MY19DB")
sqlstat(database,sql_id) - Show the execution history of a single SQL statement
Example: sqlstat("MY19DB","f3yfg50ga0r8n")
crontab(host) - Show the scripts which oracle has scheduled to run in cron
Example: crontab("mylinuxdbhost")
uptime(host) - Show how long the host has been up since last reboot
Example: uptime("mylinuxdbhost")
parameter(database,parameter) - Show the value of a parameter on a database
Example: parameter("MY19DB","sga_target")
dbversion(database) - Show the version of the database like 11.2.0.3
Example: dbversion("MY19DB")
grep(host,file,pattern) - Search a file on a host for a pattern containing only letters,numbers,- or _
Example: grep("mylinuxdbhost","/oracle/admin/diag/rdbms/MY19DB_new/MY19DB/trace/alert_MY19DB.log","ORA-")
stop() - Break out of loop and generate final report if you have run at least 5 loops.
Example: stop()
Please only generate ASCII text.
Please keep to no more than 80 character lines.
Do not produce anything but a command in the syntax given.
Do not attempt to answer the question. Only generate commands that may help answer it.
Here is the question you are trying to answer:
Describe the active sessions on the MY19DB database.
I’ve hacked the real prompt to hide the real name of the database and its host. In the real prompt MY19DB is the name of a real non-prod database and mylinuxdbhost is its real host name (a virtual machine with Redhat Linux 7).
But the main point here is that I have given Claude 4.6 these commands to execute to answer the question which is at the end of the prompt.
- query(database,select_statement)
- dblist()
- directory(host,path)
- tracefiledir(database)
- diskfree(host)
- awrlasthour(database)
- oracleprocesses(host)
- networkcheck(host)
- oraclehome(database)
- patches(database)
- sqlstat(database,sql_id)
- crontab(host)
- uptime(host)
- parameter(database,parameter)
- dbversion(database)
- grep(host,file,pattern)
- stop()
This may be too many commands. I plan to try commenting some of these out if they seem redundant. They mimic the type of tasks I do in my DBA work. For example, oracleprocesses(host) runs ps -ef | grep oracle on the database server. It is a safe read-only command and I do it periodically when I checking things on the server side.
I don’t know about parameter(database,parameter). It already has query(database,select_statement) so it can just query v$parameter for the parameter value.
So, this list may need to be refined, but you can see that it goes beyond what a select only tool could do by allowing access to the host in, hopefully, a safe read-only fashion.
The key to this whole approach and to the selectloop script that preceded it is that each call to Claude 4.6 except for the last one is a thinking step. It has an initial question like this one:
Describe the active sessions on the MY19DB database.Then it has the history of previous commands and their output. It also has a description of the commands and the other instructions in the prompt.
Do not produce anything but a command in the syntax given.
Do not attempt to answer the question. Only generate commands that may help answer it.The Claude inferences in the loop are all instructed to only generate commands. Then at the end there is a final inference to process all the history of commands and their output into a report.
def generate_answer(question, history):
"""
Make a final report based on the original question plus
the history of commands and their outputs.
"""
# Get history in text form
history_text = make_history_text(history)
# prompt header
header = """
Please generate a short report which explains this subject based on
the following question and the history of commands that were run and their outputs.
Please only generate ASCII text.
Please keep to no more than 80 character lines.
Please keep the output at no more than 30 lines.
Question:
"""
# Combine header, question, and history
combined = header + question + history_text
timeout_secs = config.LLM_TIMEOUT_SECS
response = getresponse(combined,timeout_secs)
# handle max tokens error
if response == "ERROR: An error occurred (ValidationException) when calling the InvokeModel operation: Input is too long.":
# remove last entry in history
history.pop()
# Get history in text form
history_text = make_history_text(history)
# Combine header, question, and history
combined = header + question + history_text
response = getresponse(combined,timeout_secs)
return response
One limitation is that with AWS Bedrock and Claude 4.6 I am limited to 1,000,000 tokens which is a few megabytes usually. The prompt + command history + question can’t exceed 1 million tokens or it throws an error and generates nothing.
I’m kind of intrigued by the 1,000,000 token limit. How do you code around it to get the LLM to generate one command after another applying all it knows without blowing out the limit? I’ve done things like limiting the number of rows returned by any query to 1000 and limiting the files downloaded to just 1000 lines. These limits are really just determined by practice. I’ve thought about using some sort of summary to compress the current history of commands and output but haven’t tried it.
In addition to the token limit, I have a time limit. I don’t want this to run for hours. I want to kick it off for a few minutes and then see what it comes up with. If I don’t write the question the right way it could be spinning for a long time going down the wrong path. I was doing 30 loops and 2 minute timeouts on selectloop. Sometimes 30 loops isn’t enough with all these commands so I’m tweaking that limit. I just put in the stop command which allows the LLM to choose to break out of the loop early. I’m playing with a minimum of 30 loops but then allow it to run up to 60 if it doesn’t generate stop().
I think I mentioned before the same sort of thing for selectloop. The history of the commands and their output is as valuable or more valuable than the final report. I can see what it queried and what files it looked at.
So what? I think that people still aren’t very excited about this kind of thing. It’s great that it runs 30-60 commands and summarizes their output, but it often gets the wrong answer. It requires a knowledgeable person (me) to review it and make their own judgement. I think people want something with an answer they can trust. I think they want an AI script that they can plug into an automation and produce a result that you can do updates based on safely. That isn’t what this is. This is a tool for maximizing an experienced user’s time by doing an independent investigation in tandem with the manual steps that user is doing to solve the same problem. Now, maybe some sort of experienced business analyst or financial analyst could use a similar tool. Instead of querying DBA_ views it could query business application tables and read application files from servers. Then the business user could review both the final output and the incremental steps of discovery that the AI tool took and use their own judgement. I think we want something that an untrained user can rely on blindly and I just haven’t come up with it yet. But it is helping me be more effective.
More to come. I hope this AI free all Bobby post is helpful.
Bobby
