Oracle Agent Memory: Store State and Retrieve Context

Agent memory in Oracle Database is a set of rows that the agent writes and queries using standard SQL. Oracle stores the rows durably on disk, so they survive process restarts, connection drops, and planned database maintenance.

The advantage of using Oracle for agent memory is that it fits into an environment where Oracle is already the primary data store: the same connection pool, the same backup schedules, and the same access controls apply to the memory tables. Tools like fenbs hold the shared project decisions separately, so project-level rules and the current task list do not mix with application data in the database.

Designing a memory table in Oracle

A minimal memory table needs five columns: a primary key, a session ID, a key string, a value (CLOB for long text), and a timestamp. The session ID groups memories by conversation or workflow run. The key string identifies what is stored. The timestamp supports filtering for the most recent value and auditing when a fact was written.

CREATE TABLE agent_memory (
  id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  session_id  VARCHAR2(255) NOT NULL,
  mem_key     VARCHAR2(255) NOT NULL,
  mem_value   CLOB,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE INDEX idx_agent_memory_session ON agent_memory (session_id, mem_key);

Add the index on (session_id, mem_key) from the start. Without it, reading the latest value for a key requires a full table scan as the table grows. The index makes the common read pattern fast even at millions of rows.

Writing and reading agent memory with SQL

To write a memory item, insert a new row. To read the latest value for a key in a session, select the row with the highest created_at for that session and key combination.

-- Write
INSERT INTO agent_memory (session_id, mem_key, mem_value)
VALUES ('session-001', 'project-goal', 'Redesign the contact form');

-- Read latest
SELECT mem_value FROM agent_memory
WHERE session_id = 'session-001' AND mem_key = 'project-goal'
ORDER BY created_at DESC
FETCH FIRST 1 ROWS ONLY;

This append-only pattern preserves history: each write adds a row rather than overwriting an existing one. To retrieve the full history of a key, remove the FETCH FIRST clause and sort by created_at. For more on storage patterns, see agent memory vs RAG and how to build agent memory.

Testing retrieval and retention

Run a basic persistence test after setting up the table.

1. Write a known value for a test key in a test session ID.
2. Disconnect from the database and reconnect in a new connection.
3. Query the test key and confirm the value is returned.
4. Restart the Oracle instance (on a test environment) and repeat the query to confirm the row survived the restart.

If the row is not returned after a reconnect, check that the INSERT was committed. A transaction that was not committed is not durable in Oracle. If the row disappears after a restart, check the tablespace and redo log configuration with your DBA.

Access boundaries and security

Grant the agent's database user only the privileges it needs: INSERT and SELECT on the memory table, nothing else. Do not use a DBA account for the agent connection. Oracle's row-level security (Virtual Private Database) can restrict which rows a session can read or write if you need to isolate memory across tenants or users.

Keep the memory table in a dedicated schema rather than in the application schema. This makes it easy to grant access separately, to back up the memory data on its own schedule, and to remove the memory tables without affecting application tables. See agent memory security for a broader treatment of memory isolation.

Archiving and pruning old memory rows

An agent that runs daily will accumulate thousands of rows. Without a cleanup strategy, the memory table grows without bound. Queries slow down as the table grows, even with an index, and storage costs increase. Choose between time-based archiving and session-based pruning based on your retention requirements.

Time-based archiving copies rows older than a threshold to a separate archive table, then deletes them from the main table. Run this as a scheduled job: insert the old rows into the archive first, confirm the count matches, then delete from the source. Never delete before inserting; a failure between the two steps would lose the data permanently.

Session-based pruning removes all rows for a session once it is marked complete. Add a session_status column and update it to done when the agent finishes a run. A cleanup job can then remove all completed sessions older than a short grace period. This approach keeps the table focused on active sessions and is simpler to schedule than a time-based archive.

For very high-volume agents, discuss partitioning the memory table with your DBA before the table is created. Range-partitioning by created_at lets Oracle drop old partitions in seconds rather than running a slow DELETE across millions of rows. Adding partitioning to an existing table requires a table rebuild, so plan the partition strategy at schema creation time.

How to do this with fenbs

Use Oracle for the agent's operational memory: session history, extracted facts, and intermediate state. Use fenbs for the project decisions and the shared work record.

1. Create a free account and add your project to fenbs. Record the standing rules in Decisions and rules.
2. Connect your assistant from /integrations. Ask it to read fenbs context at the start of each session.
3. Create the memory table in a dedicated Oracle schema with the five-column structure above.
4. Add the session-key index from the start.
5. Grant the agent's database user only INSERT and SELECT on the memory table.