DEV Community: Oracle Developers

Agent‑safe change delivery on Oracle: discovery, online mechanics, idempotent migrations, and provable rollbacks

Mark Nelson — Thu, 28 May 2026 15:04:13 +0000

This is article 7 of 8 in my Oracle AI Database Skills series.

Key Takeaways

The safety net is the process, not the model. Discovery before proposal, preview before apply, session-scoped verification before broad exposure, and a prepared rollback path — these steps protect against risk regardless of how confident the assistant sounds.
Invisible indexes let you test the impact of a change in one session before committing it to all queries. A change that can be reversed with a single statement is the safest kind to ship.
Liquibase updateSQL and Flyway dry-run output play the same role as SHOWSQL for migrations: they show exactly what SQL would execute before anything changes, giving a human an explicit approval moment.
DBMS_REDEFINITION and Edition-Based Redefinition reduce blast radius for structural and code changes by keeping the previous version available as a rollback window through the entire cutover period.

Change is where ambiguity turns into outages. If you let an assistant propose schema or index changes on Oracle Database, slow the work down just enough to make it safe. The pattern across this series is route → act → trust: route proposals through Oracle Database Skills so the assistant performs discovery and establishes intent; act only through a bounded, inspectable MCP surface (SQLcl MCP, where MCP is Model Context Protocol—locally, or a managed MCP in Autonomous AI Database when available); and deliver changes with Oracle‑native online and editioning mechanics, inside a migration workflow that’s idempotent on rerun and leaves a trail you can prove later.

This piece shows that posture in motion. We’ll take one additive change an assistant proposes, preview it, apply it safely, verify it helped, and capture the evidence—with a rollback ready if it didn’t.

Version scope and expectations

Most examples target Oracle Database 19c and later. We rely on invisible indexes and session testing via the OPTIMIZER_USE_INVISIBLE_INDEXES parameter (settable at the session or system level) as documented in the 19c Database Reference. For index maintenance, we use online index rebuilds where the 19c SQL Language Reference documents them for your index type and release (ALTER INDEX ... REBUILD ONLINE). For structural table changes under DML load, we point to DBMS_REDEFINITION (19c PL/SQL Reference). For view/package evolution, we use Edition‑Based Redefinition (EBR). Migration previews use Liquibase or Flyway state tables and dry‑run modes. Evidence and rollback rely on session tagging, SQLcl MCP logging, and (optionally) Unified Auditing.

If you’re on Oracle Database 26ai or Autonomous AI Database, you can also evolve vector indexes with a build‑new → validate → drop‑old pattern and measure recall/latency with DBMS_VECTOR. Always confirm exact syntax and capabilities for your Release Update (RU) and service tier.

ALTER INDEX (ONLINE for REBUILD): 19c SQL Language Reference
OPTIMIZER_USE_INVISIBLE_INDEXES: 19c Database Reference
Managing invisible indexes: 19c Admin Guide
DBMS_REDEFINITION: 19c PL/SQL Reference
CREATE VECTOR INDEX, VECTOR_DISTANCE, DBMS_VECTOR: 26ai docs
SQLcl MCP restrict levels, tools, monitoring: 25.x SQLcl docs
Liquibase and Flyway: vendor docs

Why agent‑safe change matters most

Assistants are good at pattern matching; they are bad at implicit context. Database change has a lot of implicit context: constraints you can’t see from one table, write paths you forgot exist, and optimizer behavior that swings when you add the “perfect” index. The way to make an assistant useful here is to force it to surface what it thinks it knows before it acts. That’s what Oracle Database Skills are for: schema discovery, intent disambiguation, destructive‑operation guards, and idempotency patterns live right next to delivery mechanics—migrations, online operations, EBR, and testing—so the work becomes a sequence you can reason about, not a free‑form prompt.

The assistant can still draft the change. Your job becomes review and control: you see the SQL (Article 5’s SHOWSQL discipline), you preview the migration (dry run), you apply through a constrained channel (MCP), and you verify impact before broad exposure (invisible index testing, or EBR for code). When that loop is the norm, teams stop arguing about whether to “let the model write DDL” and start shipping low‑risk improvements steadily.

Safety before execution: discovery, intent, and rerunnable SQL

Change work starts with a map. If the assistant suggests an index for DOCUMENTS, its first step isn’t to create it; it is to list what already exists and establish the blast radius.

Inventory what’s there (use USER_* views in‑schema; ALL_*/DBA_* if querying across schemas):

SELECT index_name, visibility, status
FROM   user_indexes
WHERE  table_name = 'DOCUMENTS';

Also list indexed columns so you don’t reinvent an existing index:

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'DOCUMENTS'
ORDER  BY index_name, column_position;

If the assistant claims “add an index to speed source and published date filters,” make it specify the target predicates and expected plan effect. If it can’t state the WHERE clause and the rows accessed in plain terms, it doesn’t understand the workload yet. Article_06 showed why this matters: you don’t expose a new index broadly until you’ve seen it influence a plan the way you expect.

For DML, destructive‑operation guards are non‑negotiable. Updates and deletes happen only with a WHERE clause you can count. Preview the scope:

SELECT COUNT(*)
FROM   documents
WHERE  source = 'kb'
AND    published >= DATE '2025-01-01';

Oracle doesn’t enforce WHERE clauses on UPDATE/DELETE; enforce guardrails in tooling (preview gates) and code review.

Reruns should be safe by default. On the data side, MERGE turns brittle “insert then update” sequences into idempotent steps:

MERGE INTO config t
USING (SELECT 'max_connections' AS key, '100' AS value FROM dual) s
ON (t.key = s.key)
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (s.key, s.value);

On the DDL side, migration tools carry the idempotency for you with state tables and checksums. Liquibase tracks applied changeSets in DATABASECHANGELOG and coordinates with DATABASECHANGELOGLOCK, and Flyway uses flyway_schema_history. Crucially, both can emit exactly what they would execute. Liquibase’s updateSQL and Flyway’s dryRunOutput are the human gate that pairs well with Article 5’s SHOWSQL: you inspect the proposed SQL first, then approve the apply.

Preview‑gate example (Liquibase):

# Print the SQL that would run, without changing the database
liquibase --changelog-file=changelog.xml updateSQL

# Example output (truncated)
-- Changeset 20250108-idx-docs:1
CREATE INDEX docs_src_pub_idx ON documents(source, published) INVISIBLE;
-- Changeset 20250108-config-merge:2
MERGE INTO config ...

Liquibase prints the SQL it would execute against the current database state, based on DATABASECHANGELOG and checksums.

Alternatively, with Flyway (Teams/Enterprise for dry‑run variants):

# Generate a dry-run script of pending migrations
flyway -locations=filesystem:sql -dryRunOutput=preview.sql migrate

dryRunOutput is not available in Community; use Teams/Enterprise for dry‑run script generation.

From preview to apply: bounded MCP, not ad‑hoc credentials

Once you’ve reviewed a change, you still need a controlled way to act. That’s the MCP layer: the SQLcl MCP Server locally, or a managed MCP experience in Autonomous AI Database if your tier and RU expose one.

SQLcl MCP exposes a deliberately small tool surface—connect, disconnect, list saved connections, run SQL—over a stdio server. In the 25.3 docs, Level 4 is the most restrictive. Check your installed version’s “Restrict levels” page for the default, and keep the highest level practical in shared environments. The recommended posture is to use pre‑saved least‑privilege connections; restrict levels can be configured to disallow ad‑hoc credentials and keep the tool surface small. The server tags sessions (V$SESSION.MODULE and .ACTION) and writes activity to DBTOOLS$MCP_LOG once used, so DBAs can see who did what and when. This is what “bounded action” looks like: the assistant can only do the few things the server allows, with least privilege and a record behind it. See “Using the SQLcl MCP Server,” “Restrict levels,” and “Monitoring” in the SQLcl docs for the precise tools, defaults, and logs for your version.

If your Autonomous AI Database service and RU expose a managed MCP endpoint (see your service’s Help Center “Use MCP Server” page), you can integrate assistant actions with database identity, network controls, and auditing. Capabilities and APIs vary by service tier and RU; verify availability and procedures in your tenancy documentation. If a managed MCP endpoint isn’t available for your tier/RU, use the SQLcl MCP Server approach above.

Reduce blast radius with Oracle’s online mechanics

Not every change needs a window. Oracle has first‑party features for evolving structures while the system stays available.

Structural changes under write load use DBMS_REDEFINITION. The flow is predictable—eligibility check, start, copy dependent objects and constraints, sync the interim table, finish, and clean up—and the documentation spells out object and LOB rules per release (19c PL/SQL Reference).

A compact, typical plan:

Eligibility (choose key strategy; use CONS_USE_PK when a primary key exists, or CONS_USE_ROWID otherwise—confirm constraints and object types supported in your version):

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'APP_OWNER',
    tname        => 'ORDERS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

Create interim table with desired structure (required unless supplying a column mapping to START_REDEF_TABLE):

-- Example: like-for-like copy you will later adjust as needed
CREATE TABLE app_owner.orders_int
AS SELECT * FROM app_owner.orders WHERE 1=0;

Start:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname     => 'APP_OWNER',
    tname     => 'ORDERS',
    int_table => 'ORDERS_INT'
    -- , col_mapping => '...'  -- use when structures differ
  );
END;
/

Copy dependents (constraints, triggers, indexes, grants) and track errors:

DECLARE
  v_errs PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'APP_OWNER',
    tname            => 'ORDERS',
    int_table        => 'ORDERS_INT',
    num_errors       => v_errs,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_indexes     => TRUE,
    copy_statistics  => TRUE
  );
END;
/

Sync:

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('APP_OWNER', 'ORDERS');
END;
/

Finish and swap:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('APP_OWNER', 'ORDERS');
END;
/

Cleanup: reconcile stats/grants, then drop interim artifacts as appropriate.

LOBs, materialized view logs, row movement, and editioning interactions have version‑specific rules—confirm them in your release documentation before use.

When your change is in editioned code rather than the table itself, Edition‑Based Redefinition lets you run two versions at once and cut over on your terms. Tables are not editioned, so the safe pattern is to shield them behind editioning views and synonyms, evolve the views or packages in a child edition, test in that edition, then switch references.

Prerequisites (typically applied by a DBA):

System privileges: CREATE ANY EDITION (to create editions), DROP ANY EDITION (to retire), and the ability to ALTER USER ... ENABLE EDITIONS.
Object privileges in the target schema to create editioning views and editioned synonyms.

Build‑plan: EBR cutover (compact)

Enable editions for your user:

ALTER USER app_owner ENABLE EDITIONS;

In ORA$BASE (or your current default edition), create an editioning view to shield the underlying table. The view name is editioned; the table is not:

CREATE EDITIONING VIEW ev_orders AS
SELECT order_id, customer_id, order_ts, status
FROM   orders;

Create a child edition:

CREATE EDITION app_v2 AS CHILD OF ORA$BASE;

Test in the child edition and evolve the API surface:

ALTER SESSION SET EDITION = app_v2;

-- Evolve API in the child edition (example: add a computed projection)
CREATE OR REPLACE EDITIONING VIEW ev_orders AS
SELECT order_id,
       customer_id,
       order_ts,
       status,
       CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END AS is_cancelled
FROM   orders;

Cutover: change which edition clients use (via connection‑level edition, services, or app routing). Keep ORA$BASE as a rollback window.
Rollback: switch sessions back to the previous edition while you investigate; retire the old edition once stable.

Indexes get two useful mechanics. First, invisible indexes let you test plan influence without affecting other sessions. You can create a new index INVISIBLE or set an existing index invisible, then in your session enable it:

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

If plans improve (verified via EXPLAIN PLAN and DBMS_XPLAN, and ideally timed execution), you can make the index visible for everyone. Second, ALTER INDEX ... REBUILD ONLINE is documented in 19c for supported index types, letting DML continue during maintenance with some restrictions. Note the careful wording: in 19c the ONLINE clause is documented for REBUILD; do not assume CREATE INDEX ONLINE for B‑tree indexes unless your exact release and index type say so.

The same mindset for vector and relational indexes

Oracle Database 26ai adds native vector indexes—HNSW (Hierarchical Navigable Small World) and IVF (Inverted File)—and vector distance operators. The safe evolution pattern is the same as for relational indexes: build a tuned index in parallel, validate it with representative queries, then drop the old one after a soak period.

-- Check your exact RU/service tier for required/optional clauses and defaults.
-- HNSW example (common defaults shown; tune per workload)
CREATE VECTOR INDEX docs_hnsw_new
ON documents(embedding)
ORGANIZATION HNSW
DISTANCE COSINE;

-- IVF example (parameter defaults vary by RU)
CREATE VECTOR INDEX docs_ivf_new
ON documents(embedding)
ORGANIZATION IVF
DISTANCE COSINE;

If your RU requires additional parameters (e.g., neighborhood or partition knobs), supply them as documented; prefer a build‑new → validate → drop‑old evolution unless your docs explicitly state online/rebuild semantics. Validate using workload queries or DBMS_VECTOR advisers/accuracy checks, watching both recall and latency.

Proving what happened—and preparing to undo it

If an assistant participates in change, you need clean answers to who did what and how to reverse it. Start by tagging sessions at the application level:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('mcp-agent','index-additive-change');
END;
/

DBAs can then look in V$SESSION for that module/action pair, and SQLcl MCP adds its own evidence in DBTOOLS$MCP_LOG with each tool‑mediated request. With Unified Auditing enabled, you can go further: define policies for specific DDL or DML and query UNIFIED_AUDIT_TRAIL to show who executed which statement and when. Access to dynamic performance views or audit trails depends on privileges; if your role is narrower, MCP logs still give you a reliable record. If you lack access to V$SESSION, ask a DBA to expose a filtered view you can query.

Rollback should be proportional to the change. For an index, the fastest backout is to mark it invisible; the hard backout is to drop it. For redefinition or EBR, the rollback is to keep the original structure or edition until final cutover and switch back if something misbehaves. For migrations, Liquibase can roll back by tag/changeSet/date when you’ve authored rollbacks (for supported change types); Flyway supports undo with authored scripts (Teams/Enterprise editions). The operative word is “authored”: write the backout path when you write the forward path.

A quick Unified Auditing check (if enabled and you have privileges):

SELECT event_timestamp, dbusername, action_name, object_schema, object_name, sql_text
FROM   unified_audit_trail
WHERE  action_name IN ('CREATE INDEX','ALTER INDEX','DROP INDEX')
AND    object_schema = 'APP_OWNER'
ORDER  BY event_timestamp DESC
FETCH FIRST 10 ROWS ONLY;

Mini‑demo: propose, review, apply, verify, prove

Let’s run one small, additive change that an assistant proposes. We’ll add a composite index to speed a common filter on DOCUMENTS. The mechanics are deliberately conservative: INVISIBLE first, session‑level test, then visible or back out.

Prerequisites for this demo:

Oracle Database 19c or later.
Privileges: CREATE INDEX on DOCUMENTS owner; ALTER SESSION; EXPLAIN PLAN; EXECUTE on DBMS_XPLAN and DBMS_APPLICATION_INFO.
Access to V$SESSION requires SELECT_CATALOG_ROLE or equivalent; if not available, rely on DBTOOLS$MCP_LOG for evidence.
SQLcl MCP Server running with a pre‑saved least‑privilege connection to the target schema.

1) Preflight discovery. If an assistant suggests an index, ask it to verify there isn’t already one that covers the same columns:

SELECT index_name, visibility, status
FROM   user_indexes
WHERE  table_name = 'DOCUMENTS';

SELECT index_name, column_name, column_position
FROM   user_ind_columns
WHERE  table_name = 'DOCUMENTS'
ORDER  BY index_name, column_position;

(Outside the owner schema, use ALL_INDEXES/ALL_IND_COLUMNS.)

2) Proposal. The assistant drafts an index for source and published. Keep it INVISIBLE so only a session that opts in can use it:

CREATE INDEX docs_src_pub_idx
ON documents(source, published)
INVISIBLE;

3) Apply through a bounded surface. Approve the statement and run it via SQLcl MCP’s SQL tool using a least‑privilege, pre‑saved connection. Configure restrict levels to keep the tool surface small and auditable; MCP logs the call and tags the session automatically in V$SESSION (subject to your privileges).

4) Test before broad exposure. Enable invisible indexes only in your session and check plan shape and timing:

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM   documents
WHERE  source='kb'
AND    published >= DATE '2025-01-01';

SELECT plan_table_output
FROM   TABLE(DBMS_XPLAN.DISPLAY());

For final validation, prefer executed plans via DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST after running the query, as EXPLAIN PLAN can differ from runtime under binds or adaptive features. If PLAN_TABLE isn’t present in your schema, either create it (DBA task) or use DBMS_XPLAN.DISPLAY_CURSOR, which doesn’t rely on PLAN_TABLE.

Also capture actual timings on representative data:

-- Show the last executed statement's plan with runtime stats (if available)
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

If the plan and timings improve, make the index visible:

ALTER INDEX docs_src_pub_idx VISIBLE;

5) Evidence. Tag the module/action explicitly and pull recent MCP entries:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('mcp-agent','index-additive-change');
END;
/

-- Evidence in V$SESSION (requires privileges)
SELECT module, action
FROM   v$session
WHERE  module = 'mcp-agent';

-- Evidence in MCP logs (column names can vary by version)
-- DESCRIBE DBTOOLS$MCP_LOG;
SELECT *
FROM   DBTOOLS$MCP_LOG
FETCH FIRST 5 ROWS ONLY;

-- If you need ordering, first identify a timestamp or sequence column via DESCRIBE/USER_TAB_COLUMNS,
-- then apply ORDER BY accordingly.

If logs appear empty, run a simple MCP run-sql request and retry—the log table populates after the first tool interactions. Querying V$SESSION requires SELECT_CATALOG_ROLE or equivalent; if you don’t have it, rely on MCP logs and application‑level evidence or ask a DBA to expose a filtered view.

6) Rollback if needed. If the index hurts a minority of queries or adds unacceptable write overhead, back it out quickly:

ALTER INDEX docs_src_pub_idx INVISIBLE; -- tactical backout
-- or
DROP INDEX docs_src_pub_idx;            -- hard revert

That’s the loop: propose through discovery, preview the SQL, act through MCP, verify locally via INVISIBLE, and record the proof.

Delivery mechanics that hold up in real pipelines

The same loop translates cleanly to CI/CD and change boards. Migration tools handle state and retries; MCP provides execution discipline; Oracle features reduce blast radius.

In practice, you keep DDL/DML changes in version control and insist on previews. Liquibase’s updateSQL prints exactly what would run for a given changelog; Flyway’s dryRunOutput does the same for pending migrations. Checksums and state tables prevent accidental reruns. Avoid hand‑run DDL outside the tool, which can desynchronize history tables from reality. For table changes that must happen while writes continue, DBMS_REDEFINITION is the predictable route; for code and view evolution, EBR gives you reversible cutovers. For online index maintenance, REBUILD ONLINE helps keep writes flowing, while INVISIBLE indexing keeps tests local until you’re ready.

Testing belongs inside this posture, not as an afterthought. For PL/SQL and schema‑anchored behavior, utPLSQL lets you write fast, repeatable tests and run them in CI. When you’re changing query paths, pair DBMS_XPLAN plan displays with simple timing harnesses and capture them in artifacts the same way you capture dry‑run SQL. This is how agents and humans occupy the same workflow: the agent proposes, the pipeline proves.

A short view for migration leads

If you’re modernizing a system or switching database platforms, treat “agent‑safe change” as the daily rhythm and migrations as a route through it. The sequence has four stages:

1 — Assess. Inventory every object by type and complexity before writing a single migration script. Oracle’s skills repo includes a migration-assessment.md skill that routes this step. In practice, complexity falls into three tiers: simple objects (tables with standard types, views, sequences) that translate with minimal manual effort; moderate objects (stored procedures, triggers, basic PL/SQL packages) that need construct-by-construct review; and complex objects (advanced types, Java stored procedures, database links, Oracle-specific partitioning strategies, or heavy use of DBMS_* packages) that require hands-on expert review. An assistant can draft the inventory query and score the tiers—your job is to sanity-check the classification before committing to a timeline.

A useful starting inventory:

SELECT object_type, COUNT(*) AS cnt
FROM   all_objects
WHERE  owner = UPPER(‘<SOURCE_SCHEMA>’)
GROUP  BY object_type
ORDER  BY cnt DESC;

2 — Translate with source-specific guidance. Each source platform has predictable problem areas. For PostgreSQL migrations, the most common friction points documented in migrate-postgres-to-oracle.md are: sequence syntax (SERIAL → GENERATED AS IDENTITY), BOOLEAN columns (Oracle has no native BOOLEAN in SQL prior to 23ai; the standard convention is NUMBER(1,0) with a CHECK (col IN (0,1)) constraint), RETURNING clauses (Oracle restricts RETURNING INTO to PL/SQL blocks, requiring application refactoring), ILIKE (rewrite as UPPER(column) LIKE UPPER(pattern) with a supporting function-based index), and DATE semantics (Oracle's DATE stores time-of-day, so equality filters like WHERE created = DATE '2024-01-15' silently miss rows that have a non-midnight time component). An assistant loaded with migrate-postgres-to-oracle.md from the skills repo can flag these automatically when reviewing source DDL—but always verify the translation with a human before running it in staging.

3 — Pilot additive changes in staging. Before cutover, run a representative subset of translated objects through the full discovery → preview → bounded apply → verify loop described above. This is where invisible indexes, preview-gate SQL, and DBMS_REDEFINITION earn their keep. Focus on the moderate and complex tiers first; the simple tier can usually be scripted and batch-applied. Confirm that application queries return the same results on Oracle as they did on the source—row counts, join shapes, and date arithmetic are the usual suspects for silent semantic differences.

4 — Cutover and verify. Keep ORA$BASE or a pre-cutover schema snapshot available as a rollback window. Use Liquibase or Flyway changelogs so the migration state is recorded and reruns are safe. After cutover, run your standard smoke tests plus any Oracle-specific validation queries you assembled during assessment. Tag the migration session with DBMS_APPLICATION_INFO so post-cutover audit queries can isolate migration activity from normal application traffic.

By the time you’re scheduling the production cutover, the mechanics should be boring. The assistant can draft scripts and surface incompatibilities; your tooling and Oracle’s online features make the process predictable and reversible.

What to remember—and what’s next

Agent‑safe change is not a bet on model intelligence; it’s a bet on process. Force discovery before action. Require a previewed SQL artifact. Act only through a bounded server with least privilege. Use Oracle’s online and editioning features to keep availability high. Tag sessions, log activity, and keep a rollback path that matches the change. When you operate this way, you can accept more assistant help without accepting more risk.

In Article 8 we’ll close the loop with governance: identity and least privilege, policy, VPD, data redaction, and Unified Auditing as a procedural trust layer around the same route → act → trust loop.

Sources and further reading

SQLcl MCP Server: tools, restrict levels, monitoring and logs
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.2/sqcug/using-oracle-sqlcl-mcp-server.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.3/sqcug/sqlcl-mcp-server-tools.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.3/sqcug/configuring-restrict-levels-sqlcl-mcp-server.html
https://docs.oracle.com/en/database/oracle/sql-developer-command-line/25.4/sqcug/monitoring-sqlcl-mcp-server.html
Online operations and index behavior (19c)
ALTER INDEX (ONLINE for REBUILD): https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-INDEX.html
OPTIMIZER_USE_INVISIBLE_INDEXES: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_USE_INVISIBLE_INDEXES.html
Managing invisible indexes: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html
DBMS_REDEFINITION (19c)
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REDEFINITION.html
EBR (editions and editioning views)
Start with the SQL Language Reference (CREATE EDITION; ALTER SESSION SET EDITION) and the Edition‑Based Redefinition Guide for your release (19c/23ai/26ai).
Vector indexing and functions (26ai)
CREATE VECTOR INDEX: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
VECTOR_DISTANCE: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_distance.html
DBMS_VECTOR: https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
Migrations tooling
Liquibase: https://www.liquibase.org/
Flyway: https://documentation.red-gate.com/fd
Evidence and auditing
DBMS_APPLICATION_INFO: PL/SQL Packages and Types Reference (match your version)
Unified Auditing and UNIFIED_AUDIT_TRAIL: Oracle Database Security Guide (match your version)
Series continuity
Article 5 (Select AI, AI Profiles, SHOWSQL) and Article 6 (index and vector evaluation) provide the inspection and evaluation habits used here.

Notes on differences across releases and editions

Index ONLINE semantics: In Oracle 19c, ONLINE is documented for ALTER INDEX ... REBUILD for supported index types with restrictions; check your exact RU and index type. Do not assume CREATE INDEX ONLINE for B‑tree indexes in 19c.
Invisible indexes: Controlled by OPTIMIZER_USE_INVISIBLE_INDEXES at the session/system level. Validate on your target RU.
Vector indexes: 26ai introduces HNSW and IVF; confirm syntax and capabilities for your RU/tier. Prefer build‑new → validate → drop‑old unless your docs state otherwise.
SQLcl MCP: Tools and restrict‑level defaults are versioned. Verify the tool list and defaults for your installed SQLcl version.

How to Enforce Role-Based Data Access in AI Applications with Oracle Deep Data Security

Mark Nelson — Thu, 28 May 2026 13:54:19 +0000

Key Takeaways

AI assistants should not rely on prompts or application code to enforce sensitive data access rules.
Oracle Deep Data Security keeps row, column, and document authorization in the database before data reaches the LLM.
DEAL uses the same SQL and vector retrieval for Linda and Wendy, while Oracle Database returns different authorized results.
The companion GitHub repository contains the runnable Python demo, including ADB-S setup scripts and an optional OCI GenAI chatbot loop.

AI-powered applications often use two kinds of retrieval at the same time: SQL for structured business records and vector search for contextual documents. A loan assistant might use SQL to list applications under review, then use vector search to retrieve policy guidance about income verification, debt-to-income exceptions, or credit risk. Both paths can reach sensitive data.

That retrieval power creates a security problem. If an assistant can call broad SQL tools or broad RAG retrievers, it might fetch data the current user should not see. Prompt injection, missing metadata filters, a malformed tool call, or a forgotten Python redaction step can turn a useful chatbot into a data exposure path.

The safest place to enforce access rules is the database, where the protected data already lives. Oracle Database security controls have been trusted for decades to enforce application access policies close to the rows and columns being protected. An LLM should not become the system of record for authorization decisions. It is probabilistic, prompt-influenced, and easy to steer into requesting more data than the user should receive.

That distinction matters because data exfiltration is expensive. A single overbroad answer can expose regulated customer data, trigger incident response and disclosure work, create audit findings, violate compliance requirements, and erode trust in the AI application. Database security policy is also more provable and auditable than LLM behavior: you can inspect grants, test queries under known identities, review database audit records, and show that enforcement happens before data leaves the database. The goal is not to make the prompt better at saying no; the goal is to make sure the database only returns data the active end user is allowed to see.

In this tutorial, we build DEAL — Deep Data Security Enabled Assistant for Lending. DEAL is a terminal-based Python loan assistant that connects to Oracle AI Database 26ai on Autonomous Database Serverless with a wallet. The demo uses direct logon as local Deep Sec end users: linda, a loan officer, and wendy, an underwriter.

The proof point is simple: DEAL runs the same broad SQL and the same vector retrieval pattern for both users. Oracle Database enforces the configured data access policies for the demo's SQL and vector retrieval paths before data reaches Python, the tool layer, or the LLM. The Python code does not filter rows by user, redact restricted fields, or filter RAG results by audience.

What Is Oracle Deep Data Security?

Oracle Deep Data Security is a database-enforced data authorization framework in Oracle AI Database 26ai. It lets applications pass end-user security context to the database so that the database can enforce fine-grained access to protected data.

In DEAL, there are four important pieces:

Database owner user: the database account Python uses for setup scripts, such as DEEPSEC.
Application end user: the human using the app. DEAL uses linda and wendy.
Deep Sec end-user context: the database identity that is active while a user-scoped operation runs.
Data roles and data grants: the Deep Data Security objects that define which rows, columns, and operations are available.

The teaching pattern throughout the demo is:

same query shape
same SQL
different Deep Sec end user
different authorized result

The no-token path uses direct logon as local Deep Sec end users. Deep Sec also supports service-user context patterns where the application connects as a service user and sets end-user context before each database operation. Some service-user patterns use external identity tokens, and some use prepared local Deep Sec end-user identities.

Use this table to choose the right identity path:

Path	How the database knows the end user	External IAM or Entra token required?	When to use it
Local Deep Sec direct logon	Python connects directly as a local Deep Sec end user such as `linda` or `wendy`	No	Best for a compact tutorial, lab, or proof where each demo user can have a local Deep Sec password
Service user with local Deep Sec context	Python connects as the application/service schema, then sets context for a prepared local Deep Sec end user	No external IAM token, but requires local end-user setup and context-key handling	Useful when the app should keep a stable service connection while still avoiding an external identity provider for a demo
Service user with OCI IAM or Microsoft Entra	Python connects as the application/service schema, then sets context using a database access token issued by the identity provider	Yes	Best for production-style apps that already authenticate users through OCI IAM, Entra, or a federated identity flow

The rest of the tutorial uses the first path: local Deep Sec direct logon. That keeps the security proof focused on database-enforced row, column, and vector retrieval behavior instead of identity-provider setup.

Deep Data Security requires Oracle Database 26ai and is supported only in python-oracledb Thin mode. Do not call oracledb.init_oracle_client() in this tutorial.

DEAL architecture: the Python assistant connects as local Deep Sec end users, and Oracle Database enforces the configured data access policies for the demo’s SQL and vector retrieval paths.

Key Features of Oracle Deep Data Security

DEAL uses a small lending scenario to demonstrate the Deep Data Security capabilities that matter most to AI application developers.

Data roles and end-user identity

Deep Sec data roles group data access permissions. DEAL uses two data roles:

LOAN_OFFICER_ROLE
UNDERWRITER_ROLE

The prepared Deep Sec end users are:

linda, assigned to LOAN_OFFICER_ROLE
wendy, assigned to UNDERWRITER_ROLE

In the no-token path, Linda and Wendy are local Deep Sec end users who can log on directly after an administrator grants a standard CREATE SESSION role through the Deep Sec data roles. That keeps the tutorial free of external IAM or Entra token setup.

Row-level security

Deep Sec data grants can include row predicates. DEAL uses row rules like these:

Linda sees loan applications where assigned_officer matches the active Deep Sec username.
Wendy sees loan applications where in_underwriting_queue = 'Y'.

The application SQL does not add WHERE assigned_officer = :end_user.

Column-level security

Data grants can restrict column access. DEAL restricts sensitive financial and underwriting fields for Linda, including:

customer_ssn
customer_income
customer_credit_score
underwriting_decision
risk_score
underwriting_notes

In the demo output, restricted values are displayed as NULL. The Python code does not replace values with None or hide fields.

Deep Sec-scoped vector retrieval

Role-scoped vector retrieval matters because RAG systems often search across policy, procedure, and knowledge-base documents that are not equally appropriate for every user. A loan officer might need intake and customer-document guidance, while an underwriter might need credit-risk escalation procedures. If the retriever ranks every nearby document first and asks the LLM to ignore unauthorized context later, sensitive guidance has already left the database. DEAL makes the authorization check happen before vector ranking returns results to the application.

DEAL stores policy documents in Oracle Database with a VECTOR(3, FLOAT32) column. The core demo uses deterministic 3-dimensional demo vectors so the tutorial stays self-contained.

The vector retrieval query runs under Linda’s or Wendy’s Deep Sec context. The application does not add a security filter such as:

WHERE audience = :role

Instead, Deep Sec data grants govern which loan_policies rows are visible to the query; the vector query ranks the rows visible under the active end-user context.

We use VECTOR_DISTANCE() in the SQL because it keeps the distance expression and metric explicit. Oracle AI Vector Search also provides shorthand vector distance operators, but this tutorial intentionally stays with VECTOR_DISTANCE() so the Deep Sec proof is easier to read. The demo uses a small data set and does not create a vector index. A vector index is not useful for proving performance or plans on eight rows; add indexing later when you move to a larger corpus and can verify index metadata and execution behavior for your workload.

Application trust boundary

The assistant is not the security boundary. DEAL’s Python code is responsible for:

Connecting to Oracle AI Database 26ai.
Binding values safely.

The database is responsible for enforcing the configured row, column, and policy-document access rules.

The demo avoids these application-side security patterns:

WHERE assigned_officer = :end_user

WHERE audience IN ('general', :role)

if end_user == "linda":
    row["customer_ssn"] = None

Those patterns can work in one carefully reviewed code path, but they are fragile as the application grows. Every new query, retriever, tool function, report, or chatbot action becomes another place where a developer has to remember the right row predicate, audience filter, and redaction rule. If one path forgets a filter, the database may return too much data before the mistake is noticed. Keeping the rules in Deep Sec data grants makes enforcement centralized, testable, and consistent across the application paths that reach the protected tables.

How to Get Started with Oracle Deep Data Security

This tutorial assumes an administrator-prepared Oracle AI Database 26ai on Autonomous Database Serverless environment where Oracle Deep Data Security is enabled and the required privileges have been granted. The demo targets ADB-S 26ai; it does not use Oracle Database Free or a local Oracle container.

Prerequisites

You need:

Oracle AI Database 26ai on Autonomous Database Serverless.
A downloaded and unzipped Autonomous Database wallet.
A tutorial owner schema user, such as DEEPSEC.
Database password for the tutorial owner schema.
A TNS alias from the wallet, or a connect descriptor using host, port, and service name values from your Oracle AI Database 26ai environment.
Wallet password.
Prepared Deep Sec end users for linda and wendy.
Passwords for the local Deep Sec end users linda and wendy.
Privileges to create Deep Sec data roles and data grants for the tutorial objects, or an administrator who has prepared those objects.
Optional: OCI Generative AI access for the chatbot extension.
Python 3.10 or later.

Prepared Deep Sec identity contract

Before you run the demo, an administrator must create or provide the Deep Sec identity setup. This tutorial uses local Deep Sec users and direct logon, so the setup includes end users such as:

CREATE END USER linda IDENTIFIED BY <password>;
CREATE END USER wendy IDENTIFIED BY <password>;

Those end users are not ordinary database schema users. Because the SQL does not quote linda or wendy, Oracle stores the end-user names using the normal uppercase identifier form: LINDA and WENDY. The setup scripts connect as the tutorial owner schema, such as DEEPSEC, while the user-facing read and RAG scripts connect directly as LINDA or WENDY.

The tutorial owner needs the Deep Sec privileges required to create data roles, grant data roles, create data grants, create end users, and enable mandatory use of data grants for the demo tables. Ask an administrator to grant:

GRANT CREATE DATA ROLE TO deepsec;
GRANT CREATE DATA GRANT TO deepsec;
GRANT CREATE ANY DATA GRANT TO deepsec;
GRANT ADMINISTER ANY DATA GRANT TO deepsec;
GRANT GRANT ANY DATA ROLE TO deepsec;
GRANT CREATE END USER TO deepsec;
GRANT CREATE END USER SECURITY CONTEXT TO deepsec;
GRANT SET USE DATA GRANTS ONLY TO deepsec;

For direct logon without tokens, an administrator also creates a normal database role with CREATE SESSION, then grants that role to the Deep Sec data roles used by the tutorial:

CREATE ROLE deal_direct_logon_role;
GRANT CREATE SESSION TO deal_direct_logon_role;

GRANT deal_direct_logon_role TO loan_officer_role;
GRANT deal_direct_logon_role TO underwriter_role;

If loan_officer_role and underwriter_role do not exist yet, create deal_direct_logon_role first, run Step 5 to create the Deep Sec data roles, and then run the two GRANT deal_direct_logon_role ... statements.

After the tutorial tables exist, the owner enables mandatory data-grant enforcement on the protected tables:

SET USE DATA GRANTS ONLY ON loan_applications ENABLED;
SET USE DATA GRANTS ONLY ON loan_policies ENABLED;

For a service-user context setup that uses an external identity provider, the DEEPSEC_DATABASE_ACCESS_TOKEN value in .env is not a database password and not a convenient static tutorial secret. It is a database access token issued by the configured identity provider, such as OCI IAM or Microsoft Entra ID, and it can expire. Your administrator or identity setup must tell you how to obtain and refresh it.

For the no-token local-user setup, use the direct-logon pattern: Python connects directly as local Deep Sec end users such as LINDA and WENDY. In that mode, the database does not need DEEPSEC_DATABASE_ACCESS_TOKEN, but an administrator must grant a standard CREATE SESSION role to the Deep Sec data roles.

The end_user_identity value passed to oracledb.create_end_user_security_context() depends on the identity model:

For IAM or Entra users, it is typically an identity token string.
For local Deep Sec users in a service-user context pattern, it can be a tuple such as ("LINDA", key) where the key comes from the prepared local-user setup.
For direct logon, the helper does not call create_end_user_security_context(); it opens the connection as the local Deep Sec end user.

The demo helper supports DEEPSEC_CONTEXT_MODE=direct_logon, external_token, or local_tuple. Use direct_logon for this tutorial.

Create the project

You can copy the files from this tutorial, or clone the companion GitHub repository that contains the same runnable demo project:

git clone https://github.com/markxnelson/deep-data-security.git deal-deepsec
cd deal-deepsec

If you are copying the code from the article instead of cloning the repository, create a project directory first:

mkdir deal-deepsec
cd deal-deepsec

python -m venv .venv
source .venv/bin/activate

On Windows PowerShell:

python -m venv .venv
.venv\Scripts\Activate.ps1

If you cloned the companion repository, create and activate the virtual environment inside that cloned directory before installing dependencies.

The companion repository already includes requirements.txt, .env.example, .gitignore, and the Python scripts. If you cloned it, use the file-creation sections below as reference and skip ahead to installing dependencies after your virtual environment is active.

Create requirements.txt:

oracledb
python-dotenv
oci

Install the dependencies:

pip install -r requirements.txt

Expected output:

Successfully installed oracledb ...
Successfully installed python-dotenv ...
Successfully installed oci ...

Add local environment variables

Create .env.example:

# Oracle AI Database 26ai on ADB-S connection.
ADB_USERNAME=DEEPSEC
ADB_PASSWORD=your-database-password

# Use an ADB wallet TNS alias such as yourdb_high,
# or a TCPS connect descriptor with host, port, and service_name values.
ADB_DSN=your-adb-tns-alias-or-connect-descriptor

# Directory containing the unzipped wallet files, including tnsnames.ora and ewallet.pem.
ADB_WALLET_LOCATION=/path/to/unzipped/adb-wallet

# Wallet password created when the wallet was downloaded.
# This is not the database user password.
ADB_WALLET_PASSPHRASE=your-wallet-password

# Deep Sec context mode must match your administrator-prepared identity setup.
# Use direct_logon for local Deep Sec users without IAM/Entra tokens.
# Use external_token for IAM/Entra-style identity strings.
# Use local_tuple when a service connection sets context for local end-user tuples.
DEEPSEC_CONTEXT_MODE=direct_logon

# Required only for service-user context modes.
# direct_logon does not use this value.
DEEPSEC_DATABASE_ACCESS_TOKEN=

# Direct-logon passwords for the local Deep Sec end users.
DEEPSEC_LINDA_KEY=your-linda-end-user-password
DEEPSEC_WENDY_KEY=your-wendy-end-user-password

# Protected object owner used when direct-logon users query the demo tables.
DEAL_OBJECT_OWNER=DEEPSEC

# OCI Generative AI settings for the final chatbot step.
OCI_CONFIG_FILE=~/.oci/config
OCI_PROFILE=DEFAULT
OCI_GENAI_ENDPOINT=https://inference.generativeai.us-chicago-1.oci.oraclecloud.com
OCI_GENAI_COMPARTMENT_ID=your-compartment-ocid
OCI_GENAI_MODEL_ID=your-generative-ai-model-id

Copy it to .env and edit the values:

cp .env.example .env

Never commit .env to source control. Add .gitignore:

.env
.venv/
__pycache__/
*.pyc
.pytest_cache/
.DS_Store

DEAL Demo Project

The demo uses small scripts that build on each other. Run them in order:

python 01_verify_deepsec.py
python 02_create_schema.py
python 03_load_data.py
python 04_configure_deepsec.py
python 05_context_read_demo.py
python 07_vector_basics.py
python 08_secure_rag_retrieval.py
python 08b_keyword_vs_vector_check.py  # optional

After Step 8, the Deep Sec security proof is complete. The remaining scripts wrap the proven operations as DEAL tool functions, print a final terminal session, and optionally connect those tools to OCI Generative AI:

python 09_deal_tools_demo.py
python 10_run_deal_sessions.py
python 11_oci_genai_chatbot.py  # optional, requires OCI GenAI configuration

Step 1: Verify the ADB-S connection and Deep Sec metadata visibility

Start with the smallest useful checkpoint: connect to Oracle AI Database 26ai using the wallet and confirm that python-oracledb is running in Thin mode.

Create 01_verify_deepsec.py:

import os
import sys
from pathlib import Path

import oracledb
from dotenv import load_dotenv


load_dotenv()


def required_env(name):
    value = os.getenv(name)
    if (
        not value
        or value.startswith("replace-")
        or value.startswith("your-")
        or value.startswith("/path/to/")
    ):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def first_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    raise RuntimeError(f"Set {' or '.join(names)} in .env before running this script.")


def optional_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    return None


def wallet_dsn(wallet_dir):
    tnsnames = Path(wallet_dir) / "tnsnames.ora"
    if not tnsnames.exists():
        raise RuntimeError("Set ADB_DSN or provide a wallet with tnsnames.ora.")
    for line in tnsnames.read_text(encoding="utf-8").splitlines():
        stripped = line.strip()
        if "=" in stripped and not stripped.startswith("#"):
            return stripped.split("=", 1)[0].strip()
    raise RuntimeError("No TNS aliases found in wallet tnsnames.ora.")


def connect():
    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN", "DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=first_env("ADB_USERNAME", "DB_USER"),
        password=first_env("ADB_PASSWORD", "DB_PASSWORD"),
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )


print("DEAL environment check")

with connect() as conn:
    cur = conn.cursor()

    cur.execute("select sys_context('USERENV', 'CURRENT_SCHEMA') from dual")
    print(f"Connected as: {cur.fetchone()[0]}")

    try:
        cur.execute("select banner_full from v$version where rownum = 1")
        version_text = cur.fetchone()[0]
    except oracledb.DatabaseError:
        cur.execute(
            """
            select version_full
            from product_component_version
            where product like 'Oracle Database%'
            fetch first 1 row only
            """
        )
        version_text = cur.fetchone()[0]

    print(f"Database version: {version_text}")

    mode = "Thin" if oracledb.is_thin_mode() else "Thick"
    print(f"python-oracledb mode: {mode}")

    if mode != "Thin":
        print("Deep Data Security requires python-oracledb Thin mode for this demo.")
        sys.exit(1)

    cur.execute(
        """
        select view_name
        from all_views
        where view_name in (
            'DBA_DATA_ROLES',
            'DBA_DATA_ROLE_GRANTS',
            'DBA_DATA_GRANTS',
            'ALL_DATA_GRANTS',
            'USER_DATA_GRANTS'
        )
        order by view_name
        """
    )
    visible_views = [row[0] for row in cur.fetchall()]

    if visible_views:
        print("Deep Sec metadata visible to this schema: " + ", ".join(visible_views))
    else:
        print("Deep Sec metadata views are not visible to this schema.")
        print("Continue only with an administrator-prepared Deep Sec environment.")

Run it:

python 01_verify_deepsec.py

Expected output:

DEAL environment check
Connected as: DEEPSEC
Database version: Oracle AI Database 26ai Enterprise Edition Release 23.26.2.1.0
python-oracledb mode: Thin
Deep Sec metadata visible to this schema: ALL_DATA_GRANTS, USER_DATA_GRANTS

If your schema cannot see Deep Sec metadata views, you may see:

Deep Sec metadata views are not visible to this schema.
Continue only with an administrator-prepared Deep Sec environment.

If this fails:

Confirm ADB_DSN, or let the helper pick an alias from wallet tnsnames.ora.
Confirm ADB_WALLET_LOCATION contains the unzipped wallet files.
Confirm ADB_WALLET_PASSPHRASE is the wallet password, not the database password.
Confirm the database is Oracle AI Database 26ai.
Do not switch to Thick mode.

Step 2: Add a small connection helper

Now that we have seen the full connection code, move the connection boilerplate into a small helper. Do not add authorization logic yet.

Create deal_db.py:

import os
from pathlib import Path

import oracledb
from dotenv import load_dotenv


load_dotenv()


def required_env(name):
    value = os.getenv(name)
    if (
        not value
        or value.startswith("replace-")
        or value.startswith("your-")
        or value.startswith("/path/to/")
    ):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def first_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    raise RuntimeError(f"Set {' or '.join(names)} in .env before running this script.")


def optional_env(*names):
    for name in names:
        value = os.getenv(name)
        if value and not value.startswith("replace-") and not value.startswith("your-"):
            return value
    return None


def wallet_dsn(wallet_dir):
    tnsnames = Path(wallet_dir) / "tnsnames.ora"
    if not tnsnames.exists():
        raise RuntimeError("Set ADB_DSN or provide a wallet with tnsnames.ora.")
    for line in tnsnames.read_text(encoding="utf-8").splitlines():
        stripped = line.strip()
        if "=" in stripped and not stripped.startswith("#"):
            return stripped.split("=", 1)[0].strip()
    raise RuntimeError("No TNS aliases found in wallet tnsnames.ora.")


def connect():
    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN", "DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=first_env("ADB_USERNAME", "DB_USER"),
        password=first_env("ADB_PASSWORD", "DB_PASSWORD"),
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )

There is no command to run for this helper. Later scripts import connect().

Before continuing, you should understand:

deal_db.py centralizes only connection setup.
It does not filter data, redact columns, or set user context.
It does not call oracledb.init_oracle_client().

Step 3: Create loan and policy tables

Create two tables:

loan_applications for structured loan data.
loan_policies for policy documents and demo vectors.

Create 02_create_schema.py:

import oracledb

from deal_db import connect


DDL = [
    """
    create table loan_applications (
        id number primary key,
        customer_name varchar2(100) not null,
        loan_amount number(12,2) not null,
        purpose varchar2(100) not null,
        status varchar2(40) not null,
        officer_notes varchar2(4000),
        customer_ssn varchar2(20),
        customer_income number(12,2),
        customer_credit_score number(4),
        underwriting_decision varchar2(40),
        risk_score number(5,2),
        underwriting_notes varchar2(4000),
        assigned_officer varchar2(40) not null,
        in_underwriting_queue char(1) check (in_underwriting_queue in ('Y','N'))
    )
    """,
    """
    create table loan_policies (
        id number primary key,
        title varchar2(200) not null,
        body varchar2(4000) not null,
        audience varchar2(40) not null,
        embedding vector(3, float32)
    )
    """,
]


def drop_if_exists(cur, table_name):
    try:
        cur.execute(f"drop table {table_name} purge")
    except oracledb.DatabaseError as exc:
        error = exc.args[0]
        if error.code != 942:
            raise


with connect() as conn:
    cur = conn.cursor()

    drop_if_exists(cur, "loan_policies")
    drop_if_exists(cur, "loan_applications")
    print("Dropped existing DEAL demo tables if they existed.")

    for statement in DDL:
        cur.execute(statement)

    print("Created loan_applications.")
    print("Created loan_policies with VECTOR(3, FLOAT32) embedding column.")
    print("Schema setup complete.")

Run it:

python 02_create_schema.py

Expected output:

Dropped existing DEAL demo tables if they existed.
Created loan_applications.
Created loan_policies with VECTOR(3, FLOAT32) embedding column.
Schema setup complete.

Before continuing, you should understand:

The policy corpus is stored in Oracle Database, not in a separate vector store.
The vector column is intentionally small: VECTOR(3, FLOAT32).
Security will be added with Deep Sec data grants, not views or Python filters.

Step 4: Load synthetic data and deterministic demo vectors

Load enough data to make security differences visible.

The row design is:

Linda is assigned applications 101, 102, and 105.
Wendy can see underwriting queue applications 102, 103, 105, and 106.
Policy documents are tagged as general, loan_officer, or underwriter, but the application will not filter on that column.

The three-audience policy model is intentional. A simpler all/underwriter model can work, but general, loan_officer, and underwriter makes the policy proof easier to inspect: both roles can receive general guidance, and each role can also receive role-specific documents without any Python-side audience filtering.

Create 03_load_data.py:

from array import array

from deal_db import connect


loans = [
    (101, "Avery Stone", 320000, "Home purchase", "RECEIVED",
     "Missing final pay stub.", "111-22-3333", 128000, 720,
     "PENDING_REVIEW", 41, "Not reviewed yet.", "LINDA", "N"),
    (102, "Noah Rivers", 485000, "Home purchase", "UNDER_REVIEW",
     "Customer uploaded tax documents.", "222-33-4444", 151000, 695,
     "PENDING_REVIEW", 72, "Watch revolving debt.", "LINDA", "Y"),
    (103, "Maya Chen", 210000, "Refinance", "UNDER_REVIEW",
     "Transferred from branch intake.", "333-44-5555", 98000, 681,
     "PENDING_REVIEW", 68, "Check income variability.", "raj", "Y"),
    (104, "Grace Hill", 150000, "Home equity", "NEEDS_DOCS",
     "Awaiting insurance statement.", "444-55-6666", 87000, 735,
     "NOT_STARTED", 35, "Not in queue.", "amir", "N"),
    (105, "Owen Park", 640000, "Jumbo mortgage", "UNDER_REVIEW",
     "Customer requested expedited review.", "555-66-7777", 220000, 705,
     "PENDING_REVIEW", 77, "Large loan amount.", "LINDA", "Y"),
    (106, "Sofia Reyes", 390000, "Investment property", "ESCALATED",
     "Escalated by branch manager.", "666-77-8888", 132000, 660,
     "PENDING_REVIEW", 83, "Exception review needed.", "raj", "Y"),
]

policies = [
    (1, "General lending eligibility",
     "Baseline eligibility requirements for consumer lending applications.",
     "general", array("f", [0.75, 0.20, 0.05])),
    (2, "Income verification basics",
     "Required documents for income verification and employment review.",
     "general", array("f", [0.65, 0.30, 0.05])),
    (3, "Document retention requirements",
     "Retention periods for lending documents and customer communications.",
     "general", array("f", [0.30, 0.20, 0.50])),
    (4, "Loan officer workflow checklist",
     "Loan officer workflow for intake, status updates, and customer follow-up.",
     "loan_officer", array("f", [0.85, 0.10, 0.05])),
    (5, "Officer notes standards",
     "Standards for writing clear customer-facing officer notes.",
     "loan_officer", array("f", [0.80, 0.05, 0.15])),
    (6, "Credit risk escalation policy",
     "When credit risk signals require underwriting escalation.",
     "underwriter", array("f", [0.05, 0.90, 0.05])),
    (7, "Debt-to-income exception review",
     "Underwriting guidance for debt-to-income ratio exceptions.",
     "underwriter", array("f", [0.10, 0.85, 0.05])),
    (8, "Collateral review guidance",
     "Collateral review requirements for underwriting decisions.",
     "underwriter", array("f", [0.05, 0.75, 0.20])),
]


with connect() as conn:
    cur = conn.cursor()

    cur.executemany(
        """
        insert into loan_applications (
            id, customer_name, loan_amount, purpose, status, officer_notes,
            customer_ssn, customer_income, customer_credit_score,
            underwriting_decision, risk_score, underwriting_notes,
            assigned_officer, in_underwriting_queue
        ) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)
        """,
        loans,
    )

    cur.executemany(
        """
        insert into loan_policies (id, title, body, audience, embedding)
        values (:1, :2, :3, :4, :5)
        """,
        policies,
    )

    conn.commit()

    print(f"Inserted {len(loans)} loan applications.")
    print(f"Inserted {len(policies)} loan policy documents.")
    print("Synthetic data load complete.")

Run it:

python 03_load_data.py

Expected output:

Inserted 6 loan applications.
Inserted 8 loan policy documents.
Synthetic data load complete.

Before continuing, you should understand:

All data is synthetic.
Sensitive columns are populated so column restrictions are visible.
The vectors are deterministic demo vectors, not model-generated embeddings.
The audience column is a database policy anchor, not an application-side filter.

Step 5: Configure Oracle Deep Data Security data roles and data grants

Now create the Deep Sec authorization model.

This step assumes linda and wendy are prepared Deep Sec end users in your environment. The script creates data roles, grants those roles to the end users, and creates data grants on the two tutorial tables.

DEAL access rules: Deep Sec data roles and data grants define which loan rows, restricted columns, and policy documents Linda and Wendy can access.

Create 04_configure_deepsec.py:

from deal_db import connect


with connect() as conn:
    cur = conn.cursor()

    print("Using prepared Deep Sec end users: LINDA, WENDY")

    cur.execute("create data role loan_officer_role")
    print("Created data role: LOAN_OFFICER_ROLE")

    cur.execute("create data role underwriter_role")
    print("Created data role: UNDERWRITER_ROLE")

    cur.execute("grant data role loan_officer_role to linda")
    print("Granted LOAN_OFFICER_ROLE to LINDA.")

    cur.execute("grant data role underwriter_role to wendy")
    print("Granted UNDERWRITER_ROLE to WENDY.")

    grants = [
        """
        create or replace data grant deal_loan_officer_read as
        select (
            all columns except customer_ssn, customer_income,
            customer_credit_score, underwriting_decision,
            risk_score, underwriting_notes
        )
        on loan_applications
        where assigned_officer = ORA_END_USER_CONTEXT.username
        to loan_officer_role
        """,
        """
        create or replace data grant deal_underwriter_read as
        select on loan_applications
        where in_underwriting_queue = 'Y'
        to underwriter_role
        """,
        """
        create or replace data grant deal_policy_general_to_officer as
        select on loan_policies
        where audience = 'general'
        to loan_officer_role
        """,
        """
        create or replace data grant deal_policy_officer as
        select on loan_policies
        where audience = 'loan_officer'
        to loan_officer_role
        """,
        """
        create or replace data grant deal_policy_general_to_underwriter as
        select on loan_policies
        where audience = 'general'
        to underwriter_role
        """,
        """
        create or replace data grant deal_policy_underwriter as
        select on loan_policies
        where audience = 'underwriter'
        to underwriter_role
        """,
    ]

    for grant in grants:
        cur.execute(grant)

    conn.commit()

    print("Created loan application read grants.")
    print("Created loan policy read grants.")
    print("Configured Deep Data Security for DEAL.")

Run it once against a fresh demo schema:

python 04_configure_deepsec.py

Expected output:

Using prepared Deep Sec end users: LINDA, WENDY
Created data role: LOAN_OFFICER_ROLE
Created data role: UNDERWRITER_ROLE
Granted LOAN_OFFICER_ROLE to LINDA.
Granted UNDERWRITER_ROLE to WENDY.
Created loan application read grants.
Created loan policy read grants.
Configured Deep Data Security for DEAL.

If the direct-logon role grants were waiting on the Deep Sec data roles, have the administrator run them now. Then enable mandatory data-grant enforcement on the two demo tables:

GRANT deal_direct_logon_role TO loan_officer_role;
GRANT deal_direct_logon_role TO underwriter_role;

SET USE DATA GRANTS ONLY ON loan_applications ENABLED;
SET USE DATA GRANTS ONLY ON loan_policies ENABLED;

If this fails:

Confirm the tutorial schema has the required Deep Sec privileges.
Confirm linda and wendy exist as Deep Sec end users in your prepared environment.
Confirm your schema owns the demo tables or your administrator has prepared the object-owner model.
Do not replace Deep Data Security with views, triggers, VPD, or Python filters for this tutorial.

Before continuing, you should understand:

Data roles are part of the Deep Data Security authorization model.
Data grants define row and column access in the database.
The loan_policies.audience column is used only inside database policy grants.
Application SQL still remains broad.

Step 6: Set end-user context and prove read and column enforcement

Now add Deep Sec context support to the helper and prove that the same SQL returns different authorized results. In direct_logon mode, the helper connects directly as LINDA or WENDY and qualifies protected objects through the owner schema.

Add this function to the bottom of deal_db.py:

def direct_logon_connect(end_user):
    passwords = {
        "linda": required_env("DEEPSEC_LINDA_KEY"),
        "wendy": required_env("DEEPSEC_WENDY_KEY"),
    }
    if end_user not in passwords:
        raise ValueError(f"Unknown DEAL end user: {end_user}")

    wallet_dir = first_env("ADB_WALLET_LOCATION", "DB_WALLET_DIR")
    dsn = optional_env("ADB_DSN") or optional_env("DB_DSN") or wallet_dsn(wallet_dir)
    return oracledb.connect(
        user=end_user.upper(),
        password=passwords[end_user],
        dsn=dsn,
        config_dir=wallet_dir,
        wallet_location=wallet_dir,
        wallet_password=optional_env(
            "ADB_WALLET_PASSPHRASE", "ADB_WALLET_PASSWORD", "DB_WALLET_PASSWORD"
        ),
    )


def run_for_user(end_user, work):
    if (optional_env("DEEPSEC_CONTEXT_MODE") or "direct_logon") == "direct_logon":
        with direct_logon_connect(end_user) as conn:
            return work(conn)
    raise RuntimeError("This tutorial validates DEEPSEC_CONTEXT_MODE=direct_logon.")


def object_name(name):
    owner = optional_env("DEAL_OBJECT_OWNER") or "DEEPSEC"
    return f"{owner}.{name}"

This helper uses the prepared local-user passwords from .env. The public Python functions still accept friendly names such as "linda" and "wendy", then the helper connects with the unquoted database identifiers LINDA and WENDY. No DEEPSEC_DATABASE_ACCESS_TOKEN is required in direct_logon mode. In a production app, your authentication layer must still establish the real user before choosing which local Deep Sec user or service-user context to use.

Create 05_context_read_demo.py:

from deal_db import object_name, run_for_user


SQL = """
select *
from {loan_applications}
order by id
""".format(loan_applications=object_name("loan_applications"))

FIELDS_TO_SHOW = [
    "customer_ssn",
    "customer_credit_score",
    "underwriting_decision",
    "risk_score",
]


def display_value(value):
    return "NULL" if value is None else value


def rows_for(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(SQL)
        columns = [col[0].lower() for col in cur.description]
        return [dict(zip(columns, row)) for row in cur.fetchall()]

    return run_for_user(end_user, work)


def show(end_user):
    rows = rows_for(end_user)
    ids = [str(row["id"]) for row in rows]

    print(f"\nAs {end_user}:")
    print(f"  Rows returned: {len(rows)}")
    print(f"  Visible application ids: {', '.join(ids)}")

    sample = rows[0] if rows else {}
    print("  Selected sensitive and underwriting fields:")
    for field in FIELDS_TO_SHOW:
        print(f"    {field}: {display_value(sample.get(field))}")


print("Read and column enforcement demo")
show("linda")
show("wendy")

Run it:

python 05_context_read_demo.py

Expected output:

Read and column enforcement demo

As linda:
  Rows returned: 3
  Visible application ids: 101, 102, 105
  Selected sensitive and underwriting fields:
    customer_ssn: NULL
    customer_credit_score: NULL
    underwriting_decision: NULL
    risk_score: NULL

As wendy:
  Rows returned: 4
  Visible application ids: 102, 103, 105, 106
  Selected sensitive and underwriting fields:
    customer_ssn: 222-33-4444
    customer_credit_score: 695
    underwriting_decision: PENDING_REVIEW
    risk_score: 72

The same SQL is used for Linda and Wendy:

SELECT *
FROM loan_applications
ORDER BY id

There is no WHERE assigned_officer = :end_user predicate in the application.

If this fails:

Confirm DEEPSEC_CONTEXT_MODE=direct_logon.
Confirm Linda and Wendy can log on as local Deep Sec end users.
Confirm DEAL_OBJECT_OWNER points to the owner of the demo tables.
Confirm data role grants and data grants exist.

Before continuing, you should understand:

The application does not add a user-specific WHERE clause.
The application does not redact restricted columns.
The database returns the authorized row and column view for the active direct-logon Deep Sec end user.

Step 7: Demonstrate vector distance with manual vectors

Before using vector retrieval as a RAG building block, inspect a simple vector query.

A vector is a list of numbers. In AI applications, embedding models produce vectors that place related concepts near one another. In this tutorial, we use small deterministic vectors so you can see the mechanics directly.

The query vector [0.9, 0.1, 0.0] is closest to loan-officer workflow style documents in our demo data.

Create 07_vector_basics.py:

from array import array

from deal_db import object_name, run_for_user


query_vector = array("f", [0.9, 0.1, 0.0])

sql = """
select title,
       vector_distance(embedding, :query_vector, COSINE) as distance
from {loan_policies}
order by distance
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))

print("Vector warm-up with manual 3-dimensional vectors")
print("Vector warm-up user context: linda")
print(f"Query vector: {list(query_vector)}")

def work(conn):
    cur = conn.cursor()
    cur.execute(sql, query_vector=query_vector)

    print("\nNearest policy vectors:")
    for index, (title, distance) in enumerate(cur, start=1):
        print(f"{index}. {title:<35} distance: {distance:.6f}")


run_for_user("linda", work)

Run it:

python 07_vector_basics.py

Expected output:

Vector warm-up with manual 3-dimensional vectors
Vector warm-up user context: linda
Query vector: [0.8999999761581421, 0.10000000149011612, 0.0]

Nearest policy vectors:
1. Loan officer workflow checklist     distance: 0.001723
2. General lending eligibility         distance: 0.013266
3. Officer notes standards             distance: 0.018206

Your exact distances may vary slightly by database patch level or floating-point formatting; the important result is the role-scoped result set.

A quick metric mental model:

Cosine distance compares vector direction and is common for embedding-style retrieval.
Euclidean distance compares straight-line distance in vector space.
Dot product is often used as a similarity score and is sensitive to vector magnitude.

Production embedding models may normalize vectors or document how they should be compared. Normalization can affect cosine, Euclidean, and dot-product rankings. DEAL uses one explicit VECTOR_DISTANCE() expression so the query shape stays clear.

Oracle AI Vector Search also supports vector indexes for larger corpora, including HNSW-style neighbor graph indexes and IVF/IVFFlat-style partitioned indexes. DEAL does not create an index because this security proof has eight policy rows; when you scale the corpus, validate a CREATE VECTOR INDEX statement for your metric and inspect index metadata such as USER_INDEXES before relying on performance claims.

Before continuing, you should understand:

VECTOR(3, FLOAT32) stores three FLOAT32 values.
Python binds the query vector with array.array("f", [...]).
The primitive vector example comes before any embedding model or chatbot tooling.

Step 8: Run Deep Sec-scoped vector retrieval

Now run a vector query that represents a RAG-style policy search. The search intent is:

unstable cash flow or credit risk

The tutorial maps that intent to a fixed demo vector. In a real AI application, an embedding model such as OCI Generative AI would create the query vector and document vectors at the model’s actual dimension.

Create 08_secure_rag_retrieval.py:

from array import array

from deal_db import object_name, run_for_user


query_text = "unstable cash flow or credit risk"
query_vector = array("f", [0.05, 0.9, 0.05])

sql = """
select id, title, audience,
       vector_distance(embedding, :query_vector, COSINE) as distance
from {loan_policies}
order by distance
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))


def search_as(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(sql, query_vector=query_vector)
        return cur.fetchall()

    return run_for_user(end_user, work)


print("Deep Sec-scoped vector retrieval demo")
print(f"Policy search query: {query_text}")
print("Output below is scoped by the active Deep Sec end user.")

for end_user in ["linda", "wendy"]:
    print(f"\nAs {end_user}:")
    for index, row in enumerate(search_as(end_user), start=1):
        print(f"  {index}. {row[1]:<35} distance: {row[3]:.6f}")

Run it:

python 08_secure_rag_retrieval.py

Expected output:

Deep Sec-scoped vector retrieval demo
Policy search query: unstable cash flow or credit risk
Output below is scoped by the active Deep Sec end user.

As linda:
  1. Income verification basics          distance: 0.529221
  2. Document retention requirements     distance: 0.604677
  3. General lending eligibility         distance: 0.686696

As wendy:
  1. Credit risk escalation policy      distance: 0.000000
  2. Debt-to-income exception review    distance: 0.001895
  3. Collateral review guidance         distance: 0.020925

If this fails:

Confirm Deep Sec grants exist on loan_policies.
Confirm the active end-user context is set.
Confirm the vector query runs against the protected table.
Confirm no application-side audience filter has been added.

Before continuing, you should understand:

The same query vector is used for Linda and Wendy.
The same SQL shape is used for Linda and Wendy.
The SQL does not filter by audience.
The Python code does not filter by audience.
Deep Sec data grants govern which policy rows are available to the vector query.

Optional: Compare keyword and vector-style retrieval

Vector retrieval and keyword retrieval answer different relevance questions. Keyword search finds literal text matches. Vector retrieval can find nearby concepts represented by numbers.

Neither one should become the security boundary. Run both retrieval styles under the same Deep Sec contexts.

Create 08b_keyword_vs_vector_check.py:

from array import array

from deal_db import object_name, run_for_user


query_vector = array("f", [0.05, 0.9, 0.05])

vector_sql = """
select title
from {loan_policies}
order by vector_distance(embedding, :query_vector, COSINE)
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))

keyword_sql = """
select title
from {loan_policies}
where lower(body) like :term
order by id
fetch first 3 rows only
""".format(loan_policies=object_name("loan_policies"))


def run_as(end_user):
    def work(conn):
        cur = conn.cursor()

        cur.execute(vector_sql, query_vector=query_vector)
        vector_titles = [row[0] for row in cur.fetchall()]

        cur.execute(keyword_sql, term="%credit risk%")
        keyword_titles = [row[0] for row in cur.fetchall()]

        return vector_titles, keyword_titles

    return run_for_user(end_user, work)


print("Optional vector-style vs keyword retrieval check")

for end_user in ["linda", "wendy"]:
    vector_titles, keyword_titles = run_as(end_user)
    print(f"\nAs {end_user}:")
    print(f"  Vector-style titles: {', '.join(vector_titles)}")
    print(
        "  Keyword titles for 'credit risk': "
        + (", ".join(keyword_titles) if keyword_titles else "none")
    )

Run it if you want the extra comparison:

python 08b_keyword_vs_vector_check.py

Representative output:

Optional vector-style vs keyword retrieval check

As linda:
  Vector-style titles: Income verification basics, Document retention requirements, General lending eligibility
  Keyword titles for 'credit risk': none

As wendy:
  Vector-style titles: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance
  Keyword titles for 'credit risk': Credit risk escalation policy

Before continuing, you should understand:

Keyword search and vector retrieval are relevance strategies.
Deep Sec policy still scopes the rows available to the query.
The app still does not use an audience security filter.

Try this next: Wrap the proven operations as DEAL tool functions

Only after proving the database operations directly should we wrap them as assistant tools.

Create deal_tools.py:

from array import array

from deal_db import object_name, run_for_user


def _rows_as_dicts(cur):
    columns = [col[0].lower() for col in cur.description]
    return [dict(zip(columns, row)) for row in cur.fetchall()]


def _run_for_user(end_user, work):
    return run_for_user(end_user, work)


def _query_vector(query):
    text = query.lower()
    if "risk" in text or "credit" in text or "cash flow" in text:
        return array("f", [0.05, 0.9, 0.05])
    return array("f", [0.9, 0.1, 0.0])

The _query_vector() function is relevance logic for this deterministic demo. It is not authorization logic.

Append the read tools:

def get_loan_applications(end_user):
    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select *
            from {object_name("loan_applications")}
            order by id
            """
        )
        return _rows_as_dicts(cur)

    return _run_for_user(end_user, work)


def get_application_detail(end_user, app_id):
    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select *
            from {object_name("loan_applications")}
            where id = :id
            """,
            id=app_id,
        )
        rows = _rows_as_dicts(cur)
        return rows[0] if rows else None

    return _run_for_user(end_user, work)

The WHERE id = :id predicate is a business lookup. It is not a user-specific authorization filter.

Append the policy-search tool:

def search_policies(end_user, query):
    query_vector = _query_vector(query)

    def work(conn):
        cur = conn.cursor()
        cur.execute(
            f"""
            select id, title, body, audience,
                   vector_distance(embedding, :query_vector, COSINE) as distance
            from {object_name("loan_policies")}
            order by distance
            fetch first 3 rows only
            """,
            query_vector=query_vector,
        )
        return _rows_as_dicts(cur)

    return _run_for_user(end_user, work)

Create 09_deal_tools_demo.py:

from deal_tools import (
    get_loan_applications,
    search_policies,
)


print("Tool demo: linda")
linda_loans = get_loan_applications("linda")
linda_policies = search_policies("linda", "credit risk")
print(f"  get_loan_applications returned {len(linda_loans)} rows.")
print(f"  search_policies returned {len(linda_policies)} policy documents.")

print("\nTool demo: wendy")
wendy_loans = get_loan_applications("wendy")
wendy_policies = search_policies("wendy", "credit risk")
print(f"  get_loan_applications returned {len(wendy_loans)} rows.")
print(f"  search_policies returned {len(wendy_policies)} policy documents.")

Run it:

python 09_deal_tools_demo.py

Expected output:

Tool demo: linda
  get_loan_applications returned 3 rows.
  search_policies returned 3 policy documents.

Tool demo: wendy
  get_loan_applications returned 4 rows.
  search_policies returned 3 policy documents.

Before continuing, you should understand:

Each tool runs database work under the requested Deep Sec end user.
In direct-logon mode, each user-scoped operation opens its own connection as LINDA or WENDY.
Tools do not filter rows by user.
Tools do not redact restricted columns.
Tools do not filter policy documents by audience.

Try this next: Run DEAL sessions and bypass checks

Now run the final terminal assistant session. This script calls the tool functions and prints a short transcript for Linda, Wendy, and a bypass check.

Create 10_run_deal_sessions.py:

from deal_tools import (
    get_application_detail,
    get_loan_applications,
    search_policies,
)


def display_value(value):
    return "NULL" if value is None else value


def titles(rows):
    return ", ".join(row["title"] for row in rows)


def linda_session():
    print("========================")
    print("DEAL session: linda")
    print("========================")

    loans = get_loan_applications("linda")
    ids = [str(row["id"]) for row in loans]
    detail = get_application_detail("linda", 102)
    policies = search_policies("linda", "credit risk")

    print(f"Visible applications: {len(loans)}")
    print(f"Application ids: {', '.join(ids)}")
    print(f"Restricted risk_score: {display_value(detail.get('risk_score'))}")
    print(f"Policy results: {titles(policies)}")


def wendy_session():
    print("\n========================")
    print("DEAL session: wendy")
    print("========================")

    loans = get_loan_applications("wendy")
    ids = [str(row["id"]) for row in loans]
    detail = get_application_detail("wendy", 102)
    policies = search_policies("wendy", "credit risk")

    print(f"Visible applications: {len(loans)}")
    print(f"Application ids: {', '.join(ids)}")
    print(f"Underwriting risk_score: {display_value(detail.get('risk_score'))}")
    print(f"Policy results: {titles(policies)}")

Append the bypass check and runner:

def bypass_check():
    print("\n========================")
    print("Bypass check")
    print("========================")

    linda_rows = get_loan_applications("linda")
    wendy_rows = get_loan_applications("wendy")
    linda_docs = search_policies("linda", "credit risk")
    wendy_docs = search_policies("wendy", "credit risk")

    print(f"Broad loan query returned {len(linda_rows)} Linda-scoped rows for linda.")
    print(f"Broad loan query returned {len(wendy_rows)} Wendy-scoped rows for wendy.")
    print(f"Linda policy titles: {titles(linda_docs)}")
    print(f"Wendy policy titles: {titles(wendy_docs)}")
    print("No application-side row filter, redaction, or audience filter was used.")


linda_session()
wendy_session()
bypass_check()

Run it:

python 10_run_deal_sessions.py

Expected output:

========================
DEAL session: linda
========================
Visible applications: 3
Application ids: 101, 102, 105
Restricted risk_score: NULL
Policy results: Income verification basics, Document retention requirements, General lending eligibility

========================
DEAL session: wendy
========================
Visible applications: 4
Application ids: 102, 103, 105, 106
Underwriting risk_score: 72
Policy results: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance

========================
Bypass check
========================
Broad loan query returned 3 Linda-scoped rows for linda.
Broad loan query returned 4 Wendy-scoped rows for wendy.
Linda policy titles: Income verification basics, Document retention requirements, General lending eligibility
Wendy policy titles: Credit risk escalation policy, Debt-to-income exception review, Collateral review guidance
No application-side row filter, redaction, or audience filter was used.

Final DEAL session: the same tool functions run under Linda and Wendy’s Deep Sec context, and the database returns user-scoped loan and policy retrieval results.

Before finishing, you should understand the full trust boundary:

DEAL establishes the correct Deep Sec end user for each operation.
The same broad read function returns different rows and restricted values.
The same vector retrieval function returns different policy documents.
The assistant is not trusted to filter or redact protected data.

Optional: Add an OCI GenAI chatbot loop

The terminal session proves the secured tool functions directly. As an optional extension, add a small LLM loop that lets OCI Generative AI choose which DEAL tool to call. The OCI GenAI code path is an integration pattern; the Deep Sec read and vector security proof does not depend on it. Validate the OCI SDK request and response shape in your tenancy before relying on a captured chatbot transcript.

The model is not trusted for authorization; it only selects an action. Every tool call still runs under the chosen Deep Sec end user and lets Oracle Database enforce the policy.

Create 11_oci_genai_chatbot.py:

import json
import os
import re
from pathlib import Path

import oci
from dotenv import load_dotenv
from oci.generative_ai_inference import GenerativeAiInferenceClient
from oci.generative_ai_inference.models import (
    ChatDetails,
    GenericChatRequest,
    Message,
    OnDemandServingMode,
    UserMessage,
)

from deal_tools import (
    get_application_detail,
    get_loan_applications,
    search_policies,
)


load_dotenv()


TOOLS = {
    "get_loan_applications": get_loan_applications,
    "get_application_detail": get_application_detail,
    "search_policies": search_policies,
}


def required_env(name):
    value = os.getenv(name)
    if value:
        value = value.strip().strip('"').strip("'")
    if not value or value.startswith("your-"):
        raise RuntimeError(f"Set {name} in .env before running this script.")
    return value


def oci_client():
    config_file = Path(os.getenv("OCI_CONFIG_FILE", "~/.oci/config")).expanduser()
    profile = os.getenv("OCI_PROFILE", "DEFAULT")
    config = oci.config.from_file(str(config_file), profile)
    return GenerativeAiInferenceClient(
        config=config,
        service_endpoint=required_env("OCI_GENAI_ENDPOINT"),
    )


def message_text(chat_response):
    data = chat_response.data
    if hasattr(data, "chat_response") and hasattr(data.chat_response, "choices"):
        choice = data.chat_response.choices[0]
        return choice.message.content[0].text
    return json.dumps(oci.util.to_dict(data))


def chat_once(client, prompt):
    request = GenericChatRequest(
        api_format=GenericChatRequest.API_FORMAT_GENERIC,
        messages=[
            UserMessage(
                role=Message.ROLE_USER,
                content=[{"type": "TEXT", "text": prompt}],
            )
        ],
        max_tokens=500,
        temperature=0,
    )
    details = ChatDetails(
        compartment_id=required_env("OCI_GENAI_COMPARTMENT_ID"),
        serving_mode=OnDemandServingMode(model_id=required_env("OCI_GENAI_MODEL_ID")),
        chat_request=request,
    )
    return message_text(client.chat(details))


def choose_action(client, end_user, question):
    prompt = f"""
You are DEAL, a lending assistant running as end user {end_user}.
Choose exactly one tool call for the user's request.
Return only JSON with keys tool and arguments.

Available tools:
- get_loan_applications: {{}}
- get_application_detail: {{"app_id": number}}
- search_policies: {{"query": string}}

User request: {question}
"""
    raw = chat_once(client, prompt).strip()
    try:
        return json.loads(raw)
    except json.JSONDecodeError:
        fenced = re.search(r"```

(?:json)?\s*(\{.*?\})\s*

```", raw, re.DOTALL)
        if fenced:
            return json.loads(fenced.group(1))
        start = raw.find("{")
        end = raw.rfind("}")
        if start >= 0 and end > start:
            return json.loads(raw[start : end + 1])
        raise


def run_tool(end_user, action):
    tool_name = action["tool"]
    arguments = action.get("arguments", {})
    if tool_name not in TOOLS:
        raise ValueError(f"Unknown tool requested by model: {tool_name}")
    return TOOLS[tool_name](end_user, **arguments)


def answer_with_result(client, end_user, question, action, result):
    prompt = f"""
You are DEAL, a lending assistant running as end user {end_user}.
The database has already enforced Oracle Deep Data Security.
Answer the user briefly from the tool result only.
If the tool result is a list of loan applications, count the list and list the id values exactly.
If the tool result is a list of policies, list only the returned policy titles.
Do not say that no rows or policies were found unless the tool result is an empty list.

User request: {question}
Tool action: {json.dumps(action)}
Tool result: {json.dumps(result, default=str)}
"""
    return chat_once(client, prompt)


def run_session(client, end_user, questions):
    print(f"\n========================")
    print(f"OCI GenAI DEAL session: {end_user}")
    print(f"========================")
    for question in questions:
        action = choose_action(client, end_user, question)
        result = run_tool(end_user, action)
        answer = answer_with_result(client, end_user, question, action, result)
        print(f"\nUser: {question}")
        print(f"Tool: {action['tool']} {action.get('arguments', {})}")
        print(f"DEAL: {answer}")


client = oci_client()

run_session(
    client,
    "linda",
    [
        "Which loan applications can I see?",
        "Find policy guidance about credit risk.",
    ],
)

run_session(
    client,
    "wendy",
    [
        "Which applications are in my underwriting queue?",
        "Find policy guidance about credit risk.",
    ],
)

Run it after the direct tool demo succeeds:

python 11_oci_genai_chatbot.py

Expected output:

========================
OCI GenAI DEAL session: linda
========================

User: Which loan applications can I see?
Tool: get_loan_applications {}
DEAL: You can see 3 loan applications: 101, 102, 105.

User: Find policy guidance about credit risk.
Tool: search_policies {'query': 'credit risk'}
DEAL: Income verification basics, Document retention requirements, General lending eligibility

========================
OCI GenAI DEAL session: wendy
========================

User: Which applications are in my underwriting queue?
Tool: get_loan_applications {}
DEAL: There are 4 applications in your underwriting queue: 102, 103, 105, 106.

User: Find policy guidance about credit risk.
Tool: search_policies {'query': 'credit risk'}
DEAL: Credit risk escalation policy
Debt-to-income exception review
Collateral review guidance

The important part is the tool transcript, not the wording of the generated answer. If the model asks for a broad loan query, broad application detail, or broad policy search, Oracle Database still scopes the result to the active Deep Sec end user.

Troubleshooting

The connection fails

Check:

ADB_DSN matches your ADB-S wallet alias or connect descriptor, or the wallet contains a usable tnsnames.ora alias.
ADB_WALLET_LOCATION points to the unzipped wallet directory.
The directory includes wallet files used by Thin mode, such as tnsnames.ora and ewallet.pem.
ADB_WALLET_PASSPHRASE is the wallet password, not the database password.

Do not call oracledb.init_oracle_client() for this tutorial.

The driver is in Thick mode

Deep Data Security in this Python tutorial uses python-oracledb Thin mode. Remove any call to:

oracledb.init_oracle_client()

Then rerun 01_verify_deepsec.py.

Linda and Wendy see the same loan rows

Check:

DEEPSEC_CONTEXT_MODE=direct_logon.
DEEPSEC_LINDA_KEY and DEEPSEC_WENDY_KEY contain the local Deep Sec end-user passwords.
DEAL_OBJECT_OWNER points to the schema that owns loan_applications and loan_policies.
Data roles were granted to the correct Deep Sec end users.
Data grants reference the expected Deep Sec username.

Vector inserts or queries fail

Check:

The table uses VECTOR(3, FLOAT32).
Every vector bind uses exactly three values.
Python binds vectors with array.array("f", [...]).
The vector query uses the same metric expression consistently.

You plan to use connection pools

The tutorial uses simple standalone connections so the request boundary is obvious. If you later switch to the service-user context path with a pool, set the Deep Sec context after acquiring a connection and clear it before releasing the connection. Keep this pattern:

conn = pool.acquire()
try:
    context = context_for(end_user)
    conn.set_end_user_security_context(context)
    # Run user-scoped database work.
finally:
    conn.clear_end_user_security_context()
    conn.close()

You plan to replace demo vectors with real embeddings

The core tutorial uses deterministic demo vectors so the security proof is self-contained. When you switch to a real embedding API:

Use the model’s actual vector dimension in the table definition.
Batch embedding requests where the provider supports batching.
Track API cost and rate limits.
Store enough metadata to regenerate embeddings when the model changes.
Keep authorization in Deep Sec data grants, not in embedding metadata filters.

Related Resources

Companion DEAL demo code repository — Clone the runnable Python project used in this tutorial.
Oracle Deep Data Security Guide for Oracle AI Database 26ai — Learn the Deep Data Security concepts, objects, and administration model behind the tutorial.
Create Data Grants in Oracle Deep Data Security — Review how data grants define row, column, and operation access for protected tables.
Configure Data Roles in Oracle Deep Data Security — See how Deep Sec data roles are created and assigned to end users.
python-oracledb Deep Data Security — Use the Python driver APIs for creating, setting, and clearing end-user security context.
Paul Parkinson's Deep Data Security Java article — Compare the same Deep Sec application model in a Java application.
Oracle LiveLabs: Getting Started with Oracle Deep Data Security — Try a guided Deep Data Security workshop.
Oracle AI Vector Search User's Guide — Review Oracle AI Vector Search concepts and SQL reference material.
python-oracledb Vector Data Type — Learn how Python applications bind and fetch Oracle Database VECTOR values.

Conclusion

You built DEAL, a Python loan assistant that demonstrates database-enforced access control for structured SQL, vector retrieval, and LLM-driven tool calls. Linda and Wendy use the same broad tool functions, but Oracle Database applies the active Deep Sec end-user identity before returning loan rows, restricted columns, or policy documents.

That matters because AI applications widen the blast radius of small authorization mistakes. A chatbot can call a broad query, a tool can forget a role predicate, and a prompt can ask for data the user should not receive. With Deep Data Security, the tutorial's security boundary sits in Oracle Database instead of in prompts, redaction branches, or application-side filters.

For production, replace the deterministic demo vectors with embeddings from your chosen model, test the workflow in your own ADB-S environment, and add auditing around high-risk read and retrieval paths. Keep the same rule as you evolve the assistant: the model and application can request data, but the database decides what the end user is allowed to see.

Vector‑native RAG on Oracle: embeddings, HNSW/IVF, and hybrid search under database governance

Mark Nelson — Wed, 27 May 2026 10:38:06 +0000

Key Takeaways

Storing vectors in an Oracle VECTOR column alongside content, metadata, and provenance means retrieval happens inside the database. Existing governance — row-level security, auditing, data masking — applies to vector queries the same way it applies to any other query.
Hybrid retrieval is ordinary SQL: VECTOR_DISTANCE handles semantic similarity and WHERE clauses handle business predicates in the same statement. Any reviewer who can read a query can understand what rows qualified and why.
HNSW and IVF are index strategies with real trade-offs in recall, memory footprint, and query latency. Choosing between them requires measuring both on your own corpus — they are not interchangeable defaults.
The answer step can stay inspectable too. Connecting retrieval to a Select AI profile with SHOWSQL means the SQL generation step is reviewable, not just the retrieval that fed it.

Retrieval‑augmented generation works when you can fetch the right context quickly and explain—in plain SQL—why those rows qualified. Splitting storage between a stand‑alone vector store and the database of record speeds early prototypes but makes governance fragile: metadata is duplicated, filters are reimplemented, and audit trails often end at a gateway you do not control. Oracle’s vector‑native approach keeps the entire RAG path inside the database. Embeddings live in a VECTOR column next to the text, JSON, and provenance you already track; you index them with HNSW or IVF using CREATE VECTOR INDEX; you retrieve with SQL that combines semantic similarity and business predicates. Because retrieval runs on database objects, existing policies and audits—when configured on those objects—stay in the path. When you need an LLM to compose the answer, Select AI with AI Profiles lets you scope what the model can see and inspect generated SQL before execution. The result is a retrieval path that is fast enough for interactive use and reviewable under the same governance you already operate.

Figure 1 — Route → act → trust for in‑database RAG. Retrieval is SQL; governance stays in the path.

If you read Article 5, you saw how AI Profiles keep NL2SQL inspectable. This article focuses on retrieval: how to store vectors, choose HNSW or IVF, and write hybrid queries you can defend in a review meeting.

Prerequisites

Oracle AI Database 26ai or Autonomous Database with AI Vector Search enabled for your RU/service tier.
Privileges to CREATE TABLE and CREATE INDEX in a schema with sufficient quota.
For external embeddings or Select AI, configure credentials and network ACLs per your organization’s policy.
Verify CREATE VECTOR INDEX options and the DBMS_* package signatures in your RU before automating builds.

What “vector‑native” means in Oracle

Vector‑native means embeddings are first‑class database data, not a sidecar. You add a VECTOR(dim, element_type) column to the table that holds your chunked content and provenance; you build an approximate nearest‑neighbor (ANN) index—HNSW for interactive recall or IVF for very large corpora—and you query with SQL. The practical effect is governance continuity: when Virtual Private Database (VPD) policies, Unified Auditing, Data Redaction, and Transparent Data Encryption (TDE) are configured on the relevant objects, they continue to filter, record, mask, and protect your retrieval path. When someone asks why a row entered the context window, you can point to the same table‑scoped policies that govern every other query.

For architecture and security details, see the Oracle AI Vector Search overview and the Database Security Guide for VPD, Unified Auditing, Data Redaction, and TDE:

From document to chunk to embedding

RAG quality depends heavily on two choices you control: where you cut chunks and how you embed them. Oracle provides DBMS_VECTOR_CHAIN utilities for deterministic chunking with tunable size and overlap, so the same document splits the same way as you iterate. For embeddings, you can run an in‑database model (see DBMS_VECTOR support for installed models and ONNX where documented for your RU) or call an external provider with credentials stored in the database and governed by network ACLs. Treat those decisions as versioned preferences you can update deliberately.

Keep the storage shape simple: one row per chunk with the content, the provenance you already carry (source, timestamps, tenant), and a VECTOR column for the embedding. That single‑row design makes hybrid retrieval straightforward because eligibility is a WHERE clause and semantic similarity is an ORDER BY, all in one statement.

Docs:

DBMS_VECTOR (embedding/model support) https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
DBMS_VECTOR_CHAIN (chunking and embedding helpers) https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html

DBMS_HYBRID_VECTOR: keyword + semantic in one call

DBMS_VECTOR_CHAIN and DBMS_VECTOR handle pure vector workflows. When your retrieval also needs full‑text keyword matching—relevance ranking by term frequency, Oracle Text lexer rules, or multi‑language tokenization—DBMS_HYBRID_VECTOR adds that layer. The package provides a JSON‑based SEARCH API that searches by keywords and vectors against hybrid vector indexes. By integrating traditional keyword‑based text search with vector‑based similarity search in a single call, it can improve recall for queries where either approach alone misses relevant results.

Use DBMS_HYBRID_VECTOR when:

Your corpus has mixed-quality writing (exact keywords matter for short queries, semantic similarity matters for paraphrased ones).
You support multiple languages with different stemming rules that Oracle Text already handles.
You want a single managed search API rather than hand-tuned SQL merging of separate text and vector result sets.

For pure vector retrieval with business-predicate filters, the WHERE + VECTOR_DISTANCE pattern in the section below is simpler and sufficient. Reserve DBMS_HYBRID_VECTOR for the cases where keyword recall meaningfully improves results on your corpus—measure both before committing to the more complex path.

Doc:

DBMS_HYBRID_VECTOR https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_hybrid_vector1.html

Choosing an index: HNSW or IVF

HNSW and IVF both deliver sub‑second nearest‑neighbor search, but they trade memory, build time, and recall differently.

HNSW builds an in‑memory navigable small‑world graph. In practice, it often yields high recall at low latency for interactive workloads, at the cost of a memory footprint that grows with corpus size and neighbor degree. Use the documented memory and accuracy advisors in DBMS_VECTOR to size the index pool and validate recall on your corpus before you build. If you are powering a help center, runbooks, or an internal knowledge base where queries arrive piecemeal and you value accuracy, HNSW is a common starting point—measure and tune for your data.

IVF clusters the vector space into partitions and probes only a subset per query. That design can lower memory footprint and sustain good throughput on very large corpora. You trade some recall and tune it by changing how many partitions to probe. If you manage millions of chunks and steady traffic, evaluate IVF operationally using Oracle’s accuracy and performance advisors.

One optimizer detail matters to plans and expectations: the optimizer uses a vector index only when your query’s distance metric matches the index’s metric. If they differ, the index will not be used and an exact search is performed. If your embeddings use cosine similarity (common for text), create the index with DISTANCE COSINE and use the same metric in ORDER BY.

Docs:

CREATE VECTOR INDEX (HNSW/IVF) https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
DBMS_VECTOR advisors and accuracy helpers https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html

Writing hybrid retrieval you can defend

Hybrid search in Oracle is just SQL. Your business predicates—tenant, source system, publish date, access level—are ordinary filters in WHERE, and VECTOR_DISTANCE ranks eligible rows by semantic similarity. Because this happens on a single table with a single row shape, the execution plan can combine a vector index for similarity with conventional indexes for filters. When configured on the relevant objects, VPD policies still apply to the session.

A typical query looks like this:

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
AND    published >= DATE '2025-01-01'
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 5 ROWS ONLY;

Ensure the index’s DISTANCE metric matches the query’s metric; otherwise the vector index may not be used.

Two small details avoid surprises in production. VECTOR_DISTANCE returns smaller values for closer vectors, so you sort ascending and keep the metric explicit to match the index. Also, the query vector’s dimension and element type must match the column’s or the statement will error. In plans, you will often see the vector index satisfy similarity ordering while B‑trees or partitions satisfy filters; the optimizer’s choice depends on predicate selectivity, statistics, and metric/index alignment.

If you prefer operator syntax, the <=> operator is the cosine distance operator in 26ai. Use it only if your target version documents it; otherwise prefer VECTOR_DISTANCE(..., COSINE) explicitly:

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

Docs:

A tiny end‑to‑end demo

This toy example shows the loop from rows to top‑K in a few statements. It runs on Oracle AI Database 26ai or Autonomous Database. Replace the three‑dimensional vectors with your model’s actual dimension (384/768/1536 are common), and use FLOAT32 for typical embeddings. For real data, prefer CLOB when content exceeds 4000 bytes.

1) Create a table that keeps content, provenance, and a vector together:

CREATE TABLE documents (
  id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source     VARCHAR2(64),
  published  DATE,
  content    VARCHAR2(4000), -- use CLOB for larger text in production
  embedding  VECTOR(3, FLOAT32)
);

2) Insert a few rows with deterministic toy vectors so results are obvious:

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-01-15', 'How to reset your password',
   TO_VECTOR('[0.10, 0.05, 0.90]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-02-10', 'How to export monthly invoices',
   TO_VECTOR('[0.80, 0.10, 0.10]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('runbook', DATE '2025-03-05', 'Rotate API keys every 90 days',
   TO_VECTOR('[0.15, 0.85, 0.10]', 3, FLOAT32));
COMMIT;

3) Create a cosine HNSW index and keep the metric explicit:

CREATE VECTOR INDEX docs_hnsw_idx
ON documents (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;

The ORGANIZATION clause names the HNSW structure as documented in the SQL Reference. Verify feature availability and any licensing considerations in your environment, and keep the query metric aligned (COSINE here) for the optimizer to consider this index.

4) Run a top‑K query. VECTOR_DISTANCE returns smaller values for closer matches, so use ascending ORDER BY. Using a literal vector for illustration (in applications, pass vectors as binds rather than literals to avoid hard parsing and ensure type/dimension alignment):

SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 3 ROWS ONLY;

Using a bind (recommended in applications). Ensure :qvec matches the column’s dimension and element type:

-- Suppose :qvec is a VECTOR(3, FLOAT32) bind variable
SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 3 ROWS ONLY;

Operator shorthand (if documented in your RU; <=> is cosine):

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

5) Add a business predicate to show hybrid eligibility in action:

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 2 ROWS ONLY;

If your corpus grows very large, evaluate IVF. Drop the HNSW index first. Keep a single active vector index per column to simplify planning and maintenance.

Use the documented IVF clause (NEIGHBOR PARTITIONS) in your RU’s SQL Reference:

DROP INDEX docs_hnsw_idx;

CREATE VECTOR INDEX docs_ivf_idx
ON documents (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE;

After creating IVF, verify search‑effort controls and probe counts for your RU in the SQL Reference and DBMS_VECTOR docs; use those documented knobs to balance recall and latency.

Orchestrating the answer: in your app or with Select AI

Retrieval yields top‑K rows with provenance; you still need to compose an answer. Many teams keep this step in the application: run the retrieval SQL, log the query and bind values, and pass the returned snippets—plus id, source, and published—to an LLM. Because the context is explicit, reviewers can reconstruct what the model saw and sign off on the path.

If you prefer to orchestrate inside the database, use Select AI with a narrowly scoped AI Profile. During early review, enable SHOWSQL/EXPLAINSQL so a human can inspect generated SQL before execution. Syntax for USING/profile attributes varies by RU; follow the Select AI examples for your environment:

-- In a session with an AI Profile that scopes access
SELECT AI SHOWSQL 'List the top 3 KB articles about password resets from 2025.'
USING 'profile = <your_ai_profile>';

To prevent table data from being sent to a provider during review, use the data‑access controls documented for your RU/service tier (see Select AI examples). Enable the appropriate session/profile setting rather than relying on defaults.

Select AI can include sources in responses when configured; check the SOURCES option and relevant profile attributes for your RU, and keep SHOWSQL/EXPLAINSQL enabled until stakeholders are comfortable with the generated SQL.

Docs:

Select AI examples (SHOWSQL, EXPLAINSQL, object scoping, data access) https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html

Operating in production shape

Demos are short; production is measurement and repeatability. For HNSW, memory depends on graph parameters and corpus size. Use DBMS_VECTOR memory advisors before large builds so you can size the index pool without starving the buffer cache, and measure recall and latency as you adjust graph parameters. For IVF, recall depends on partitioning and probe counts. Use accuracy helpers to compare index settings against exact search on a representative sample, and decide whether a point of recall is worth the latency trade.

Plan hygiene matters. Keep the query metric aligned with the index metric, and gather statistics after bulk loads so the optimizer can choose the intended plan. A minimal stats call looks like this:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'DOCUMENTS');
END;
/

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(USER, 'DOCS_HNSW_IDX');
END;
/

Gather index stats after index builds or rebuilds so the optimizer can cost similarity ordering accurately.

If you evolve index settings or dimensions, make DDL idempotent and, where your release supports it, prefer online rebuilds to avoid unnecessary outages. Article_07 covers schema and index evolution strategies.

Governance continuity does not require special workarounds. When policies are configured on the relevant objects, VPD continues to filter rows, Unified Auditing can capture activity, and Data Redaction can mask columns that should not appear in clear text. As with any sensitive path, set MODULE and ACTION at session start, run your retrieval, and make evidence easy to find in audit views. Consider tagging vector queries with a distinct ACTION (for example, RAG_RETRIEVAL) to simplify Unified Auditing filters.

Docs:

DBMS_VECTOR advisors and accuracy reporting https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html
Security guide landing (VPD, Unified Auditing, Data Redaction, TDE) https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg

Version scope and environment notes

The VECTOR data type, VECTOR_DISTANCE, ANN vector indexes (HNSW/IVF via CREATE VECTOR INDEX), DBMS_VECTOR, DBMS_VECTOR_CHAIN, and the Select AI features shown here require Oracle AI Database 26ai or Autonomous Database. If you are on 19c, you can persist arrays as JSON or BLOB and compute distances in the application, but you do not have native vector columns, vector indexes, or VECTOR_DISTANCE. Always verify your exact RU or service tier for option names, index clauses, and package signatures before you automate builds. If you use external embedding providers, configure credentials and network ACLs before calling them from the database. Ensure the query vector dimension and element type match the column definition; otherwise the statement will error.

Out of scope

This article stays focused on storing vectors, building the two ANN index types, writing hybrid retrieval SQL, and keeping the answer path reviewable. It does not cover Oracle Text, summarization prompts, or application frameworks. For NL2SQL hardening and AI Profile design, see Article 5. For safe schema and index evolution, see Article 7.

Try it next

Load 50–100 real chunks from your docs, store embeddings in a VECTOR column, and build an HNSW index; compare recall and latency before and after switching to IVF on the same corpus.
Turn on SELECT AI SHOWSQL for the answer step so reviewers can sign off on generated SQL before execution.

References

Oracle AI Vector Search overview https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/overview-ai-vector-search.html
VECTOR type, TO_VECTOR, and VECTOR_DISTANCE https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector.html https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/to_vector.html https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/vector_distance.html
CREATE VECTOR INDEX (HNSW/IVF) https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/create-vector-index.html
Querying with similarity and hybrid predicates https://docs.oracle.com/en/database/oracle/oracle-database/26/vecse/query-data-similarity-and-hybrid-searches.html
DBMS_VECTOR and DBMS_VECTOR_CHAIN https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector1.html https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/dbms_vector_chain1.html
Select AI examples (SHOWSQL, EXPLAINSQL, object scoping, data access) https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
Security guide landing (VPD, Unified Auditing, Data Redaction, TDE) https://docs.oracle.com/en/database/oracle/oracle-database/26/dbseg

Safer NL2SQL with Select AI and AI Profiles

Mark Nelson — Mon, 25 May 2026 16:30:20 +0000

Key Takeaways

AI Profiles bind the model to a defined list of database objects. With enforce_object_list enabled, generated SQL cannot reach tables you did not explicitly name — scope is a constraint, not just a suggestion.
SHOWSQL returns the candidate SQL without executing it, using schema metadata rather than table data. You see what the assistant intends before any data moves or any query runs.
EXPLAINSQL surfaces the model's reasoning — the join choices, date windows, and grouping logic — so reviewers can catch wrong assumptions before they become surprises in a result set.
DISABLE_DATA_ACCESS keeps early experiments in a prompt-only lane: metadata-driven inspection continues, but actions that would send table data to the model are blocked until you re-enable them.

Natural-language-to-SQL is fast until it guesses your schema, misreads a join, or touches the wrong table. On Oracle, you don’t have to accept that trade-off. AI Profiles bind provider and model to a defined set of database objects; Select AI adds database-native, inspectable actions so you can review intent and generated SQL before anything runs. In practice, you scope a profile, inspect with SHOWSQL and EXPLAINSQL, capture feedback, and only then execute. You keep the speed of NL2SQL without giving up control.

This article builds on the managed MCP posture from Article 4, where we put a governed action surface in front of assistants. Here, we add an Oracle-native reasoning loop: Select AI’s profiles and actions make the model’s intent reviewable and controllable inside the database.

Version scope and sandbox assumptions

Select AI is available in Oracle Database 26ai and in Autonomous Database (Serverless, Dedicated, and Cloud@Customer). The examples assume Select AI is enabled, a provider credential exists (for example, created with DBMS_CLOUD for external providers), and any required network ACLs are in place. The HR sample schema is used for illustration with read-only access, and table/owner casing must match your environment. You’ll need EXECUTE on DBMS_CLOUD_AI and access to the referenced credential. Profiles are created via DBMS_CLOUD_AI.CREATE_PROFILE and take effect for the current session when set with DBMS_CLOUD_AI.SET_PROFILE. Supported providers and models vary by platform and region; consult the Providers and Models page for your environment.

Prompts that include punctuation or special characters parse more reliably when enclosed in single quotes (for example, SELECT AI SHOWSQL '…';). Some prompts work unquoted; if parsing fails, add quotes. All examples below use quoted prompts.

Action tokens and profile JSON keys can be release-sensitive; confirm exact spellings and supported values in your target release. For current behavior, providers/models, action syntax, conversations, and end-to-end examples, see the Select AI docs:

Overview: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-ai-about.html
Providers and models: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-your-ai-provider-and-llms.html
Examples (profiles, actions, scoping, data-access toggle, feedback): https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/select-ai-examples.html
Conversations and action syntax: https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/select-ai-enable-conversations.html

Why reasoning quality matters for database AI

LLMs know SQL patterns; they don’t know your database. Ask, “How many employees were hired last quarter by department?” and an open-ended model can guess a table name, pick the wrong join key, or hard-code a date window that doesn’t match your calendars. The remedy is twofold: make the model schema-aware, and force an inspection step before anything executes.

AI Profiles handle schema awareness by binding the provider, model, and an object list the model may consider. Review then happens inside the database with SELECT AI SHOWSQL (candidate SQL) and SELECT AI EXPLAINSQL (rationale). That combination—scope plus inspection—turns NL2SQL from a clever demo into a workflow you can standardize. Per docs, for generation and inspection, SHOWSQL and EXPLAINSQL use database metadata (schemas/tables/columns/comments) rather than table contents by default. This keeps them safe defaults during review; consult docs for any action-specific exceptions in your release.

AI Profiles in practice: provider, credential, model, and scope

An AI Profile is a database object created with DBMS_CLOUD_AI.CREATE_PROFILE. It specifies which provider and model to use, which credential authorizes access, and which database objects are in bounds. The object_list does double duty: it gives the model the vocabulary it needs and, when enforced, constrains generation to the allowed objects. Profiles are selected per session with DBMS_CLOUD_AI.SET_PROFILE; after that, SELECT AI actions in the session use the bound provider/model and respect your scoping choices.

The following example enables HR headcount questions without inviting schema drift. The model string is illustrative—choose a provider/model combination supported in your environment and release.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'HR_READONLY',
    attributes   => '{
      "provider": "openai",
      "credential_name": "OPENAI_CRED",
      "model": "gpt-4o-mini",
      "object_list": [
        {"owner":"HR","name":"EMPLOYEES"},
        {"owner":"HR","name":"DEPARTMENTS"}
      ],
      "enforce_object_list": true,
      "case_sensitive_values": true
    }'
  );
END;
/
EXEC DBMS_CLOUD_AI.SET_PROFILE('HR_READONLY');

Notes:

Replace provider, model, and credential with values supported in your release. Supported providers vary by platform and region; check the Providers and Models page.
Verify attribute keys and supported values (for example, case_sensitive_values) against your release; use JSON booleans (true/false), not quoted strings.

A few practical notes help avoid surprises. Profiles are session-scoped, so call DBMS_CLOUD_AI.SET_PROFILE in each session that will use SELECT AI (new SQL Developer tabs, pooled connections, and so on). Creating or using profiles requires appropriate privileges (for example, EXECUTE on DBMS_CLOUD_AI), access to the referenced credential, and any needed network ACLs. The object list can span multiple schemas; execution still honors your roles and any VPD policies. Keeping "enforce_object_list": true curbs schema drift, though the constraint isn’t absolute—if your environment uses synonyms or views, validate with SHOWSQL that generated SQL stays within intended objects. The case_sensitive_values attribute steers how string literal comparisons are produced; if your text data is mixed-case and recall matters more than exact match semantics, consider setting it to false while confirming the exact predicate shape your chosen provider/model generates. For example, with "case_sensitive_values": false a generated comparison might use a case-insensitive form (such as UPPER(e.email) = UPPER(:email)), but exact SQL varies by model and release. Finally, attribute keys such as enforce_object_list, case_sensitive_values, and any automated scoping modes are release-sensitive; verify names and supported values against current docs before relying on them.

Inspectable actions: SHOWSQL first, then EXPLAINSQL if needed

Once a profile is active, you can ask natural-language questions with database-native actions designed for review. SHOWSQL returns a candidate SQL statement without running it. Generation uses metadata—schemas, tables, and columns—rather than table contents.

SELECT AI SHOWSQL 'how many employees were hired last quarter by department?';

EXPLAINSQL summarizes the model’s intent so you can catch wrong assumptions before execution. Like SHOWSQL, it relies on metadata, not table data.

SELECT AI EXPLAINSQL 'how many employees were hired last quarter by department?';

Together, these two actions keep review and execution separate. SHOWSQL gives you something concrete to approve or adjust; EXPLAINSQL tells you why the model chose certain filters or join keys. Use them as your default path during early adoption. A third action, NARRATE, answers in natural language and can incorporate data when enabled; reserve it for contexts where your data-sharing posture allows model access to results and you have documented that decision (docs: Examples page).

Hardening the path: scoping, case handling, and data minimization

Start with guardrails. Enforce the object list so the model stays inside approved tables or views. Decide on case handling up front so string comparisons behave predictably. And be explicit about what data, if any, can leave the database during NL2SQL interactions.

Oracle provides an administrative toggle for data transfer. Calling DBMS_CLOUD_AI.DISABLE_DATA_ACCESS blocks actions that would send table data or results to the model. Metadata-only flows—SHOWSQL and EXPLAINSQL—continue to work when disabled. Data-bearing actions (for example, NARRATE, and in some releases certain chat or result-enriched flows) are blocked until re-enabled. See the Select AI docs for the per-release action matrix.

EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;

-- Still allowed: metadata-driven candidate SQL (no data sent)
SELECT AI SHOWSQL 'total headcount by department?';

-- Expected to raise an error while disabled (data-bearing action)
SELECT AI NARRATE 'total headcount by department?';

EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;

Treat this toggle as a policy switch you can document and audit. When disabled, reviewers can still shape and approve SQL; when enabled, teams can opt into natural-language answers in contexts where that’s appropriate. Roles, VPD, and auditing continue to apply to any approved SQL you run, regardless of whether the SQL was generated by Select AI (docs: Overview and Examples).

Guardrails to standardize in your runbooks: keep enforce_object_list enabled; choose case_sensitive_values deliberately; default to SHOWSQL/EXPLAINSQL during review; and use the data-access toggle to separate prompt-only review from data-bearing actions.

Quick scope test you can try

With the HR_READONLY profile above (only EMPLOYEES and DEPARTMENTS in scope), ask something that tempts the model to reach for HR.LOCATIONS:

-- LOCATIONS is out of scope; we expect generation to stay within allowed objects
SELECT AI SHOWSQL 'list each department with its city and headcount';

Review the candidate. With enforce_object_list on, generation is constrained to the allowed objects. If the candidate includes a disallowed object (for example, HR.LOCATIONS), do not approve it—either change the question or explicitly add the needed object to the profile and regenerate. Scope isn’t a security bypass; it’s a generation boundary that works best when the object list is precise. Synonyms and views can influence behavior, so verify the metadata your profile exposes. When you want to encapsulate joins and filters deliberately, consider scoping to views rather than base tables.

A small, governed NL2SQL demo

Here is a path your team can adopt without changing how you secure or audit the database. First, create and set a narrow profile; the HR_READONLY example above is sufficient for headcount questions, and keeping enforce_object_list enabled prevents drift to unapproved tables. Next, inspect with SHOWSQL and, when the intent needs clarification, EXPLAINSQL. For the HR schema, a typical review might begin with a candidate:

-- Candidate SQL; not executed
SELECT AI SHOWSQL 'list the number of employees hired in the last 90 days by department with department name';

If the candidate looks structurally right but relies on SYSDATE - 90 and you need calendar quarters instead, either edit the date predicate yourself or nudge the prompt and generate another candidate. When you want to see the model’s assumptions—date windows, join keys, grouping logic—ask for a short rationale:

-- Short rationale to help review
SELECT AI EXPLAINSQL 'list the number of employees hired in the last 90 days by department with department name';

After review, execute the approved SQL manually under a read-only role. Generated or not, it is just SQL, so your roles, VPD, and unified auditing remain in force.

Feedback and evaluation loops

You’ll learn faster if you keep lightweight records of what you approved and why. A tiny table works well:

CREATE TABLE ai_sql_feedback (
  id             NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  asked_at       TIMESTAMP DEFAULT SYSTIMESTAMP,
  profile_name   VARCHAR2(128),
  nl_question    CLOB,
  generated_sql  CLOB,
  corrected_sql  CLOB,
  rating         NUMBER, -- 1–5
  notes          CLOB
);

Record your decision after each review round:

INSERT INTO ai_sql_feedback (profile_name, nl_question, generated_sql, corrected_sql, rating, notes)
VALUES (
  'HR_READONLY',
  'list the number of employees hired in the last 90 days by department with department name',
  :paste_candidate_sql_here,
  :paste_corrected_or_approved_sql_here,
  4,
  'Approved after switching SYSDATE-90 to quarter boundaries'
);

Oracle also exposes a built-in feedback hook so you can record positive or negative signals tied to generated SQL. See DBMS_CLOUD_AI.FEEDBACK in the Select AI docs for the current procedure signature; confirm parameter names such as sql_text versus sql_id and accepted feedback types/ratings for your release (docs: Examples).

-- Example only; verify parameter names/values for your release (e.g., sql_text vs sql_id)
BEGIN
  DBMS_CLOUD_AI.FEEDBACK(
    feedback_type => 'SQL_QUALITY',             -- verify accepted values
    rating        => 4,                         -- verify rating scale
    sql_text      => :paste_candidate_sql_here, -- or use sql_id => '...' if supported
    notes         => 'Good structure; adjusted date window to fiscal quarter'
  );
END;
/

Keep the loop simple: store the question, what Select AI proposed, what you ran, and a short note. In reviews, patterns emerge quickly—repeated date-window mistakes, common join fixes, or object_list gaps you can close in profiles. If you depend on case-insensitive matching, pilot with a small test set that validates generated predicates against your data.

What changes when you do this under managed MCP

In Article 4 we hosted an MCP server on Autonomous Database to make the database the control plane for actions. Select AI fits neatly into that posture. An agent can plan and call tools inside the database, but the way it generates and explains SQL remains inspectable and scoped by the active profile. Your identity, roles, private endpoints, VPD, and auditing don’t get bypassed; they’re the runway. This is series context, not a formal product integration; Select AI behavior and governance follow the documented database APIs. The operational benefit is simple: teams can standardize on "SHOWSQL first," even when an assistant is in the loop, and your audit trail still shows a normal query execution under the right account, module, and action once you approve the run.

Common failure modes, and how the Select AI pattern avoids them

Open-ended NL2SQL tends to fail the same ways: hallucinated table names, ambiguous joins, and fragile filters. An enforced object_list removes most schema drift by design. SHOWSQL gives reviewers something concrete to approve or fix, and EXPLAINSQL surfaces incorrect assumptions before they become surprises in production. When data sharing is sensitive or still under review, DISABLE_DATA_ACCESS keeps experiments in the prompt-only lane. None of this is exotic. It is a repeatable workflow you can hand to a junior developer—or an assistant—and expect the same outcome: first we scope; then we inspect; then we run; and we prove it in logs and policy after the fact.

What comes next

This same discipline carries into retrieval-augmented generation. In Article 6, we will combine Select AI with Oracle AI Vector Search so answers are grounded in embeddings stored next to the business data itself. You’ll use the same pattern: bind provider and model with a profile, scope what’s visible, inspect what will run, and keep governance in the path.

A tiny sequence to keep on a team wall

Closing

NL2SQL doesn’t have to be a black box. On Oracle, you bind the model and the data surface with AI Profiles, inspect what the model intends with SHOWSQL and EXPLAINSQL, and execute only when you’re satisfied—under the same roles, VPD, and auditing you already trust. Start narrow. Keep enforce_object_list on. Use the data-access toggle when you’re in prompt-only mode. Log decisions. The result is faster iteration with guardrails you can explain—and prove.

Managed MCP in Autonomous AI Database: remote, governed tools per database

Mark Nelson — Thu, 21 May 2026 10:32:46 +0000

This is article 4 of 8 in my Oracle Database Skills series.

Key Takeaways

Managed MCP moves the action surface into the database itself. Tools run under real database identities with existing network controls, VPD policies, and audit trails already in force — no separate trust stack to build.
Custom tools are database objects. You define the logic in PL/SQL and register it with DBMS_CLOUD_AI_AGENT.CREATE_TOOL, so governance travels with the tool definition rather than depending on each caller to do the right thing.
The five questions security teams ask — who acted, under which identity, from what network path, against which tool, and where is the audit record — are answerable by design when the endpoint is managed by the database.

Most teams begin where Article 3 ended: a single developer running a local MCP (Model Context Protocol) server to prove out tools and workflows. That’s perfect for a solo experiment and brittle the moment work becomes shared. As soon as you put an IDE on a shared sandbox or add a second teammate, you need identity that isn’t “whoever is at the laptop,” network boundaries that aren’t home Wi‑Fi, and an audit trail your security team can actually review. Autonomous AI Database’s managed MCP server gives you those primitives without rebuilding your trust stack. Each database exposes its own HTTP MCP endpoint. Clients authenticate as a database user. Tools are defined and governed inside the database. Existing controls—roles, Access Control Lists (ACLs) or Private Endpoint reachability, Virtual Private Database (VPD)/redaction, and Unified Auditing—stay in the path for every tool call.

If you liked the local model—small, named tools; least‑privilege credentials; proof after the fact—you keep it. What changes is location and blast radius. The server runs with the database service. Calls execute with a database user’s roles. Network policy and auditing you already rely on continue to apply over streamable HTTP.

Sources: consult the Autonomous Database MCP documentation for concepts, enablement, security, and troubleshooting:

Version scope and assumptions

This article focuses on Autonomous Database Serverless (Autonomous AI Database) with its managed, per‑database HTTP MCP endpoint and on custom tool registration with DBMS_CLOUD_AI_AGENT. It does not cover on‑premises databases, self‑hosted proxies, or alternative deployment topologies. For authentication, follow the documented OAuth and short‑lived bearer token flows supported at publish time. The examples assume you can modify an Autonomous AI Database in Oracle Cloud Infrastructure (OCI) and create PL/SQL in your schema. Private Endpoint deployments require clients to run within, or be routed into, the VCN. Availability is documented as exclusive to Oracle Autonomous AI Database (versions 26ai and 19c) on the “About MCP Server” page; verify your environment against current docs before starting.

What you’ll need (prerequisites)

An Autonomous AI Database (Serverless) you can modify in OCI.
Permission in OCI to update free‑form tags on that database (enablement uses a tag).
A database user to authenticate to MCP (least‑privilege strongly recommended).
Database privileges to create/compile PL/SQL in your schema and to execute DBMS_CLOUD_AI_AGENT (DBA can grant EXECUTE on the package).
Network posture set appropriately:
- Public endpoint constrained by ACLs to known IPs/CIDRs, or
- Private Endpoint with clients running inside or routed/peered into the VCN.
An MCP‑aware client that supports streamable HTTP (e.g., Claude Desktop via mcp-remote; VS Code with Cline). Ensure the client’s transport is set to streamable HTTP per its configuration schema.
To view audit trails, privileges such as AUDIT_VIEWER (or equivalent access to UNIFIED_AUDIT_TRAIL) and any required unified audit policies per your security team.

Why move from local to managed

Local MCP gets you speed but leaves basic enterprise questions unanswered: Who ran this? Under which roles? From where? What policy filtered the result? Where’s the audit record? A managed MCP endpoint answers them by design. Identity is a database user, so tools run with that user’s roles and any VPD or redaction policies. Network is first‑class: public endpoints honor ACLs; Private Endpoint confines reachability to your VCN. Evidence lives with the data in Unified Auditing, and—if your organization uses it—Oracle Data Safe can centralize review. You don’t assemble a separate trust stack for assistants; you reuse the one already protecting your data.

Enable the per‑database MCP endpoint

Enabling MCP is an OCI operation. You turn it on per database with a free‑form tag. After a brief delay, the database details page shows the MCP URL. Use that exact URL; Private Endpoint deployments will reflect your VCN in the hostname. The documented public endpoint pattern is:

https://dataaccess.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}

For Private Endpoint databases, the documented pattern uses the database Private Endpoint hostname prefix:

https://{hostname_prefix}.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}

In the OCI Console, open your Autonomous AI Database (Serverless) and add this free‑form tag:

Name: adb$feature
Value: {"name":"mcp_server","enable":true}

Tighten network posture before you connect. For public endpoints, restrict inbound IPs with an ACL. For Private Endpoint, keep the MCP URL accessible only within your VCN; clients must run in or have routed/peered reachability to that network. The enablement and security pages in the docs provide step‑by‑step screens for both configurations.

Authenticate to the managed MCP endpoint

The managed server accepts OAuth or short‑lived bearer tokens as documented. Tokens are issued per database and presented on every request as a bearer header over streamable HTTP.

Token endpoint path (documented): /adb/auth/v1/databases/{database-ocid}/token
Present the token in client requests as: Authorization: Bearer <token>
Tokens are typically valid for approximately one hour; confirm the current duration and request body in the Use MCP Server page before automating refresh.

Client configuration varies slightly:

Claude Desktop via mcp-remote: configure a remote MCP server using your database’s MCP URL and add the Authorization: Bearer ... header in the bridge’s HTTP headers field. Set the transport to streamable HTTP as required by your client (for example, a setting named streamable-http or streamableHttp).
VS Code with Cline: add a remote HTTP MCP server pointing to the MCP URL and set Authorization: Bearer ... in the request headers section. Ensure the transport is explicitly set to streamable HTTP.

A minimal Cline-style configuration looks like this, with placeholders for your database URL and bearer token:

{
  "mcpServers": {
    "adb-managed-mcp": {
      "type": "streamableHttp",
      "url": "https://dataaccess.adb.{region-identifier}.oraclecloudapps.com/adb/mcp/v1/databases/{database-ocid}",
      "headers": {
        "Authorization": "Bearer <token>"
      }
    }
  }
}

If your database uses a Private Endpoint, make sure the client host has network reachability into the VCN. Valid credentials against an unreachable hostname still fail.

Publish a custom tool the database can govern

Unlike local MCP servers that host code beside the client, the managed server looks up tools defined in the database. You create small, purposeful functions in your schema and register them with DBMS_CLOUD_AI_AGENT.CREATE_TOOL. That keeps control centralized: the definitions live in the database, metadata is queryable from views, and execution inherits database roles and policy.

To keep the example easy to reason about, we’ll publish a read‑only tool that returns a paginated JSON array of objects in a given schema that are visible to the executing database user and are not in Oracle‑maintained schemas. This variant excludes Oracle‑maintained schemas using ALL_USERS.ORACLE_MAINTAINED. The Oracle docs also include a sample that filters on ALL_OBJECTS.ORACLE_MAINTAINED = 'N'; either approach works on Autonomous Database.

First, create and compile the function:

CREATE OR REPLACE FUNCTION list_objects_json (
  p_schema IN VARCHAR2,
  p_offset IN NUMBER DEFAULT 0,
  p_limit  IN NUMBER DEFAULT 20
) RETURN CLOB
AS
  v_json  CLOB;
BEGIN
  SELECT COALESCE(
           JSON_ARRAYAGG(
             JSON_OBJECT(
               'SCHEMA_NAME' VALUE owner,
               'OBJECT_NAME' VALUE object_name,
               'OBJECT_TYPE' VALUE object_type
             ) RETURNING CLOB
           ),
           '[]'
         )
    INTO v_json
    FROM (
      SELECT o.owner, o.object_name, o.object_type
      FROM   all_objects o
      WHERE  o.owner = UPPER(p_schema)
      AND    EXISTS (
               SELECT 1
               FROM   all_users u
               WHERE  u.username = o.owner
               AND    u.oracle_maintained = 'N'
             )
      ORDER  BY o.object_name
      OFFSET p_offset ROWS FETCH NEXT p_limit ROWS ONLY
    );
  RETURN v_json;
END;
/

Check for compilation errors before registering a tool:

SHOW ERRORS FUNCTION list_objects_json;

-- Or:
SELECT name, type, line, position, text
FROM   user_errors
ORDER  BY name, sequence;

Next, register the function as an MCP tool. Use the attribute names exactly as shown in the documentation for DBMS_CLOUD_AI_AGENT.CREATE_TOOL.attributes; mismatches can cause registration or invocation errors. Common keys include instruction, function, and tool_inputs. For this example, rely on the PL/SQL defaults defined in the function and do not include unverified default values in the tool_inputs JSON.

BEGIN
  DBMS_CLOUD_AI_AGENT.CREATE_TOOL(
    tool_name  => 'LIST_OBJECTS',
    attributes => q'~{
      "instruction": "Return a JSON array of objects visible to the executing database user for the given schema (excluding Oracle-maintained schemas). Always respect p_limit and p_offset.",
      "function": "LIST_OBJECTS_JSON",
      "tool_inputs": [
        { "name": "p_schema", "type": "string", "required": true,  "description": "Schema name (case-insensitive)" },
        { "name": "p_offset", "type": "number", "required": false, "description": "Row offset for pagination" },
        { "name": "p_limit",  "type": "number", "required": false, "description": "Max rows to return" }
      ]
    }~'
  );
END;
/

Verify that registration succeeded by querying the registry view. Column sets differ by version, so keep the check simple:

SELECT tool_name
FROM   user_cloud_ai_agent_tools
ORDER  BY tool_name;

If you see “insufficient privileges,” ask your DBA to grant EXECUTE on DBMS_CLOUD_AI_AGENT to your user and retry.

Note: DBMS_CLOUD_AI_AGENT supports both custom PL/SQL‑backed tools and built‑in tool types. For the current list of built‑in types and parameters, see the package reference:

https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/dbms-cloud-ai-agent-package.html

Also note: MLE JavaScript is not supported for MCP tools. Java can be used via PL/SQL calling Java stored procedures when JAVAVM is enabled on the database (see the docs for details).

Connect a client and try the tool

Once authentication works and the tool is registered, connect an MCP‑aware client and ask it to use the tool. In Claude Desktop, add a remote MCP server that points to your database’s MCP URL, include the Authorization: Bearer <token> header, and set transport to streamable HTTP per the client’s schema. In a fresh chat, prompt the assistant: “Use the LIST_OBJECTS tool to list 5 objects in the HR schema.” You should see the assistant invoke the tool and stream back JSON. In VS Code with Cline, add the same MCP URL in the extension’s settings, supply the same header, and ensure the streamable HTTP transport is selected; then request LIST_OBJECTS with p_schema=HR and p_limit=5.

A representative response from the tool looks like this (truncated):

[
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "COUNTRIES", "OBJECT_TYPE": "TABLE" },
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "EMPLOYEES", "OBJECT_TYPE": "TABLE" },
  { "SCHEMA_NAME": "HR", "OBJECT_NAME": "JOBS", "OBJECT_TYPE": "TABLE" }
]

On Private Endpoint databases, run those clients from a host inside the VCN or with routed/peered access. Lack of reachability is the most common reason a seemingly correct configuration fails.

Observe the audit trail (roles permitting)

Unified Auditing on Autonomous Database records activity under your database user. Exact fields and event names depend on your version and your organization’s audit policy. With appropriate privileges (for example, AUDIT_VIEWER or equivalent access to UNIFIED_AUDIT_TRAIL), you can review a time‑bounded window of recent activity:

SELECT event_timestamp, dbusername, action_name, return_code, obj_privilege, unified_audit_policies
FROM   unified_audit_trail
WHERE  event_timestamp >= SYSTIMESTAMP - INTERVAL '15' MINUTE
AND    UPPER(dbusername) = UPPER(USER)
ORDER  BY event_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Autonomous Database retains unified audit data for about 14 days by default; if you need longer retention or centralized reporting, integrate Oracle Data Safe. When you author VPD or audit policies for MCP, do not rely on SESSION_USER. Use MCP context attributes instead—for example, sys_context('MCP_SERVER_ACCESS_CONTEXT','USER_IDENTITY') as shown in the Security docs. Some concept pages may refer to the MCP context container using slightly different naming in prose; follow the exact string in the Security examples. Your security team may also enable additional unified audit policies to capture MCP‑specific attributes.

Operate safely and clean up

Treat tools as product surface area. Keep them small and read‑heavy at first; add write paths only when you can articulate least privilege and validate inputs. Prefer schemas you own to avoid accidental privilege sprawl. Version tool registrations as code and require review before deployment.

When a tool is no longer needed, remove both the registration and the underlying function:

BEGIN
  DBMS_CLOUD_AI_AGENT.DROP_TOOL(tool_name => 'LIST_OBJECTS');
END;
/

DROP FUNCTION list_objects_json;

As you expand usage, periodically re‑check ACLs on public endpoints and confirm Private Endpoint posture remains correct, especially after network changes. Rate limiting is enforced by the managed service; consult current release notes for behavior and limits.

Troubleshooting

Most issues fall into a few buckets:

Compilation and registration: fix PL/SQL errors (SHOW ERRORS, USER_ERRORS) before registering. Ensure your user has EXECUTE on DBMS_CLOUD_AI_AGENT. If registry queries fail with unknown columns, select only tool_name and consult versioned docs for metadata.
Connectivity: verify you’re using the MCP URL exactly as shown in the console. For public endpoints, confirm your IP/CIDR is in the database ACL. For Private Endpoint, confirm the client host has routed/peered access to the VCN.
Authentication: obtain tokens from /adb/auth/v1/databases/{database-ocid}/token and present them as Authorization: Bearer <token>. Copy the exact request body and lifetime from the current docs. Place headers where your client expects them.

If a client claims to connect but tool calls fail, double-check that attribute keys in CREATE_TOOL match the documentation exactly; case mismatches cause registration or invocation errors.

What comes next

With a per-database MCP endpoint, one governed tool, and a way to verify activity in Unified Auditing, you can expand safely. Start with a small set of read-heavy tools your team actually needs. Introduce narrow write paths with explicit inputs and clear privileges. Treat tool definitions like code: review them, version them, and track changes.

The next article moves from action surfaces to reasoning scope: Select AI and AI Profiles. That is where you decide what natural-language-to-SQL should be allowed to see, how generated SQL should be inspected, and how to keep an assistant’s query generation inside a deliberate profile instead of a broad database connection.

Freshness note

Content validated against the Autonomous Database MCP documentation on 2026-05-08. Re-check the Use MCP Server and Security pages for any changes to endpoint paths, JSON key names, token lifetimes, and audit details before publishing or automating.

Controlled Oracle Actions with SQLcl MCP

Mark Nelson — Tue, 19 May 2026 12:07:51 +0000

This is article 3 of 8 in my Oracle Database Skills series.

Kay Takeaways

The assistant connects using a saved connection name, not a pasted password. Credentials stay in the SQLcl store and out of prompts, so the model sees the name but never the secret.
Restrict levels define the tool surface at runtime. Starting at the most restrictive setting limits what the assistant can do; you widen the surface deliberately as the use case earns it.
Session tagging and the MCP activity log give a DBA verifiable proof of what ran. Evidence comes from the database, not the chat transcript — that distinction matters when someone asks what the assistant actually did.

In the last piece, you routed Oracle prompts through db/SKILL.md to keep intent explicit (see Article 1 and Article 2 in this series). Now it’s time to act—locally, with guardrails, and with an evidence trail your DBA can verify. SQLcl MCP gives you that posture. You launch it as a local process (sql -mcp); it exposes a small catalog of named tools over stdio; it connects through saved connections you control; it starts in a highly restricted mode by default; and it leaves database‑native traces you can query later. That’s the right shape for early assistant experiments on a developer machine.

Citations for versioned behavior are in Oracle’s SQLcl documentation (for example, “Using SQLcl MCP Server,” “SQLcl MCP Server tools,” “Starting and managing SQLcl MCP Server,” “Restrict Levels,” “Preparing your environment,” and “Saving connections” in the 25.4 and 26.1 editions). Treat any version‑sensitive claim here as “verify against your installed docs.”

What you need before you start

SQLcl 25.2.0 or newer with MCP support
Java 17 or 21 available to SQLcl (per “Preparing your environment”)
A non‑production Oracle Database (19c+ suffices for this demo)
A least‑privilege, read‑only database user for testing
A saved SQLcl connection that includes a stored password, created with -save and -savepwd (required for non‑interactive MCP connects)
Permission to query V$SESSION for evidence, or access to an equivalent DBA‑provided view that exposes MODULE and ACTION for your sessions
If you use TNS aliases, configure Oracle Net as usual (for example, set TNS_ADMIN to your tnsnames.ora directory). If your MCP client launches sql -mcp with a custom environment, pass TNS_ADMIN there as needed.

Security note: Storing passwords is a prerequisite for non‑interactive MCP connects. Keep this to a sandbox, use a read‑only account, and lock down your local SQLcl store (commonly under ~/.dbtools) per your organization’s secrets policy.

Why SQLcl MCP is the right local action layer

A tempting shortcut is to paste credentials into a prompt and let a model “try something.” That’s exactly what you want to avoid. SQLcl MCP flips the pattern: you launch sql -mcp locally and expose a small set of structured tools. Your client can list saved connections, connect by name, run SQL, and disconnect. The MCP connect path uses saved/named connections; the tool does not accept raw credentials in the call. For non‑interactive use, the password must be saved (-savepwd). You decide which saved connections exist and what privileges they carry.

Restrict levels are SQLcl’s runtime safety gates that limit which features and commands are allowed. In MCP mode the server defaults to restrict level 4 (most restrictive) unless you override it with -R, so the surface stays small until you intentionally broaden it. That’s a posture you can explain to a DBA and adjust deliberately as you earn trust.

References: SQLcl MCP overview and startup; Saved connections; Restrict levels (25.4/26.1 docs).

How the server behaves under the hood

When your client launches sql -mcp, SQLcl starts a small server over stdio and advertises a catalog of named tools. Message envelopes vary by client, but tool names and arguments follow the SQLcl MCP documentation. As of the 26.1 docs, commonly listed tools include:

list-connections
connect
disconnect
run-sql
run-sqlcl

Some builds also include schema-information. Tool availability and argument shapes can vary by version/build; verify against your installed docs.

Connections are discovered from your local SQLcl store (for example, ~/.dbtools). To make a connection usable without a prompt, save it with a stored password. Oracle documents this as a prerequisite for hands‑off connection by name.

Restrict levels shape what the process can do. In MCP mode, the server defaults to restrict level 4 (most restrictive) unless you override it with -R. Outside MCP, generic SQLcl defaults to restrict level 0 (unrestricted) unless you pass -R; always verify the “Restrict Levels” page for your exact build.

References: SQLcl MCP tools; Saved connections; Restrict levels and -R flag (25.4/26.1 docs).

Traceability you can prove (monitoring and governance)

Before you run anything, plan how you’ll show what happened. The most portable approach is to tag your session with DBMS_APPLICATION_INFO and then confirm it in V$SESSION. The tag appears in performance tooling and helps a DBA correlate what ran and why. By default, SQLcl MCP records the execution history of requests in DBTOOLS$MCP_LOG (per Oracle docs). Access, owner/schema, and privileges vary by environment, so confirm presence and permissions with your DBA.

Tagging mechanism: DBMS_APPLICATION_INFO.SET_MODULE and SET_ACTION (documented in Oracle Database PL/SQL packages)
Evidence view: V$SESSION (privilege‑gated; ask a DBA for a scoped alternative if you lack access)
Logs: DBTOOLS$MCP_LOG records requests by default; access and retention are environment‑specific

References: DBMS_APPLICATION_INFO; V$SESSION; SQLcl MCP monitoring/logging pages (25.4/26.1 docs).

Version scope and environment notes

Keep SQLcl at 25.2.0+ for MCP features and run it with Java 17 or 21 (per “Preparing your environment”).
The tool catalog and defaults can evolve; the 25.4 and 26.1 guides capture current behavior, but verify against your installed version.
The demo assumes Oracle Database 19c or later and avoids database features tied to 26ai.
Some clients (for example, SQL Developer for VS Code) can launch and manage the MCP server for you; follow your client’s documentation for its configuration format and capabilities, which can change by version.

A small, traceable demo you can run today

This walkthrough saves a single read‑only connection, launches the MCP server, validates that the connection is discoverable, connects by name, tags its session, runs one read‑only discovery query, and disconnects. Each step includes copy‑paste commands and why the step matters.

1) Save a named connection for a read‑only sandbox user

Open SQLcl, connect once interactively, and persist the connection with a stored password so MCP can use it without a prompt.

sql /nolog
# If you connect by service name:
conn demo_ro@//host:1521/service -save demo_ro -savepwd
# If you connect by TNS alias (ensure Oracle Net is configured and tnsnames.ora is discoverable):
# conn demo_ro@ALIAS -save demo_ro -savepwd

When prompted, enter the password interactively. The -save and -savepwd flags persist the named connection (commonly in ~/.dbtools) with a stored password so MCP can use it non‑interactively. Use a sandbox‑only, least‑privilege account and follow your org’s secrets policy for local credential storage.

2) Start the MCP server (keep the default restrict level)

Launch SQLcl in MCP mode and leave the terminal open. Unless your client does this for you, this process is the local server your MCP‑aware client will talk to.

sql -mcp

By default, restrict level 4 applies. If you ever see broader capability than expected, confirm that the process was not started with a lower restrict level (for example, -R 0). For early experiments, stick with the default level 4.

3) Point your MCP‑aware client at SQLcl

Configure your client to launch sql -mcp.

Representative MCP server configuration for a desktop client. Field names and file locations vary by client; follow your client’s documentation for the exact schema.

{
  "mcpServers": {
    "sqlcl": {
      "command": "sql",
      "args": ["-mcp"],
      "env": {
        "TNS_ADMIN": "/path/to/network/admin"
      }
    }
  }
}

4) Verify that the saved connection is discoverable

Before you connect, ask the server to list saved connections. Seeing demo_ro here confirms that the server can use it non‑interactively.

Representative MCP tool call; adapt to your client’s message envelope (for example, a surrounding "type": "call_tool"), server name, and argument key names as defined by your client and the SQLcl MCP tools page for your installed version.

[
  { "tool": "list-connections", "arguments": {} }
]

If demo_ro does not appear, revisit the conn ... -save -savepwd step and make sure you created the connection under the same OS user/profile that SQLcl is using.

5) Connect by name and tag the session

Connect using the saved name, then tag the session with MODULE and ACTION so you can find it later in performance views and, if accessible, MCP logs.

Representative MCP tool call; adapt to your client’s envelope and argument names. Note: some clients use name, others connectionName or connection_name.

[
  { "tool": "connect", "arguments": { "name": "demo_ro" } },
  { "tool": "run-sql", "arguments": { "sql": "BEGIN DBMS_APPLICATION_INFO.SET_MODULE('sqlcl-mcp','schema-discovery'); END;" } }
]

Tagging is optional but strongly recommended. It creates a human‑readable breadcrumb for exactly this experiment.

6) Run one read‑only discovery query

To keep the example universal, start with a query against objects you own:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sql", "arguments": { "sql": "SELECT table_name FROM user_tables ORDER BY table_name FETCH FIRST 5 ROWS ONLY" } }

If your account has cross‑schema visibility, you can adjust the query to ALL_TABLES:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sql", "arguments": { "sql": "SELECT owner, table_name FROM all_tables WHERE owner = UPPER('<YOUR_SCHEMA>') ORDER BY table_name FETCH FIRST 5 ROWS ONLY" } }

Keep the action read‑only while you learn the surface and review the evidence with a DBA partner.

Optional: Some clients also expose run-sqlcl for SQLcl console commands (for example, DESC). At restrict level 4, many SQLcl commands are disabled; simple describe operations may work depending on your build—verify in your environment. If enabled in your version and posture, a representative call looks like:

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "run-sqlcl", "arguments": { "command": "DESC USER_TABLES" } }

Treat this as optional; the minimal surface for early tests is run-sql.

7) Disconnect when you are done

Disconnecting is good hygiene and makes your test’s boundaries clear.

Representative MCP tool call; adapt to your client’s envelope and argument names.

{ "tool": "disconnect", "arguments": {} }

Prove what happened

Evidence checks depend on privileges. If your account cannot query performance views, ask your DBA for a scoped alternative that exposes MODULE and ACTION for your sessions.

See the tag in a live session view:

SELECT module, action, username, machine
FROM   v$session
WHERE  module = 'sqlcl-mcp';

By default, SQLcl MCP records requests in DBTOOLS$MCP_LOG. You can review recent entries there as well. Presence is standard per the docs, but owner/schema and access vary by setup; in some environments the table resides under a DBA‑owned schema. Treat the query below as an example and ask your DBA for the owner or a scoped view if needed.

SELECT id, mcp_client, model, end_point_type, end_point_name, log_message
FROM   DBTOOLS$MCP_LOG
ORDER  BY id DESC
FETCH  FIRST 10 ROWS ONLY;

If neither view is available to you directly, a DBA can run the checks or provide a narrow view limited to your test user.

When something goes wrong

If the client prompts for a password, the saved connection likely lacks a stored secret. Recreate it with both -save and -savepwd so MCP can connect non‑interactively. When the tool catalog looks unfamiliar or is missing entries, you may be on a different SQLcl version; check the MCP tools page for your installed build (the 25.4 and 26.1 docs capture current behavior). If you don’t see your session tag in V$SESSION, it’s probably a privilege boundary rather than a failure to tag; ask for a scoped view or a one‑time verification. If the server shows more capability than your risk posture allows, confirm the restrict level and restart without any -R override so the default restrictive posture applies.

Why this posture earns DBA trust

DBAs reasonably resist assistants that can write to production. SQLcl MCP avoids that leap of faith. You connect through named entries you control. You default to a restrictive mode. You tag your sessions with MODULE and ACTION so activity shows up cleanly in performance tooling. And, where available, MCP logs give an after‑the‑fact record. The point is not speed; it’s reversibility and evidence. In a world where models sometimes propose creative fixes, that’s the currency that gets you permission to try again tomorrow.

What’s intentionally out of scope here

This article focuses on local SQLcl MCP on a developer workstation, read‑only sandboxes, and minimal evidence checks. It does not cover managed or remote MCP hosting, enterprise audit/policy, advanced reasoning patterns (for example, Select AI or AI Profiles), vector‑native RAG, or change‑management workflows. Those topics are addressed next when we move beyond a single machine.

Where this leaves you next

You now have a local, developer‑friendly path for safe, traceable Oracle actions. Previously in this series: Article 1 (routing) and Article 2 (route‑to‑action bridge). Next up: Article 4, where we carry these guardrails into a managed path with audit, policy, and team controls so you can scale without losing the evidence and restraint you established here.

Freshness Note: Version‑sensitive behavior (tool catalog, defaults, restrict levels) should be verified against your installed SQLcl documentation (notably the 25.4 and 26.1 editions).

Route, Don’t Flood: Using db/SKILL.md to Steer Oracle‑Aware Assistants

Mark Nelson — Fri, 15 May 2026 12:21:32 +0000

If you’ve tried to make an assistant “Oracle‑aware,” you’ve likely hit the same wall: you paste a stack of links, the model blends vendor‑neutral habits with stale Oracle guidance, and the answers get vague when you need precision. The fix isn’t more context—it’s better routing. One Oracle skill at a time, in the right order, so every next move follows Oracle’s own sequence.

In Article 1 we outlined an operating model for AI on Oracle Database: route → act → trust. This piece goes deep on the first verb. It shows how to use db/SKILL.md in the public Oracle Database Skills repository as your front door; how to route by persona or by task; and how to enforce progressive discovery so your assistant loads exactly one file at a time. This is a no‑execution article; we stay in the routing lane and leave tools to Article 3 (SQLcl MCP). Prerequisite: you only need to browse https://github.com/oracle/skills in a web browser.

Why routing beats context dumping

Dumping a pile of links into a prompt feels thorough, but it blurs version lines and lets generic “SQL” patterns overrule Oracle‑specific guidance. It also bloats token budgets without improving the next decision. db/SKILL.md fixes this by providing a maintained map of Oracle Database skills, pointing you to the right starting file for common jobs, and encoding short, opinionated sequences for multi‑step workflows. The goal is not to read everything; it’s to pick the next file, digest it, and decide whether one more file is warranted. When you’re actually ready to execute a command, you’ve left routing and entered “act” (Article 3 covers SQLcl MCP).

Treat db/SKILL.md as the front door

db/SKILL.md is the router for the Database domain. It opens with a routing table and guidance to keep context tight—begin at the table, then read only the specific file or category you need.

It also shows the shape of the Database skills, including these directories:

db/agent
db/features
db/frameworks
db/performance
db/security
db/devops
db/migrations
db/sqlcl

Two parts matter most on your first pass. First, the file highlights concrete entry points for real jobs—where performance triage begins, where SQLcl basics and MCP server entries sit (setup is deferred to Article 3), how to approach schema migrations, and which agent behaviors (like schema discovery) must be understood before you write anything. Second, it outlines common sequences that act as guardrails. Examples include RAG (skills under db/features: ai‑profiles → vector‑search → dbms‑vector), slow‑query diagnosis (skills under db/performance: explain‑plan → wait‑events → optimizer‑stats → awr‑reports), and agent‑safe schema change (skills under db/agent and db/migrations: schema‑discovery → destructive‑op‑guards → idempotency‑patterns → schema‑migrations). There’s also an MCP setup path you’ll use later when you move from routing to action: sqlcl‑basics → a least‑privilege/privilege‑management skill in db/security → sqlcl‑mcp‑server.

Starting here changes how your assistant behaves. Instead of grabbing whatever looks related on the internet, it asks, “What’s the next Oracle‑authored file?” The result is fewer wrong turns and shorter, more reviewable prompts.

Route by persona when you’re scoping a project

When you’re defining scope rather than responding to a ticket, match your role and load one file. Don’t skim the whole repository first—route, read, and decide.

App developer

If you’re wiring a service to Oracle, start with frameworks and then layer on application‑specific skills. The db/frameworks directory anchors guidance for stacks such as Spring, Django, and SQLAlchemy: connection configuration, driver/dialect selection, data type mapping, and recommended connection patterns. Once the framework is set, dip into app‑dev skills for JSON, Spatial, Text, or pooling nuances you actually need. Doing it in this order avoids needless trial and error—you adopt the Oracle‑tested path before you customize.

AI engineer

Begin in db/agent and db/features. The agent skills cover behaviors that matter when a model touches a database: discover schema before DML, guard against destructive operations, make steps idempotent so retries don’t double‑apply, and identify clients for traceability. The features skills introduce Oracle’s AI‑native building blocks—Select AI and AI Profiles for governed NL2SQL, and AI Vector Search/DBMS_VECTOR for retrieval and RAG. Version gate: check each feature’s documentation. DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it.

DBA

The fastest way to get Oracle‑native outcomes from an assistant is to route through db/performance and then db/security. Performance skills teach the assistant to obtain and read the plan that actually executed (for example, via DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')), interpret row sources and cardinality, and pivot at the right moment from plan reading to wait events and then, if warranted, to AWR. Security skills ground privilege design, auditing, and encryption in Oracle’s vocabulary so “least privilege” becomes an actionable design rather than a slogan.

Migration lead

Treat db/migrations and db/devops as two halves of one job. Migration skills help with assessment and translation; devops skills handle delivery mechanics such as schema change workflows, online operations, Edition‑Based Redefinition, and testing. Loading one file at a time keeps heterogeneous estates from collapsing into generic, lowest‑common‑denominator advice.

Route by task when you’re on the clock

When a ticket arrives with a single job, follow the ordered sequences in the repository. Oracle’s diagnostics and defaults are opinionated for a reason; ignoring their order usually costs time.

RAG on Oracle Database

Start with AI Profiles, because that’s where you choose a provider and model and, crucially, define which database objects the model may see. Only after scope and governance are set should you learn retrieval patterns in vector‑search and then orchestrate pipelines with DBMS_VECTOR. The payoff is a governed RAG flow that can be inspected (SHOW SQL) and tightened (OBJECT_LIST and data access controls), rather than a bag of embeddings taped onto a database.

Route (skills under db/features): ai‑profiles → vector‑search → dbms‑vector
Reference: Select AI examples (SHOW SQL, OBJECT_LIST, and data access controls): https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
Version note: Follow the feature docs. DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it.

Slow‑query diagnosis

Begin by reading the plan that actually ran. In Oracle that means capturing the executed plan from the cursor (for example, DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')), not relying solely on an EXPLAIN PLAN estimate. If the plan looks sane but runtime stalls, pivot to wait events. If rows and joins are misestimated, fix statistics and cardinality before widening the window with AWR.

Route (skills under db/performance): explain‑plan → wait‑events → optimizer‑stats → awr‑reports
Availability note: AWR/ASH access depends on environment and licensing.

Agent‑safe schema change

Discover what exists before you propose change, add loss‑prevention so a single bad predicate can’t harm a table, and make each step idempotent so a retry won’t double‑apply. Only then move into an audited migration workflow.

Route (skills under db/agent and db/migrations): schema‑discovery → destructive‑op‑guards → idempotency‑patterns → schema‑migrations

A compact prompt that enforces progressive discovery

A small prompt can prevent freelancing by enforcing a loop: route, read one file, summarize, decide. That discipline keeps the context precise and affordable.

Prompt

Open db/SKILL.md. I’m [persona or task].
Which single skill file should I load next, and why?
Summarize that file in 5 bullets, then propose the next decision (read another file or stop).
Do not execute any actions.

Progressive routing loop

Mini demos (no DB access, no execution)

These prompts validate routing behavior before you connect any tools. They’re deliberately constrained to “read and decide.”

Demo A: Persona‑first route for a Spring app developer

Prompt

Open db/SKILL.md. I’m wiring a Spring Boot service to Oracle.
Which single frameworks/appdev skill should I load next, and why?
Summarize that file in 5 bullets, then propose the next decision.
Do not execute any actions.

Example assistant response

Next file: A Spring Data JPA with Oracle skill under db/frameworks/.
Why: It covers Oracle-specific connection configuration, driver/dialect selection, pooling, and type mapping.

Summary:
- Recommended Oracle JDBC URL formats and service naming for Spring Boot
- Dialect/driver selection and common DataSource pitfalls
- Transaction, batching, and fetch-size options tuned for Oracle
- LOB, DATE/TIMESTAMP, and NUMBER mapping considerations
- Starter checks and links to follow-up appdev skills

Next decision:
- If you need pooling/retry/circuit specifics, read a connection/pooling skill next.
- Otherwise, stop and apply these configuration steps before proceeding.

Demo B: Task‑first route for diagnosing a slow query

Prompt

Starting from db/SKILL.md, list the ordered skill files for slow-query triage.
Load only the first file, summarize it in 5 bullets, then recommend whether to read the next file or stop.
Do not execute any actions.

Example assistant response

Ordered files:
1) The explain-plan skill in db/performance/
2) The wait-events skill in db/performance/
3) The optimizer-stats skill in db/performance/
4) The awr-reports skill in db/performance/

Loaded: explain-plan

Summary:
- Obtain the executed plan from the cursor (vs. EXPLAIN PLAN estimates) and where to find it
- Read row sources and cardinality; spot misestimates and join-order issues
- Recognize anti-patterns (forced full scans, skewed histograms, implicit conversions)
- Interpret plan notes for adaptive features and parallel decisions
- Decide when to pivot from plan analysis to wait-event inspection

Recommendation:
- Read wait-events next if the plan looks reasonable but runtime stalls.
- If the plan is clearly wrong, stop and fix plan issues before proceeding.

Notes:

The “explain‑plan” skill in this route teaches capturing and reading the actual cursor plan, not just the EXPLAIN PLAN estimate.
AWR/ASH may not be available in all environments and may require licensing.

Version scope and context‑budget discipline

Assume Oracle Database 19c as the baseline unless a skill states a higher requirement. For AI features, check per‑feature documentation: DBMS_VECTOR and many Select AI/AI Profiles examples are documented for 26ai; several AI capabilities are also available in Autonomous Database. Do not assume 19c availability unless a skill or doc explicitly states it. Label those steps clearly when you route.

Two habits keep assistants accurate and affordable:

Load one file or short index at a time, then summarize in five bullets.
Decide whether to stop, read one more, or hand off to action (SQLcl MCP in Article 3). Don’t blur routing with execution.

Why “route, don’t flood” holds up under pressure

Teams tend to bulk‑load context when deadlines loom. Ironically, progressive discovery pays off most when time is tight. db/SKILL.md compresses Oracle experience into short routes you can trust: read the executed plan before hunting waits, scope an AI Profile before experimenting with retrieval, and prove discovery and guards before you accept any schema change. Staying inside those lanes doesn’t slow you down; it eliminates the detours that burn days.

Make the front door easy to reach. The simplest path is to browse the repository in a web browser and open db/SKILL.md. If you prefer a local copy, install the Database domain directly:

npx skills add oracle/skills/db

This puts the domain files in your working directory so you can open db/SKILL.md immediately. Then set your assistant’s “Oracle Database” system prompt to begin with “Open db/SKILL.md,” and keep the progressive‑discovery loop in place until the next decision is truly to act.

Conclusion

Oracle Database Skills is not a PDF dump for models. It’s an operating method that starts with routing. Use db/SKILL.md as your first touch, load one file at a time, and stop before you wander into execution. In Article 3, we’ll introduce SQLcl MCP as the bounded action surface that pairs naturally with this approach. Until then: route → read one → summarize → decide.

Sources

Oracle Database Skills repository root: https://github.com/oracle/skills
Database router (db/SKILL.md): https://github.com/oracle/skills/blob/main/db/SKILL.md
Frameworks directory: https://github.com/oracle/skills/tree/main/db/frameworks
Performance directory: https://github.com/oracle/skills/tree/main/db/performance
Select AI examples (SHOW SQL, OBJECT_LIST, and data access controls): https://docs.oracle.com/en/database/oracle/oracle-database/26/selai/examples-using-select-ai.html
DBMS_XPLAN.DISPLAY_CURSOR (19c): https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_XPLAN.html
Licensing Information User Manual (AWR/ASH availability/licensing): https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/index.html
DBMS_VECTOR (26ai): https://docs.oracle.com/en/database/oracle/oracle-database/26/arpls/DBMS_VECTOR.html

AI agents know SQL. Oracle Database Skills teach them Oracle

Mark Nelson — Wed, 13 May 2026 14:02:12 +0000

This is article 1 of 8 in my Oracle Database Skills series.

Most assistants can write SQL. That’s not the same as knowing Oracle Database. The difference shows up in production. A model that learned generic patterns can assume another vendor’s isolation semantics, borrow date arithmetic that silently miscounts in Oracle, rely on hints that “fix” one plan but fight the optimizer elsewhere, or forget Edition‑Based Redefinition (EBR) during a migration. Give that assistant a live connection and those differences turn into risk.

Oracle Database Skills change the operating model. Instead of hoping a generalist remembers every Oracle rule, you route it—one decision at a time—to Oracle‑authored, source‑backed instructions. When action is appropriate, you expose only small, named tools via Oracle MCP servers (MCP = Model Context Protocol), never a blank connection string. And you keep Oracle’s governance in the path so identity, scope, and evidence ride with every request. In short: route first, act through bounded tools, and let the database prove what happened.

Why generic SQL knowledge fails on Oracle Database

Large models generalize across vendors; your database does not. Oracle’s transaction semantics, time idioms, plan management, and governance are exactly where generic advice goes sideways.

Isolation and consistency are different in Oracle. Many public answers treat isolation levels as dials that change write visibility. Oracle’s multi‑version read consistency gives readers a clean snapshot but doesn’t use the same language or behavior as those engines. Ask a generalist assistant to “make this serializable” and you can get a non‑Oracle explanation—or an irrelevant fix. See Oracle Database Concepts on consistent reads for details.
Time math bites hard. DATE/TIMESTAMP/INTERVAL semantics and NLS/time zone interactions vary by vendor; Oracle requires vendor‑specific idioms. Skills provide tested patterns for Oracle SQL and PL/SQL so an assistant doesn’t have to guess which expression respects your NLS settings, time zones, and interval choices.
Plan stability demands a different order of operations. Oracle recommends maintaining accurate optimizer statistics and using SQL Plan Management (baselines) for stability; avoid over‑reliance on ad‑hoc hints in application code. Skills teach that progression so the assistant recommends fixes that survive the next gather‑stats job.
Governance cannot be an afterthought. “Connect and run” examples usually ignore Virtual Private Dat
abase (VPD), Unified Auditing, Fine‑Grained Auditing, Data Redaction, Transparent Data Encryption (TDE), and network ACLs/private endpoints. In Oracle, those are how you make AI access presentable to a DBA, a security reviewer, or an auditor. If you want to ship, keep those controls in the path.

Route first with Oracle Database Skills (progressive discovery)

The oracle/skills repository is not a doc dump. It’s installable operating instructions, loaded sparingly—one file at a time—so assistants (and humans) can make the next decision correctly without carrying 200 pages of context. The Database domain (oracle/skills/db) is organized the way you work: agent safety; features like Select AI, AI Profiles, and Vector Search; app frameworks; performance; DevOps and migrations; SQLcl; and security. The front door is a routing file: db/SKILL.md.

Progressive discovery narrows the model’s attention to what moves the work forward, preserves version truth, and leaves a clean review trail. Because the assistant reads only the next skill, it doesn’t mix versions or unrelated features. Skills mark Oracle Database 19c as the baseline and flag when you need Autonomous Database (Serverless) or Oracle Database 23ai/26ai features. Teams can see which Oracle sources the assistant consulted, in what order, and why.

A concrete path makes this real. Suppose you’re building retrieval‑augmented generation on Oracle. The route in db/SKILL.md steers you through Select AI profiles (to set provider and scope), then Vector Search basics (to choose the right data model and index), then DBMS_VECTOR (to make retrieval programmable). You don’t need AWR or EBR on day one. The route keeps you focused until you do.

Act through bounded tools: Oracle’s MCP surfaces

Sometimes the assistant must do more than explain. It needs to run a read‑only query, inspect a schema, or check a plan. That’s where Oracle’s MCP servers help: they expose small, named capabilities rather than hand the assistant a general‑purpose connection.

Local development: SQLcl MCP server. Built into SQLcl, it exposes a concise set of named tools—for example, to list saved connections, connect using a saved connection, and run SQL—and supports configurable “restrict levels,” with higher numbers enforcing tighter limits. As of SQLcl 25.4, the MCP server defaults to restrict level 4 (most restrictive). Confirm your version and configuration. Teams can set the most restrictive mode for local experiments. SQLcl MCP can also leave evidence when configured: it can log activity (for example, to a DBTOOLS$MCP_LOG table), set V$SESSION.MODULE and V$SESSION.ACTION to identifiable values (such as the MCP client and a client‑provided model/agent label), and tag LLM‑generated statements with a comment. See the SQLcl MCP docs linked below for version‑specific details.
Managed, governed execution: Autonomous Database (Serverless) MCP server. Enable it on a specific Autonomous Database (via an OCI free‑form tag) and you get a per‑database HTTP endpoint and authentication that serve built‑in tools or custom tools you define with DBMS_CLOUD_AI_AGENT.CREATE_TOOL. Identity and roles apply as usual. So do network ACLs and private endpoints, VPD policies, and Unified Auditing and Fine‑Grained Auditing when configured. If your team wants a controlled tool surface without hosting anything, this is the right shape: the database remains the control plane for what tools exist and who can use them.

The shared property is restraint. In both cases, the assistant gets the minimum set of named actions appropriate for the task, and Oracle‑native controls keep doing their job.

Keep Oracle governance in the path (trust and evidence)

Oracle’s governance features aren’t an appendix; they are the safety net that lets you ship. Roles and least privilege define what an assistant can reach. Network ACLs and private endpoints restrict egress. VPD enforces row‑level policies you already rely on. Unified Auditing and Fine‑Grained Auditing can be configured to record the activity you care about. Data Redaction and TDE protect sensitive data at query time and at rest. These controls are available and can be configured; they are not enabled by default in every environment.

The benefit for AI work is simple once you see it: your proof is in the database. When actions go through SQLcl MCP or the Autonomous Database (Serverless) MCP server, the same identity, scoping, and auditing options that protect your applications also protect your assistant. The question becomes less “Can we trust the agent?” and more “Show me the database evidence.”

Mini demo: install skills and open the routing door

Begin without touching a database or setting up MCP. Fetch the Database Skills domain and open the routing file. From there, pick the path that matches your role and objective.

You only need internet access, Node.js installed, and a text editor. This step does not connect to a database, handle credentials or secrets, or change any MCP restrict levels.

npx skills add oracle/skills/db

This installs the Database domain into your working directory. Open db/SKILL.md in your editor. If you’re an app developer wiring a Spring app, follow the frameworks route. If you’re an AI engineer, start with the agent and features routes (Select AI, AI Profiles, Vector) before you let any assistant write SQL. If you’re a DBA, begin with performance and security so the assistant speaks AWR/ASH/optimizer and respects governance. Keep the habit: load only the next file you need, then stop. That discipline keeps model context clean and your review surface small.

Where to find this in Oracle docs and repos

The instruction layer is the skills repo itself, with db/SKILL.md acting as the router. The controlled action layer is documented for both entry points: SQLcl MCP (restrict levels, tool catalog examples, saved connections, monitoring, activity logging and tagging) and the Autonomous Database (Serverless) MCP server (enablement via OCI free‑form tag, per‑database endpoint and authentication, custom tool publication, and governance integration). The trust layer is the familiar security stack: roles and privileges; network ACLs and private endpoints; VPD; Unified Auditing and Fine‑Grained Auditing when configured; Data Redaction; encryption. Each is a first‑class Oracle feature you can point to in docs and verify in your environment.

Source links:

Skills repo and routing (start at db/SKILL.md)
SQLcl MCP (using the server; logging/tagging; restrict levels)
Autonomous Database (Serverless) MCP server (enable and use)
Oracle Database Concepts — Consistent Reads/MVRC
Oracle SQL Language Reference — Data Types (DATE/TIMESTAMP/INTERVAL and NLS)
Optimizer statistics and SQL Plan Management
Oracle Database Security Guide (19c baseline)

Version scope and environment notes

Most skills target Oracle Database 19c as the baseline. Files that require features from Autonomous Database (Serverless) or newer Oracle Database 23ai/26ai releases—such as Select AI, AI Profiles, Vector Search, or the Autonomous Database (Serverless) MCP server—are labeled in the repo. If you work in a mixed‑version estate, treat those labels as gates and route accordingly. For hands‑on tries in this opener, you need only a local machine with git and a text editor. MCP client steps and database connectivity come later, when action is appropriate.

What’s out of scope (in this opener)

Enabling or configuring the Autonomous Database (Serverless) MCP server
Connecting to any database or handling credentials
Changing SQLcl MCP restrict levels
Configuring Unified Auditing or Fine‑Grained Auditing
Demonstrating Select AI or Vector Search live

A short story from the field

A team asked an assistant to “optimize a daily sales query.” The first answer added an index and a couple of hints that looked fine in isolation. The skills route would have sent it somewhere else: read the explain-plan and optimizer-stats files first; check cardinalities and stale stats; consider a plan baseline if volatility is the real problem. That path took longer but avoided chasing a hint that didn’t survive the next gather‑stats job. The difference wasn’t model IQ—it was having Oracle‑specific operating instructions and the habit of loading only what came next.

Where this series goes from here

Everything you need to begin is in your editor: the skills repo, the routing file, and the mental model—route, act, trust. We’ll build outward from that front door, showing how local and managed MCP surfaces work when action is warranted, and how Select AI and Vector Search improve reasoning close to your data. Expect short, source‑backed posts that follow the same grammar:

Route: pick a path in db/SKILL.md for your role and task.
Act: use SQLcl MCP locally or the Autonomous Database (Serverless) MCP server when you need controlled actions.
Guardrail: keep roles, ACLs/private endpoints, VPD, and auditing in‑path for evidence and scope.

The eight posts are organized into four groups:

Foundation 1 — this post; 2 — Route, Don’t Flood Mental model and db/SKILL.md as routing layer
Action 3 — SQLcl MCP; 4 — Managed MCP in Autonomous AI Database Local and remote bounded action surfaces
Intelligence 5 — Select AI and AI Profiles; 6 — Vector‑Native RAG NL2SQL reasoning quality and in‑database retrieval
Rollout 7 — Agent‑Safe Change Delivery; 8 — The Trust Layer Safe change workflows and enterprise governance

Conclusion: a safer default for Oracle‑backed AI

If your assistant can touch Oracle Database, “just let it connect” isn’t an acceptable plan. Put the skills repo between the model and the problem so it learns to work the Oracle way. When execution is appropriate, send it through SQLcl MCP or the Autonomous Database (Serverless) MCP server so actions are small, named, and governed. Keep Oracle’s controls—roles, ACLs, VPD, auditing, redaction, encryption—in the path so every action is scoped and traceable.

Oracle AI Agent Memory: A Governed, Unified Memory Core for Enterprise AI Agents

Wojtek Pluta — Mon, 04 May 2026 08:57:04 +0000

This article is syndicated from the original post on blogs.oracle.com. Read the canonical version there for the latest updates.

Recently, Oracle introduced the Oracle AI Agent Memory Python package, a model and framework-agnostic memory solution that gives enterprise AI teams a governed memory core on Oracle AI Database: short-term threads with summaries and context cards, long-term durable memories with vector search, automatic LLM-based memory extraction, and the governance and isolation production agents require.

Oracle Agent Memory: a unified agent memory layer built on Oracle AI Database.

Oracle AI Agent Memory is available now via PyPI as oracleagentmemory and documented in the Oracle Help Center. It is designed to replace the patchwork memory stack most production agents inherit with a single governed memory substrate built on Oracle AI Database.

This is the difference between an agent that is memory-augmented, given a vector store to consult, and one that is memory-aware, responsible for reading from and writing to its own governed, durable state with one enterprise-grade backend.

"Agent memory has shifted from a research curiosity to a production requirement in under two years. Teams shipping serious agentic systems need a backend that handles vectors, structured data, and transactional consistency in one place, not three stitched together. Oracle AI Database is one of the few platforms that delivers all of that natively, which is why we built Hindsight to run on it as a first-class backend."
— Chris Latimer, Co-Founder & CEO of Vectorize

Why Agent Memory, Why Now

The four types of agent memory: working, semantic, episodic, and procedural.

Most agent implementations treat memory as a bolt-on. A vector store consulted at retrieval time, a chat history table glued on beside it, and whatever hand-written extraction logic the team can maintain.

That stack holds together for a demo. It falls apart from the moment an enterprise AI team asks questions that actually matter. Who owns the memory? Where is it governed? How do we isolate tenants? How do we audit what the agent learned, and how do we forget it on request?

Context windows have grown over the years, but no context window is large enough to hold the full state of a long-running agent: weeks of user preferences, accumulated domain knowledge, prior tool outcomes, evolving task state, and the reasoning history that makes each decision defensible.

Agents need memory for the same reasons people do: to hold an active state while working on a problem, to retain facts learned over time, to recall specific past experiences, and to encode behavioral rules and procedures.

A practical taxonomy for agent memory commonly used in agent design covers four types:

Working memory is the active state the agent is reasoning over right now, the running conversation and the scratchpad the model sees at inference time.
Semantic memory is the durable facts and knowledge the agent accumulates about users, entities, and the world: preferences, canonical definitions, structured reference data.
Episodic memory is specific past experiences the agent can recall, what happened on a prior session, what the user asked three weeks ago, how a similar task resolved last time.
Procedural memory is the behavioral rules, guidelines, and learned procedures that shape how the agent acts, how to handle customers, which tools to prefer, what not to do.

These are not four different systems. They are four access patterns over the same underlying state, which is what makes a unified memory core the right architectural answer rather than four bolted-together services.

Oracle AI Database as the Memory Core

Reference architecture for Oracle AI Agent Memory on Oracle AI Database.

Oracle AI Database combines vector similarity search, relational querying, and graph-aware data access in one governed engine, enabling semantic recall alongside precise transactional and relationship-centric retrieval. Combined with Oracle's operational story, backups, replication, high availability, encryption, fine-grained access control, and audit, teams get a path from notebook to regulated production without swapping storage layers, rewriting compliance reviews, or stitching together bespoke isolation logic along the way.

Memory engineering, as a discipline, demands substrate choices that hold up under the access patterns a real enterprise agent actually has: concurrent writes, per-user and per-tenant scoping, full audit, and semantic retrieval at scale.

“Enterprise agents need an agent memory solution with robust security guarantees, strong governance controls, sophisticated workload isolation, as well as deep integration within the enterprise data platform. Oracle AI Agent Memory greatly simplifies building agent memory solutions by consolidating what are usually multiple separate and fragmented services, within the converged database architecture that customers already trust for their most critical data.”
— Tirthankar Lahiri, SVP, Mission-Critical Data and AI Engines, Oracle Database

Production agent memory carries two loads. Developers wire the stack together: vector store, chat log, extraction scripts, isolation logic, governance per piece. Agents reason over the fragments, deciding what to retrieve from where and fitting the relevant world into a finite context window each turn.

Oracle AI Agent Memory lifts both. One governed client replaces the four-service stack, with one set of credentials, one compliance review, and one backup story. Working, semantic, episodic, and procedural memory share one substrate and one retrieval surface, so the model reasons over a coherent view of its state. Summarization and scoped retrieval put the right subset into context at the right moment, freeing the model to spend its reasoning budget on the task rather than memory bookkeeping.

Automatic LLM-based extraction turns conversation into durable memories without hand-rolled prompt chains. Multi-tenant isolation is enforced at the store layer, so a single schema can host multiple deployments without cross-tenant leakage. And because the SDK is framework-agnostic, integrating with LangGraph, Claude Agent SDK, OpenAI Agent SDK, WayFlow, and custom harnesses, teams aren't locked into a single runtime to get the substrate.

Key Benefits For AI Workloads

Configuration: gpt-5.5, reasoning effort xhigh, nomic-embed-v1.5 embeddings, local HNSW index, top-K = 200. X-axis truncated; all categories scored above 88%.

Oracle AI Agent Memory is built for the operational realities of running AI agents in production.

Production-grade recall on long-horizon memory benchmarks. On LongMemEval, the standard academic benchmark for long-context agent memory, Oracle AI Agent Memory scores 93.8% (469 of 500), with the strongest results on the categories that matter most for production agents: 100% on single-session assistant recall, 96% on temporal reasoning, and 95% on knowledge-update tasks. Multi-session recall, the hardest category in the benchmark, lands at 88%. Configuration: OpenAI gpt-5.5 (reasoning effort xhigh), nomic-embed-text-v1.5 embeddings, local HNSW index, top-K = 200.

Bounded per-turn cost as sessions extend. Periodic thread summarization, durable memory extraction, and prompt-time message compaction keep the working context bounded as conversations grow. In an 80-turn scripted conversation, Oracle AI Agent Memory held per-request input around 1,300 tokens for the full run while a flat-history baseline grew linearly past 13,900 — roughly 9.5× more tokens per request by the final turn, and a much steeper bill across the full conversation. Teams shipping long-running agents trade a linear-in-history cost curve for a flat one.

80-turn ChromAtlas-ND scripted conversation · gpt-5.4 (raw OpenAI client, no framework). Token estimate: chars / 4 (notebook convention)

Better answers than a flat-history baseline. A flat-history agent has the entire verbatim conversation in its prompt — every fact ever mentioned, in order. By rights it should be hard to beat on recall. Across the same 80-turn conversation, evaluated by an impartial gpt-5.4 judge on accuracy, completeness, relevance, and coherence, Oracle AI Agent Memory won 48 turns to flat history's 13, with 19 ties: 3.7× more wins despite the baseline's information advantage. A retrieved context card focuses the model on what matters; a sprawling transcript dilutes attention across noise.

80-turn ChromAtlas-ND scripted conversation; judge: gpt-5.4; scored on accuracy, completeness, relevance, coherence

Cost is a tunable knob, not a fixed value. The summarization trigger controls how aggressively the package compacts thread context, and it moves the cost-fidelity trade-off directly. In an 8-query demo conversation (five runs per threshold), a 10,000-token trigger landed at a mean of 121,268 total tokens, about 60% under the 306,823-token flat-history baseline. As the trigger rises, the package compacts less often and preserves more raw context per turn; by a 50–70k trigger, mean total tokens approach or exceed the baseline, and run-to-run variance widens. Teams pick the threshold that matches their answer-quality requirements and lock in the cost envelope they want, rather than accepting whatever curve a fragmented stack produces.

Memory Agent Efficiency vs Summarization Threshold on Demo Conversation. Num queries = 8; num runs per threshold = 5.

One backend, every Python runtime. LangGraph, the Claude Agent SDK, the OpenAI Agents SDK, WayFlow, and custom Python harnesses all instantiate the same OracleAgentMemory client and read and write the same Oracle Database store. Teams running more than one framework no longer rebuild memory per runtime, and migrations between frameworks no longer mean migrating memory.

Primitives for audit and erasure on a single substrate. Every record carries user, agent, thread, and timestamp scoping fields, and the SDK exposes search, list, and per-record delete operations across memories, threads, and messages, so callers can locate records for a subject and remove them on request. Oracle Database's native auditing covers the storage layer underneath. Compliance reviews land on a single substrate (one database with audit, retention, and access controls already in the data plane) rather than four services with four reviews.

One vendor relationship for production agent memory. A single Oracle AI Database instance carries vector search, structured state, JSON document retrieval, transactional consistency, and database-native audit. No second vector database to license, no third service to monitor and scale, no fourth backup pipeline to maintain.

Who Oracle AI Agent Memory Is For

Oracle AI Agent Memory is designed for:

AI Developers and engineers building production agents who need durable short-term and long-term memory in one place, with enterprise security and isolation.
Teams already running Oracle AI Database who want their agents to write to the same governed backend as the rest of the business
Technical leaders evaluating Oracle AI Database for agent memory infrastructure at scale, with compliance and audit requirements

Getting Started

Install the Oracle AI Agent Memory package:

pip install oracleagentmemory

A minimal end-to-end loop in Python looks like this:

from oracleagentmemory import AgentMemory

memory = AgentMemory.from_connection(
    connection_string="...",
    user_id="user_123",
)

# Add conversation turns to a short-term thread
thread_id = memory.create_thread(user_id="user_123")
memory.add_messages(thread_id, messages=[
    {"role": "user", "content": "I prefer vegan meals."},
    {"role": "assistant", "content": "Noted."},
])

# Extract durable long-term memories from the thread
memory.extract_memories(thread_id)

# Scoped search over long-term memory, enforced per-user
results = memory.search(
    user_id="user_123",
    query="dietary preferences",
    limit=5,
)

Code samples are illustrative; the final API surface is documented in the Oracle Help Center.

The quickstart notebook and framework how-to guides are available in the Oracle AI Developer Hub, and the full API reference is available in the Oracle Help Center.

Oracle AI Agent Memory is the first release of a broader commitment to a governed memory substrate enterprise agents need. Memory engineering is still an emerging discipline. The infrastructure behind it should not be.

What Is Agent Memory? A Beginner’s Guide for AI Developers

Anya Summers — Wed, 29 Apr 2026 09:11:11 +0000

TL;DR: Agent memory is stored state an AI agent can retrieve across sessions to maintain continuity. A bigger context window does not fix the problem. Once memory has to persist, be scoped to the right user, and be retrieved reliably, it becomes a data problem, and is often best handled in a database such as Oracle AI Database.

Why This Matters

You can build a convincing AI agent surprisingly fast. Give it a model, wire up a few tools, and it can look sharp in the first session. Then the user comes back the next day. They ask a follow-up question. They refer to a failed attempt from yesterday. They expect the agent to remember that they prefer Python examples and concise answers. Instead, the agent starts from scratch.

That is usually the moment when a demo stops feeling clever and starts feeling flimsy. A lot of beginner guides blur this point. They talk as if a larger context window solves the whole problem. It does not. A larger context window gives the model more room to work during one session. It does not give the system a memory of what happened last week. When the session ends, the context goes with it. Memory is the layer that preserves what matters.

What You'll Learn

What agent memory actually is, and why a bigger context window is not a substitute
The four useful types of agent memory: working, procedural, semantic, and episodic
When memory stops being a prompt trick and starts being infrastructure
How to implement a persistent semantic memory store using LangChain and Oracle AI Database
Common mistakes to avoid and a checklist for building your first memory layer

What Is Agent Memory?

Agent memory is the information an AI agent can carry from one interaction to the next. That information might be a user preference, a summary of an earlier conversation, a previous task result, or facts the system has learned and may need later.

The key point is simple. It is not enough that the model saw the information once. The system needs to be able to bring it back when it matters.

Imagine a user tells an assistant three things today:

they prefer concise answers
they are working in Python
the last attempt failed because their API key had expired

If the assistant can use that information tomorrow without being told again, it has memory. If the user has to repeat all three points, it does not. That is the difference.

Context Window vs Memory: What Is the Difference?

This is the part that trips people up. A context window is the text the model can see right now. That includes the prompt, the recent messages, retrieved documents, tool outputs, and any system instructions passed into the current call. It is the model's live working space. Memory is different. Memory is stored state the system can recover later.

The simplest analogy is this:

the context window is the desk
memory is the filing cabinet

A bigger desk is useful. You can spread out more notes and hold more detail in front of the model. But the desk gets cleared. The filing cabinet is what lets you come back tomorrow, open the right folder, and pick up where you left off.

It also helps to separate memory from Retrieval Augmented Generation (RAG). RAG brings in external knowledge (e.g.,company PDFs) so the model can answer a question with better grounding. Memory, by contrast, preserves useful state from previous interactions. One helps the agent know more in the moment; the other helps it behave with continuity over time.

In practice, the strongest systems usually use all three layers:

context window for active reasoning
retrieval for outside knowledge
memory for continuity across sessions

A simple scenario makes the difference concrete.

Monday: The user says, "I am learning Python and I prefer short answers." The agent helps them debug a script and the session ends.

Tuesday: The user returns and asks, "Can you help me sort a list?"

Without memory, the agent gives a long answer in whichever language it guesses. With memory, the agent retrieves the user's preference, responds in Python, and keeps the answer concise. Same model.

Same prompt. Different system around it.

The Four Types of Agent Memory

A simple way to understand agent memory is to borrow a rough model from human memory. It's not perfect, but it's useful.

Memory type	Simple meaning	Example in an agent
Working memory	What the agent is handling right now	Current messages, tool outputs, temporary reasoning state
Procedural memory	How the agent does things	Instructions, workflows, and tool-use rules
Semantic memory	Facts the agent has learned	User preferences, saved facts, product knowledge
Episodic memory	Specific past events	Previous sessions, task history, and failed attempts

This framework matters because not every agent needs the same mix. A customer support agent may need semantic memory for customer preferences and episodic memory for past tickets. A coding agent may care more about procedural memory for workflows and semantic memory for project conventions. A one-shot Q&A bot may not need much memory at all.

That's worth keeping in mind: "add memory" is not a universal requirement. It only makes sense when continuity actually improves the experience or the outcome.

When Does Agent Memory Become a Data Problem?

The moment you want memory to persist, you're no longer just writing prompts. You are making storage and retrieval decisions.

You need to decide:

what is worth storing
what should be ignored
how memories are tied to the right user
how old or stale memories get updated or removed
how the system finds the right memory at the right time

That is where many first agent builds get messy. Saving text is easy. Bringing back the right memory for the right user, in the right context, without pulling in noise, is the hard part. Once memory has to persist and be searchable, structure matters. You typically need metadata such as user_id, memory_type, timestamps, and maybe expiry rules. You also need a retrieval strategy that avoids surfacing irrelevant or outdated information.

Persistence also introduces governance concerns. As soon as an agent is storing anything that can be traced to a person, you are dealing with personally identifiable information, and every mature system needs answers to a small set of questions. What personal data is being stored? How long is it kept? How does a user request deletion, and can the system actually honour that request?

Building those answers in from day one is much easier than retrofitting them after the first audit or data subject request. Governance lives best as code and schemas, not as a Confluence page somebody hopes to find later.

This is why databases appear so quickly in serious agent systems. Prompts are temporary. Memory needs storage, filtering, and lifecycle rules. If you are storing embeddings, scoping memories to a user, and reusing them later, you are designing a small data system whether you planned to or not.

That sounds heavier than it is. You do not need a giant memory platform on day one. But you do need to stop thinking of memory as "extra text for the prompt". It is a system component. Without structure and filtering, memory quickly turns into noisy context that reduces answer quality instead of improving it.

Architecture Overview

At a high level, a production-ready agent memory system has three layers working together: a context window for active reasoning, a retrieval layer for outside knowledge (RAG), and a persistent memory store for continuity across sessions. The memory store is where platforms like Oracle AI Database provide the most value.

Oracle AI Database is a strong fit for production memory systems for three reasons:

Durability: Memory is only valuable if it survives restarts, deployments, and the kind of quiet infrastructure changes that happen in any real engineering environment.
Metadata-driven filtering: Storing vectors next to structured columns like user_id, tenant_id, memory_type, and created_at means retrieval can be scoped cleanly without building a second database to hold the filters.
Lifecycle control: Expiry, archival, soft-delete, and audit trails are problems databases have been solving for decades, and memory needs all of them. Running vector search in the same database that already holds the relational and governance layer removes a whole category of synchronisation bugs that would otherwise appear on week three.

Prerequisites

Python 3.9 or later
Access to an Oracle AI Database 26ai instance (Autonomous Database, container, or local install)
An embedding model configured and callable from your environment
Basic familiarity with LangChain concepts (vector stores, retrievers)

Step-by-Step Guide: A Simple Memory Layer with LangChain and Oracle

The goal here is not to build a huge platform. It is to make the pattern concrete: save a useful memory, attach metadata, and retrieve it later when it becomes relevant.

Step 1: Install the Packages

Install the LangChain Oracle integration along with the Oracle Python driver and LangChain core.

pip install langchain-oracledb oracledb langchain-core

Step 2: Connect to a Persistent Store

Open a connection to Oracle and wrap it in a LangChain OracleVS vector store. This example assumes you already have an embedding model configured. The broad idea matters more than the exact class names — the agent now has somewhere durable to store semantic memory outside the prompt.

import oracledb
from langchain_oracledb.vectorstores import OracleVS

connection = oracledb.connect(
    user="agent_user",
    password="password",
    dsn="hostname:port/service"
)

memory_store = OracleVS(
    client=connection,
    embedding_function=embeddings,
    table_name="AGENT_MEMORY"
)

Step 3: Store a Memory and Retrieve It Later

Save something worth remembering, attach metadata so it stays scoped correctly, and retrieve it when the next interaction needs it. That is the core loop.

memory_store.add_texts(
    texts=["User prefers concise answers and Python examples."],
    metadatas=[{"user_id": "user_123", "memory_type": "preference"}]
)

results = memory_store.similarity_search(
    "How should I answer this user?",
    k=3,
    filter={"user_id": "user_123"}
)

If you only remember one design lesson from this section, make it this: memory quality depends less on storing more information and more on retrieving the right information cleanly.

When Do You Actually Need Agent Memory?

Not every agent needs memory. This is where it is easy to overbuild. You probably need memory when:

the same user comes back repeatedly
the agent needs to remember preferences or previous decisions
tasks span multiple sessions
the system improves when it learns from earlier outcomes

You may not need much memory when:

the task is one-off question answering
document retrieval is enough
users are unlikely to return
continuity adds more complexity than value

A lot of first agent projects do not need a big memory layer. They need a clear use case and a small amount of well-scoped memory. That's usually a better place to start.

Validation & Troubleshooting

Retrieval returns noise: If results look bad with ten stored memories, they will be worse at ten thousand. Validate retrieval quality early by running a handful of realistic queries and inspecting what comes back.
Wrong user's memories appear: Every similarity_search call should include a filter on user_id. If you see cross-user leakage, check that metadata is written on every add_texts call.
Stale memories resurface: Add a created_at timestamp and define lifecycle rules. Preferences change. Facts expire. If the system never updates or retires old memories, it will eventually return stale context.
Connection errors to Oracle: Verify your DSN string matches your service name, and that the oracledb driver can reach the host. Autonomous Database users should confirm their wallet configuration.
Everything gets saved as a "memory": That sounds safe, but it usually creates noise. Decide upfront what qualifies as a memory worth storing.

Common Mistakes to Avoid

A few mistakes show up again and again.

First, people treat a larger context window as if it solves memory. It helps within a single session. It does not create continuity on its own.
Second, they save everything. That sounds safe, but it usually creates noise. If every past detail becomes a "memory", retrieval quality drops fast.
Third, they skip metadata. Without fields like user_id, memory_type, or timestamps, the system has no reliable way to determine which memory belongs to whom or whether it is still relevant.
Fourth, they forget memory lifecycle. Preferences change. Facts expire. Previous failures become irrelevant. If the system never updates or retires old memories, it will eventually return stale context.

Finally, some teams add memory before they have proved they need it. That is backwards. Start with the user problem. Then decide whether continuity genuinely improves the product.

Key Takeaways

Agent memory is stored state an agent retrieves across sessions to maintain continuity. A context window helps with the current interaction; memory enables continuity over time.
Four useful memory types are working, procedural, semantic, and episodic. Not every agent needs the same mix.
As soon as memory must persist, be scoped to the right user, and be retrieved reliably, you are dealing with a data problem.
Start with one memory type to begin with. Semantic memory for user preferences is usually the highest-value entry point.
Scope every memory by user_id, and include memory_type and a timestamp from the first write.
Oracle AI Database 26ai fits well here by combining durable storage, metadata-driven filtering, and lifecycle control in the same system that already holds your relational and governance layer.
Building an agent is easy. Keeping one alive in production is where memory stops being a prompt trick and bedomes infrastructure.

Frequently Asked Questions

What is the difference between a context window and agent memory?
A context window is the information the model can see during the current interaction. Agent memory is information the system stores and can bring back in future interactions.

What are the main types of agent memory?
A simple framework uses four types: working, procedural, semantic, and episodic. In practice, most agent builds care most about semantic memory for facts and preferences, and episodic memory for past interactions.

Do all AI agents need memory?
No. Some agents only answer one-off questions and do fine with a prompt plus retrieval. Memory becomes useful when continuity across sessions actually improves the result.

Can a vector database be used for agent memory?
Yes. A vector database or vector-capable store can work well for semantic memory, especially when you need similarity search. It still needs metadata and retrieval rules, otherwise it turns into a pile of loosely relevant text.

When should I use Oracle AI Database 26ai for agent memory?
Use it when you need durable storage, vector similarity search, and metadata-driven filtering in the same system. It is especially valuable when your application already has a relational and governance layer, because running vector search alongside it removes a whole category of synchronisation bugs.

What are the limitations?
Memory architectures are still largely bespoke. There is no clean default answer for when to summarise versus store verbatim, or how to balance recall against retrieval noise as the store grows. Eviction, forgetting, and evaluation of memory systems are all genuinely open problems. Start small, instrument retrieval, and treat the design as something you will revise.

Next Steps

Unified Memory Core for AI Agents

Anya Summers — Mon, 27 Apr 2026 16:05:40 +0000

A practical guide to building episodic, lexical, vector, and graph memory workflows in Oracle AI Database

Companion notebook: Unified Agent Memory with Oracle AI Database

Key takeaways

A unified memory core combines episodic, lexical, semantic, and relationship-aware retrieval in one governed platform.
Hybrid retrieval (Oracle Text + vector + metadata filters) improves reliability in enterprise queries.
GRAPH_TABLE adds business relationship context beyond nearest-neighbor similarity.
DBMS_SCHEDULER and VPD patterns make memory lifecycle and security operational.
The companion notebook demonstrates all core patterns in a runnable workflow.

What agent memory is and why it matters

Agent memory is the stored context an AI system can access across steps, sessions, or workflows. In practice, it supports several critical functions:

State persistence – remembering what the agent is currently doing

Context continuity – carrying forward prior user goals and constraints

Knowledge retrieval – finding facts, documents, and learned abstractions

Workflow resilience – resuming long-running tasks after delays or failures

Personalization – adapting behavior based on historical interactions

This matters because modern agents are not just answering isolated questions. They are coordinating tools, operating over enterprise systems, and producing outputs that depend on both immediate context and historical knowledge.

At a high level, memory is what allows an agent to behave less like a stateless API and more like a system that can learn and adapt over time.

You'll learn how to

model episodic memory with JSON and query it using SQL/JSON.
run lexical retrieval with Oracle Text and semantic retrieval with vectors.
combine lexical, semantic, and metadata constraints into hybrid retrieval.
traverse user-ticket-document context with SQL Property Graph and GRAPH_TABLE.
apply lifecycle and tenant-aware security patterns for governed agent memory.

Architecture overview

The unified memory flow keeps ingestion, storage, retrieval, and governance inside Oracle AI Database. Episodic events are stored in JSON, reusable knowledge is retrieved with Oracle Text and vectors, relationship context is traversed with SQL Property Graph, and lifecycle/security controls are enforced with scheduler and VPD patterns.

Prerequisites

Python 3.10+
Oracle AI Database 26ai (or compatible environment)
Dependencies: oracledb, python-dotenv, pandas, optional langchain-core
Privileges for tables, indexes, SQL/JSON, and queries
Optional privileges for Oracle Text and SQL Property Graph features

Types of agent memory and their storage needs

Not all memory behaves the same way. Different memory types have different latency, durability, and retrieval requirements.

Short-term vs. long-term memory

Short-term memory is the agent's working context. It typically includes the current conversation window, recent tool outputs, temporary plans, and session variables. It requires very low latency but does not need to be persisted indefinitely.

Long-term memory persists beyond a single interaction. It may include user preferences, completed tasks, conversation summaries, business objects, knowledge artifacts, and execution history. It should be durable, searchable, and governed.

Episodic memory

Episodic memory stores events and experiences. For agents, that can mean:

previous conversations
tool calls and outputs
actions taken on behalf of a user
workflow checkpoints
timestamps, actors, and outcome metadata

This memory is usually time-oriented and benefits from structured metadata, durable storage, and filtering by user, task, tenant, or date range.

Example: episodic memory as JSON documents

A practical pattern is to store each conversation turn, tool invocation, or workflow checkpoint as a JSON document and query it with SQL/JSON functions:

CREATE TABLE agent_events (
    event_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    session_id  VARCHAR2(100) NOT NULL,
    event_data  JSON NOT NULL,
    created_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

SELECT e.session_id,
       JSON_VALUE(e.event_data, '$.type') AS event_type,
       jt.tool_name,
       jt.latency_ms
FROM   agent_events e,
       JSON_TABLE(e.event_data, '$'
         COLUMNS (
           tool_name  VARCHAR2(100) PATH '$.tool.name',
           latency_ms NUMBER        PATH '$.tool.latencyMs'
         )
       ) jt
WHERE  JSON_VALUE(e.event_data, '$.type') = 'tool_call';

This pattern is especially useful when an agent needs durable session history without flattening every attribute into separate columns on day one.

Semantic memory

Semantic memory stores generalized knowledge rather than a raw event log. It includes facts, policies, product information, documentation, ontologies, embeddings, and derived knowledge the agent can reuse across tasks.

This memory often benefits from a combination of:

vector search for semantic similarity
keyword/text search for exact terminology and domain-specific phrases
relational filters for governance, freshness, and access constraints
graph relationships for connected business meaning

Example: semantic retrieval with Oracle Text

Why does lexical retrieval still matter in a vector-first architecture? An agent can use CONTAINS to rank policy, support, or product documents by relevance and combine that with vector search in the surrounding workflow:

SELECT article_id,
       title,
       SCORE(1) AS relevance
FROM   knowledge_articles
WHERE  CONTAINS(content, 'database performance', 1) > 0
ORDER  BY relevance DESC
FETCH FIRST 10 ROWS ONLY;

For short text catalogs, Oracle Text's CTXCAT model is also a strong fit when agents need keyword matching plus structured filters such as product family, severity, or tenant.

Matching memory types to storage technologies

The key design principle is simple: use multiple memory types, but avoid fragmenting them across too many disconnected systems unless you truly need to.

Storage technologies for agent memory

In-memory storage: pros, cons, and use cases

This type of storage is well suited for fast, transient state.

Pros

extremely low latency
simple fit for session state and active workflow context
useful for intermediate reasoning artifacts and recent tool results

Cons

limited durability
not suitable as the system of record
difficult to govern and audit if used alone

Best use cases

active conversation buffers
current plan state for an orchestrator
short-lived coordination across steps in a single run

File-based storage: when and why to use it

Files and object storage are useful for large, unstructured artifacts such as:

PDFs and reports
images and media
transcript archives
exported workflow bundles

They work well when the artifact itself is large, rarely updated, or naturally belongs in a document repository. However, file-based storage alone is a weak memory layer because it lacks rich query semantics. In practice, it works best when paired with database metadata, vector indexes, or a catalog layer.

Databases: SQL, NoSQL, and key-value stores

Databases are the backbone of durable agent memory.

SQL databases excel when agents need strong consistency, joins, transactions, governance, and structured filters.
NoSQL/document stores are useful when schemas evolve quickly and payloads are semi-structured.
Key-value stores are effective for simple lookups, caching, and session persistence at high speed.

For enterprise agents, the strongest pattern is often not choosing one memory store per memory type, but choosing a platform that can support multiple memory representations together.

Vector databases for semantic memory retrieval

Vector retrieval is essential when an agent must find content by meaning rather than exact wording. It is especially effective for:

semantic search over documents
similarity matching for prior cases
memory recall from summarized or embedded interactions
grounding RAG workflows with relevant context

But vector search should not be treated as the entire memory architecture. Enterprise retrieval often requires semantic matching plus exact filtering, freshness rules, tenant boundaries, business keys, and joins to live data.

That is where Oracle AI Database stands out as a unified memory core. It brings vector search next to relational data, JSON, graph, spatial, and enterprise governance features, allowing agents to retrieve semantically relevant context without losing operational control.

Why text search belongs in the unified memory core

A significant portion of production searches need both vector similarity and keyword matching. Users do not always ask only by meaning. They often include:

exact product names
policy clauses
ticket IDs
account numbers
legal terms
error messages and codes

That is why a mature memory core should include text search alongside vector, graph, spatial, JSON, and relational capabilities. Semantic retrieval helps with meaning; keyword retrieval helps with precision. Together they produce more reliable enterprise context.

Hybrid storage architectures and patterns

Most serious agent systems use a hybrid pattern, such as:

in-memory working context for active sessions
relational/JSON persistence for durable state and episodic history
vector indexes for semantic recall
text search for lexical precision
object storage for large source artifacts
graph structures where relationships are central to reasoning

The question is not whether hybrid memory exists—it almost always does. The real design decision is whether those layers are operationally fragmented or organized around a unified platform.

Example: relationship-aware memory with SQL Property Graph

Oracle SQL Property Graph lets you model and query graph data — vertices (nodes) and edges (relationships) — directly on top of existing relational tables, views, materialized views, or external tables. No data is copied; the graph definition stores only metadata, and queries operate against current table data. This is useful when an agent must follow connected context such as user → ticket → service → document:

SELECT *
FROM GRAPH_TABLE (memory_graph
  MATCH (u IS user) -[r IS opened]-> (t IS ticket) -[m IS mentions]-> (d IS document)
  COLUMNS (
    u.name  AS user_name,
    t.title AS ticket_title,
    d.title AS document_title
  )
);

That kind of retrieval complements vector similarity by surfacing the business relationships around a memory item, not just the nearest semantic neighbors.

Scaling agent memory for large applications

Externalizing memory from models

Large language models should not be expected to carry all relevant context in their parameters or prompt window. As applications grow, memory must be externalized into governed stores that can be queried on demand.

This improves:

freshness of retrieved context
controllability of business logic
auditability and compliance
reuse across workflows and teams
cost efficiency versus oversizing prompts

Hierarchical and tiered memory layers

At scale, memory is usually tiered:

Hot memory – immediate session context and recent tool outputs
Warm memory – summaries, recent episodes, and active task state
Cold memory – historical records, archived artifacts, and long-term facts

This layered approach keeps latency manageable while retaining historical depth.

Retrieval-augmented generation (RAG) techniques

RAG is the most common pattern for grounding an agent with external memory. Strong RAG systems typically combine:

semantic retrieval over embeddings
lexical retrieval for exact terms
metadata filtering for tenant, time, trust, or policy
reranking for relevance and precision
source attribution for traceability

In most real-world systems, no single retrieval method is enough on its own. In enterprise settings, hybrid retrieval matters. Many useful searches depend on both meaning and exact terminology, so text search is not a legacy feature to bolt on later. It is an important part of the unified memory core.

Retrieval evaluation metrics

To validate retrieval quality rigorously, hybrid memory systems should be measured with explicit ranking and coverage metrics, not only by subjective answer quality.

Precision@K: how many of the top-K retrieved results are relevant.
Recall@K: how much of the relevant context is recovered within top-K results.
MRR (Mean Reciprocal Rank): how early the first relevant result appears in the ranked list.
Latency (p50/p95): retrieval responsiveness under realistic concurrency and tenant load.

In practice, these metrics should be tracked per retrieval mode (lexical, semantic, hybrid, graph-augmented) and per query class (policy, troubleshooting, identity, compliance) to detect ranking drift early and keep retrieval behavior stable over time.

Memory management: summarization, pruning, and lifecycle policies

Memory is not just about storing more. It is also about deciding what to keep, compress, expire, and promote.

Important practices include:

summarization to condense long histories into reusable state
pruning to remove redundant or low-value context
retention policies based on legal, business, and product rules
promotion rules to move temporary knowledge into durable memory
staleness checks so outdated facts do not keep reappearing

Example: scheduled summarization and pruning

DBMS_SCHEDULER is Oracle's enterprise job scheduling framework. It provides a rich feature set for scheduling PL/SQL code, stored procedures, executables, and scripts — either on a time-based calendar expression, in response to external events, or as part of dependency chains. The DBMS_SCHEDULER maps naturally to memory lifecycle automation. Teams can schedule summarization, retention enforcement, and archive workflows directly in the database:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SUMMARIZE_AGENT_SESSIONS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN memory_pkg.summarize_old_sessions(30); END;',
    repeat_interval => 'FREQ=HOURLY;INTERVAL=6',
    enabled         => TRUE,
    auto_drop       => FALSE
  );
END;
/

The same scheduling pattern can also keep search infrastructure fresh, for example by running CTX_DDL.SYNC_INDEX and CTX_DDL.OPTIMIZE_INDEX jobs for Oracle Text indexes on a predictable cadence.

Infrastructure considerations for scalability

As memory volume grows, architects should consider:

partitioning by tenant, time, or workload
indexing strategies for vector, text, and relational access
concurrency and transaction isolation for multi-agent workflows
cost of re-embedding and re-ranking pipelines
observability for recall quality, latency, and drift

Oracle AI Database is compelling here because it supports enterprise-grade scalability while keeping multiple data modalities close together. That reduces the coordination overhead of moving context between independent systems.

Operations runbook for memory systems

To keep unified memory reliable in production, teams should operationalize a lightweight runbook that covers indexing, partitioning, scheduler cadence, and observability.

Indexing: monitor Oracle Text and vector index health, and schedule regular sync/optimize routines.
Partitioning: partition event and knowledge tables by tenant and/or time windows to control growth and query cost.
Scheduler cadence: run summarization, pruning, retention, and index-maintenance jobs on predictable intervals.
Observability: track retrieval latency (p50/p95), result quality metrics, and drift signals across retrieval modes.
Operational review: review failed jobs, low-confidence retrieval patterns, and tenant hot spots on a recurring schedule.

A compact runbook helps maintain retrieval quality, governance compliance, and performance consistency as memory volume and workload complexity increase.

Implementation walkthrough

This implementation builds a unified memory flow in Oracle AI Database, from event storage to multi-mode retrieval and governance.

Initialize the environment and establish one Oracle connection reused across the workflow.
Create episodic memory (agent_events) with realistic JSON events for user messages, tool calls, tool results, checkpoints, and summaries.
Query episodic memory with SQL/JSON (JSON_VALUE, JSON_TABLE) for filtering, extraction, and analytics.
Apply tenant-aware retrieval patterns so every recall path remains policy-aligned.
Create and populate the knowledge store (knowledge_articles) with tenant-scoped support and policy content.
Run lexical retrieval with Oracle Text (CONTAINS, SCORE) for exact-term precision.
Add semantic retrieval with vectors and rank results by similarity using VECTOR_DISTANCE.
Combine lexical, semantic, and metadata constraints into hybrid retrieval ranking.
Execute unified recall by combining latest episodic context with knowledge retrieval candidates.
Add relationship-aware retrieval with SQL Property Graph and GRAPH_TABLE over user-ticket-document paths.
Apply lifecycle automation and security patterns using DBMS_SCHEDULER and DBMS_RLS.
Validate outputs end to end and keep the workflow rerunnable with cleanup.

How this guide maps to the notebook

To make the guide easier to navigate, each concept in this article is implemented in a corresponding notebook section.

Episodic memory is introduced through the agent_events model and sample event ingestion, then expanded with SQL/JSON extraction using JSON_VALUE and JSON_TABLE.

Tenant-aware retrieval and knowledge storage follow, along with lexical retrieval using Oracle Text and semantic retrieval using vectors (VECTOR_DISTANCE).

Hybrid retrieval then combines lexical relevance, semantic distance, and metadata filtering in one ranking path.

The workflow continues with unified episodic-plus-knowledge recall, relationship-aware traversal using GRAPH_TABLE, and operational patterns for lifecycle automation (DBMS_SCHEDULER) and row-level tenant isolation (DBMS_RLS / VPD).

The notebook concludes with an optional LangChain interoperability layer that keeps retrieval Oracle-native.

Security and privacy considerations in agent memory storage

Memory makes agents more capable, but it also expands the attack surface.

Common security risks and attack vectors

data leakage across users, roles, or tenants
prompt injection through retrieved content
memory poisoning from incorrect or malicious inputs
over-retention of sensitive information
stale or conflicting memory causing unsafe decisions
weak authorization around recalled context and tool actions

Data protection and access control strategies

Best practices include:

role-based and attribute-based access control
encryption in transit and at rest
row-level or tenant-aware data isolation
retrieval filters tied to identity and policy
audit logs for memory access and mutation events
data classification for sensitive memory types

Operational security checklist

To translate security principles into day-to-day practice, teams should validate a compact operational checklist for every memory workflow.

Enforce tenant isolation in every retrieval path, not only at the application layer.
Log memory reads and writes for auditability, including tool-triggered retrieval actions.
Apply data classification and PII handling rules before memory is persisted or retrieved.
Use role-aware authorization checks for both retrieval and mutation operations.
Define retention and deletion controls so sensitive memory does not persist beyond policy windows.
Protect retrieved context against prompt-injection and memory-poisoning propagation.

This checklist helps ensure that memory quality, security, and compliance remain aligned as agent usage scales.

Example: tenant-aware memory access with VPD

Virtual Private Database (VPD), also called Fine-Grained Access Control (FGAC), is Oracle's mechanism for enforcing row-level security transparently at the database kernel level. Unlike application-layer filtering — which can be bypassed by ad-hoc queries, ETL tools, or reporting applications — VPD policies are enforced by the Oracle query engine itself, regardless of how a query reaches the database.. The Row-Level Security can enforce tenant isolation directly in the database with VPD (DBMS_RLS):

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'APP',
    object_name     => 'AGENT_MEMORY',
    policy_name     => 'TENANT_ISOLATION',
    function_schema => 'APP',
    policy_function => 'TENANT_ISOLATION_POLICY',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE',
    update_check    => TRUE
  );
END;
/

With SYS_CONTEXT-driven predicates, the same memory tables can serve many tenants while ensuring an agent only recalls context it is authorized to access.

Mitigation best practices and compliance

Organizations should design agent memory with compliance in mind from the start. That means applying retention rules, provenance tracking, redaction strategies, and approval workflows where needed. It also means ensuring the retrieval layer does not bypass the same governance standards applied to transactional systems.

This is another reason a unified, governed database platform is attractive: it allows memory retrieval to inherit mature enterprise security controls instead of recreating them separately for every store.

Recap of memory types and storage options

Short-term memory supports immediate task execution and should be fast.
Long-term memory preserves durable context across sessions and workflows.
Episodic memory captures what happened, when, and under what conditions.
Semantic memory helps the agent retrieve meaning, facts, and abstractions.

No single storage mechanism solves every memory problem. In-memory caches, files, relational stores, key-value systems, text indexes, graph structures, and vector search all have a role.

Guidelines for choosing and managing agent memory

Use the following rules of thumb:

Match storage to memory behavior, not just data format.
Keep working memory fast, but make durable memory governed and auditable.
Combine semantic retrieval with keyword and metadata filtering.
Treat lifecycle management as part of memory design, not an afterthought.
Prefer unified platforms when governance, consistency, and scale matter.

Balancing performance, scalability, and security

The best agent memory architectures do not optimize only for retrieval quality. They balance:

performance for responsive agent interactions
scalability for growing users, tasks, and data volumes
security for enterprise trust and compliance

Oracle AI Database fits this balance especially well for enterprise agents. It provides one of the most sophisticated and scalable ways to unify vector, JSON, graph, spatial, relational, and analytic data access in a governed platform. That makes it a strong foundation for a true unified memory core rather than a collection of disconnected memory services.

When memory becomes a first-class architectural concern, agents become more reliable, more context-aware, and more useful in real business workflows.

Validation & troubleshooting: failure modes and fallback strategy

Production memory systems should not assume every retrieval mode is always available. A resilient workflow defines deterministic fallback behavior so the agent can continue safely and predictably.

In this architecture, retrieval degrades gracefully: if lexical retrieval is unavailable or returns weak matches, the workflow can fall back to semantic retrieval; if vector retrieval is unavailable, lexical retrieval and tenant-scoped filtering remain active; if graph traversal is unavailable, relationship context can be reconstructed with relational joins; and if all retrieval modes return low-confidence results, the system should return a safe tenant-scoped fallback response and request clarification.

This fallback strategy preserves continuity, improves user trust, and prevents silent retrieval failure in enterprise workflows.

Validate Oracle Text index creation and lexical ranking output.
Validate vector dimensions and input format used by TO_VECTOR / VECTOR_DISTANCE.
If GRAPH_TABLE parsing fails, avoid reserved labels and use names like user_v, ticket_v, document_v.
If results are empty, verify tenant/category filters and fallback query paths.
Run the notebook end-to-end and confirm outputs across episodic, lexical, vector, hybrid, and graph sections.

Frequently Asked Questions

Why not use vector search alone?
Enterprise queries often contain exact policy/product terms and IDs, so hybrid retrieval is usually more reliable.

What does unified memory mean in practice?
It means episodic, lexical, semantic, and relationship-aware retrieval are handled in one governed database workflow.

What happens if one retrieval mode is unavailable?
The workflow can use fallback paths (lexical, semantic, or relational fallback) to preserve continuity and safe defaults.

How does this relate to the companion notebook?
The notebook implements each pattern as executable steps so readers can validate outputs end-to-end.

Agent Memory with LangChain4j and Oracle AI Database

Anders Swanson — Wed, 22 Apr 2026 17:22:17 +0000

One of the quickest ways to make an impressive agent demo is to prepare a clever prompt. One of the quickest ways to make that same agent fall apart in production is to give it no durable memory.

In this article, we'll build a small, memory-backed assistant with LangChain4j and Oracle AI Database. The assistant can search prior incidents, runbooks, decisions, and shift handoffs to answer questions. It can write new memories back to the database so they become searchable in any session. Additionally, all user, agent, and tool messages are logged to database table for observability and auditing.

Database feature overview
Run the sample
Chat Memory vs Durable Memory
Hybrid retrieval: semantic + full-text search
Lightweight reranking
LangChain4j agent
Memory writeback
Recording user, agent, and tool messages
Why database memory is useful for agents
Code pointers
Where you can take this next

Database feature overview

The agent is built with modern Oracle AI Database features:

persistent JSON memory documents in Oracle AI Database
vector embeddings in a VECTOR column
Oracle Text search over the same JSON document
hybrid ranking that blends semantic and exact-match retrieval
append-only transcript logging by conversation ID

Using these features, the agent (a fictional operations assistant) can answer question about runbooks, incident reviews, change requests, and shift handoffs from its persistent memory. Because the memory is database backed, multiple agents from concurrent sessions may access the same data safely.

Run the sample

You will need Java 21+, Maven, Docker, and an OpenAI API Key.

From the module root, run the tests:

export OPENAI_API_KEY=<your key>
mvn test

To run the live terminal app using your database connection string and user:

export OPENAI_API_KEY=<your key>
mvn compile exec:java \
  -Dexec.args="jdbc:oracle:thin:@localhost:1521/freepdb1 testuser testpwd"

Once it starts, try prompts like:

What happened during the checkout incident after CHG2145?
Which runbook section should I use for the checkout rollback?
Draft a next-shift handoff and remember it.

Chat Memory vs Durable Memory

Chat memory and durable memory solve different problems. Operational memory has different requirements:

it should survive process restarts
it should be queryable across conversations from distributed, concurrent agents
it should support structured metadata like service, environment, incident ID, and change ticket
it should be searchable both semantically and exactly
it should allow writeback when the agent learns something worth preserving

That starts to look a lot more like a database problem than a prompt engineering problem.

Hybrid retrieval: semantic + full-text search

The MemoryRepository runs two queries, which are fused into one ranked list:

Vector search over the embedding column using cosine distance.
Oracle Text search over the JSON payload using json_textcontains.

Here is the vector query:

select id,
       memory_kind,
       title,
       memory_doc,
       (1 - vector_distance(embedding, ?, COSINE)) as vector_score
from agent_memories
order by vector_score desc, id
fetch first ? rows only

And here is the text query:

select id,
       memory_kind,
       title,
       memory_doc,
       score(1) as text_score
from agent_memories
where json_textcontains(memory_doc, '$', ?, 1)
order by score(1) desc, id
fetch first ? rows only

Pure vector search is often too fuzzy for ticket IDs. Pure text search is often too brittle for paraphrases. Hybrid retrieval handles both.

Lightweight reranking

Once both branches return hits, MemorySearchRanker merges the results with deterministic weights:

a bonus when the incident ID or change ticket matches directly
a bonus for keyword overlap in the indexed memory text
a combined matchedBy indicator of VECTOR, TEXT, or BOTH

The deterministic ranker could be implemented by an LLM judge or a more complex re-ranking system. For this sample, I kept it intentionally lightweight and low-latency.

LangChain4j agent

The LangChain4j agent implementation is quite small, using a single interface:

public interface OpsMemoryAssistant {
    @SystemMessage("""
            You are an operations handoff assistant backed by Oracle AI Database memory.
            Use searchMemories when prior incidents, runbooks, handoffs, decisions, or change history are relevant.
            When you rely on memory results, include the references in the form [M123].
            If the user asks you to remember or preserve a new handoff or decision, call storeMemory after drafting it.
            Keep answers concise and operational. Mention incident IDs and change tickets when they matter.
            """)
    @UserMessage("{{message}}")
    String chat(@V("message") String userMessage);
}

That is the right level of abstraction for this sample.

LangChain4j handles chat orchestration and tool wiring. Oracle AI Database handles durable memory, search, and transcript persistence. Each layer is doing the job it is actually good at.

Memory writeback

The sample keeps two memory stores:

a curated durable memory store for retrieval
an append-only transcript for observability and auditing

This one also stores new durable memory through the storeMemory tool when the user explicitly asks the assistant to preserve a handoff or decision.

That matters because an agent memory system should not just be a read-only archive. If a useful conclusion comes out of a conversation, the system should be able to keep it.

In this sample, writeback creates a new MemoryDocument, generates an embedding, and inserts both the JSON payload and vector into agent_memories. Because the JSON search index is configured with sync (on commit), newly stored handoffs are searchable immediately after commit.

That last detail is important. Delayed indexing is exactly the kind of thing that makes an agent feel unreliable.

Recording user, agent, and tool messages

With our database connection, it's easy to record chat sessions in the database. To do this with LangChain4j, we implement the ChatMemory interface in the LoggingChatMemory.java class.

Each session gets its own unique conversation ID, and user/agent/tool messages are written to the agent_conversation_log table.

That table captures:

conversation_id
message_seq
role and message type
message text
tool name and tool call ID when relevant
optional JSON context
creation timestamp

That distinction tends to get blurred in agent demos. It should not.

Why database memory is useful for agents

Chat windows and flat files can't scale the same way a database can. A database-backed memory layer gives you:

durable storage
structured metadata
many types of retrieval: semantic, text, relationship, graph, etc.
transactional writes and concurrency
better auditability

Databases can help you progress from agent demos to real applications that effectively utilize agent memory.

Code pointers

If you want to explore the implementation, start here:

README.md -> app overview
OpsMemoryAgentApplication.java -> Main class and agent loop
MemoryRepository.java -> Memory retrieval for text and vector search
MemoryTools.java -> LangChain4j tool bindings to search and store memories
LoggingChatMemory.java -> LangChain4j ChatMemory implementation to log chat interactions
MemoryRepositoryIntegrationTest.java -> test using Oracle AI Database Free and Testcontainers

The tests validate the behavior that matters

The integration tests are worth reading because they verify the actual retrieval patterns we care about:

exact text search finds the checkout incident for CHG2145 and INC4721
vector search finds the same incident from a paraphrased outage description
hybrid fusion marks the strongest result as matched by both channels
a stored handoff can be found on the next combined search

Where you can take this next

If you'd like to extend this sample, here's a few ideas to play with:

Add "forgetting" with recency ranking so newer memories are ranked as more relevant.
Parameterize scoring and filtering mechanisms to make the app more flexible.
Add another agent tool that uses an LLM to judge search results.
Add approval/rejection when storing memories. Maintain a log of failures so the agent knows what not to do.